DB Status?

Question

Post reply

DB Status?

Vertigo44

SSCarpal Tunnel

Points: 4055
More actions
November 22, 2010 at 7:04 am

#146232

Hi all!

So this is my first week as an offical dba and I have been tasked with getting a list of all databases on serveral instances and servers within the company. I am able to login to each server using Management Studio and running the query sp_databases to get a list of all the databases then saving them to CSV for excel where I can create a master list. While I was running this simple query I asked myself what other essential information I would want to retrieve and export to excel. Since these databases have not had a DBA in a Looooong time what essential indicators should I be looking for to get a good overall health report on each database?

Thank you!

I just saw the Newbie forum. Please feel free to move this thread there.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

Check the backups. First thing. Validate that they're running, running correctly, are scheduled, etc. Then, identify the most important databases and validate that you can run a restore of the backups so that you know you've got good backups scheduled and running. That's the most important job you can do starting out.

After that, check log backups and recovery models.

Then, I'd go into checking the other maintenance routines, index defrag, statistics updates, that sort of thing. Especially the stats updates.

THEN, I'd go into the db's and see what you're dealing with there. Are they primarily home grown or third party. If home grown, check the key structures, indexes, etc., so you understand what's been done. You can do the same thing for third party apps, but since you have no control over them, it's slightly less useful.

Finally, assuming you're talking about 2005 and above servers, I'd check the cache, sys.dm_exec_query_stats, to see aggregate information about the queries that are running in the system. Which ones are called most frequently, run the longest, etc. It'll let you know if you have outstanding performance issues. If you're looking at a 2000 system, set up a server-side trace and capture that information. You may still want to do that for 2005/2008 systems, but with the DMV you can skip it for the initial check.

You might go to Brent Ozar's site (www.brentozar.com) and check out his SQL Blitz script. It runs all kinds of checks on systems that he does as a consultant and he shared it.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Vertigo44 SSCarpal Tunnel Points: 4055 More actions · Answer 2

Vertigo44

SSCarpal Tunnel

Points: 4055

November 22, 2010 at 7:39 am

#1253738

Outstanding information! Thank you. I am familiar with Brents website and just started following him on twitter. His site has been very helpful thus far. Again thanks for the advice!

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

Not a problem. If you run into specific issues, please come back around and post them here. People are very excited to help out.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

homebrew01 SSC Guru Points: 55454 More actions · Answer 4

Here's a query that you can run to check on when each database was last backed up and the recovery model.

I used this when I started at a new job to quickly identify holes in backup strategy.

SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,



ISNULL(Convert(char(19), MAX(backup_finish_date), 100), 'NEVER') as LastBackupDate,

case

	when type='D' then '** FULL **'

	when type='I' then 'DIFFERENTIAL'

	when type='L' then 'LOG'

end as Backup_Type,

b.recovery_model_desc as 'Recovery Model',

case

	when state_desc <> 'ONLINE' then state_desc -- Alert that DB might be ReadOnly, Offline etc...

		else ' '

end as 'DB Status'

FROM master.sys.databases B

LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name

GROUP BY B.name , a.type, b.recovery_model_desc, state_desc

ORDER BY B.name , LastBackupDate desc,a.type, b.recovery_model_desc, state_desc

Vertigo44 SSCarpal Tunnel Points: 4055 More actions · Answer 5

Eww. Nice! I just ran Brents Blitz query on my local instance and it came back with a lot of good info but at the bottom of the message was this...

(7 row(s) affected)

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

Msg 208, Level 16, State 1, Line 22

Invalid object name 'sys.dm_server_audit_status'.

Msg 14261, Level 16, State 1, Procedure sp_verify_operator, Line 25

The specified @name ('The Database Administrator') already exists.