November 22, 2010 at 7:04 am
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.
November 22, 2010 at 7:35 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 22, 2010 at 7:39 am
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!
November 22, 2010 at 7:44 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 22, 2010 at 8:18 am
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
November 22, 2010 at 8:27 am
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.
Msg 14261, Level 16, State 1, Procedure sp_verify_alert, Line 49
The specified @name ('Severity 016') already exists.
Msg 14261, Level 16, State 1, Procedure sp_add_notification, Line 36
The specified notification ('Severity 016 / The Database Administrator') already exists.
Msg 14261, Level 16, State 1, Procedure sp_verify_alert, Line 49
The specified @name ('Severity 017') already exists.
Msg 14261, Level 16, State 1, Procedure sp_add_notification, Line 36
The specified notification ('Severity 017 / The Database Administrator') already exists.
Msg 14261, Level 16, State 1, Procedure sp_verify_alert, Line 49
The specified @name ('Severity 018') already exists.
Msg 14261, Level 16, State 1, Procedure sp_add_notification, Line 36
The specified notification ('Severity 018 / The Database Administrator') already exists.
Msg 14261, Level 16, State 1, Procedure sp_verify_alert, Line 49
The specified @name ('Severity 019') already exists.
Msg 14261, Level 16, State 1, Procedure sp_add_notification, Line 36
The specified notification ('Severity 019 / The Database Administrator') already exists.
Msg 14261, Level 16, State 1, Procedure sp_verify_alert, Line 49
The specified @name ('Severity 020') already exists.
Msg 14261, Level 16, State 1, Procedure sp_add_notification, Line 36
The specified notification ('Severity 020 / The Database Administrator') already exists.
Msg 14261, Level 16, State 1, Procedure sp_verify_alert, Line 49
The specified @name ('Severity 021') already exists.
Msg 14261, Level 16, State 1, Procedure sp_add_notification, Line 36
The specified notification ('Severity 021 / The Database Administrator') already exists.
Msg 14261, Level 16, State 1, Procedure sp_verify_alert, Line 49
The specified @name ('Severity 022') already exists.
Msg 14261, Level 16, State 1, Procedure sp_add_notification, Line 36
The specified notification ('Severity 022 / The Database Administrator') already exists.
Msg 14261, Level 16, State 1, Procedure sp_verify_alert, Line 49
The specified @name ('Severity 023') already exists.
Msg 14261, Level 16, State 1, Procedure sp_add_notification, Line 36
The specified notification ('Severity 023 / The Database Administrator') already exists.
Msg 14261, Level 16, State 1, Procedure sp_verify_alert, Line 49
The specified @name ('Severity 024') already exists.
Msg 14261, Level 16, State 1, Procedure sp_add_notification, Line 36
The specified notification ('Severity 024 / The Database Administrator') already exists.
Msg 14261, Level 16, State 1, Procedure sp_verify_alert, Line 49
The specified @name ('Severity 025') already exists.
Msg 14261, Level 16, State 1, Procedure sp_add_notification, Line 36
The specified notification ('Severity 025 / The Database Administrator') already exists.
Msg 14261, Level 16, State 1, Procedure sp_verify_alert, Line 49
The specified @name ('Error 825') already exists.
Msg 14261, Level 16, State 1, Procedure sp_add_notification, Line 36
The specified notification ('Error 825 / The Database Administrator') already exists.
Msg 208, Level 16, State 1, Line 61
Invalid object name 'sys.dm_database_encryption_keys'
November 22, 2010 at 8:32 am
homebrew01 (11/22/2010)
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
I just ran this and this is exactly what I need! I am going to run it against all the "important databases" first and get a report out to the system admins. I believe they mentioned something about Tivoli ASM? I might have the name wrong.
November 22, 2010 at 8:33 am
What version of SQL are you running?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2010 at 10:40 am
GilaMonster (11/22/2010)
What version of SQL are you running?
We are running a few 2000 but mostly 2005. No 2008 just yet.
November 22, 2010 at 10:50 am
Which version did you try running that script on?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2010 at 10:59 am
While trying to run this...
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
I got this...
Msg 208, Level 16, State 1, Line 2
Invalid object name 'master.sys.databases'.
I then found out the product version is 8.00.760 which i think may be sql server 8. But how do I get the same information out of 8 that i can run on 2005 with no problem?
November 22, 2010 at 11:02 am
That script isn't set up to run on 2000. You'll have to gather the metrics other ways.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 22, 2010 at 11:02 am
SQL 2000
Brent's script is written for SQL 2005 and portions for only SQL 2008. That script is not going to run on SQL 2000. Some things can be done, not all.
Perhaps start posts in the SQL 2000 forums for the things that you want to get on SQL 2000.
p.s. Don't just run Brent's script without understanding what you're doing. There are clear comments in that for each section including ones noting which queries are for SQL 2008 only.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2010 at 11:20 am
reading the comments now on the blitz query. Thanks!
November 22, 2010 at 11:02 pm
Ahhhh Glasshopper... "Important databses". At this stage and until verified, they are all important 😛
I still use this today and it works on All Versions incl 2K8R2. Create from a management server the required linked servers and create this in master. Use case supplied at the bottom.
Create procedure uspListBackupStatus (
@a_server_name varchar(50),
@DEBUG bit)
as
declare @cmd varchar(8000)
SET @cmd =
'SELECT b.name as Database_Name, '+
'ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(Backup_finish_date)))), ''NEVER'')as DaysSinceLastBackup, '+
'ISNULL(Convert(char(10), MAX(backup_finish_date), 101), ''NEVER'') as LastBachup '+
'from ['+@a_server_name+'].master.dbo.sysdatabases b '+
'LEFT JOIN ['+@a_server_name+'].msdb.dbo.backupset A ON A.database_name = B.name '+
'WHERE A.type = ''D'' '+
'GROUP BY B.Name '+
'ORDER BY B.name '
if @DEBUG = 0
exec(@cmd)
else
print @cmd
/*
*********************************************************************
USE Case
Created :Mal Daughtree
Date :18/05/07
Date Author Purpose
---------- ------------- ----------------------------
30/05/07 Initial
*********************************************************************
exec uspListBackupStatus [LINKEDServer],0 -- Execute the cmd
exec uspListBackupStatus [LINKEDServer],1 -- Types just the cmd Text
*/
Code On 😛
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply