In this article, I will share with you seven Transact-SQL queries that I like to run daily for the SQL Server instances I support. These queries can be integrated into your own stored procedures, web pages, email reports, or monitoring routines.
I like to run these reports first thing in the morning. These reports are not intended to be a replacement of SQL Server Agent alerts or other critical event notification mechanisms, but rather a daily checkup used to help keep a pulse on your environment (reduce surprises).
The following seven scripts are written in Transact-SQL, referencing various system tables, stored procedures, and undocumented stored procedures. I know that system table references and undocumented extended stored procedure calls may cause issues in future versions of SQL Server, but if the script is just used by me, I don't mind re-writing it in the future!
As a side note, I also like to check daily the System, Security, and Application event logs using Windows Event Viewer for each of my SQL Server instances. I do not use Transact-SQL to do this, but hopefully when Yukon is released, I can write a VB.NET script to access the event logs programmatically.
Here are the seven monitoring reports. Hope you find them a useful addition to your Transact-SQL toolkit!
Failed jobs report. This query lists the name of all jobs that failed in their last attempt:
SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0
Free space by drive. This is an undocumented extended stored procedure call that you can use to show free space by drive, for the SQL Server instance:
EXEC master..xp_fixeddrives
Disabled jobs. ever have a critical job that someone decided to disable? Catch it with this script!
SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY name
Running jobs. Need to know what jobs are still running? Ever come into work on Monday morning to find several critical jobs having “piled up”, running way to long, or hung up? This query lists those running queries (whether normally scheduled or not). This procedure call is good for making sure your Distribution or Merge agent job is still running too. I make a call to sp_get_composite_job_info (loading in a bunch of NULLS, and a “1” to indicate running jobs):
msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL
Server role members. Check which logins are a member of server roles. Have co-workers who like to slip in new sysadmin users? This will help you find rogue server role assignments.
SELECT 'ServerRole' = A.name, 'MemberName' = B.name FROM master.dbo.spt_values A, master.dbo.sysxlogins B WHERE A.low = 0 ANDA.type = 'SRV' ANDB.srvid IS NULL AND A.number & B.xstatus = A.number
Last backup date. This query shows the very last time your databases were backed up.
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 LastBackupDate FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name
SQL Log. Reading the SQL Log using Transact-SQL. This query shows the entries from the latest SQL log (the one currently being updated). You can modify this script according to what information you find important. As you can see, I like to filter out various key works and entries that are part of non-critical SQL Server messages. This script uses the undocumented extended stored procedure xp_readerrorlog:
CREATE TABLE #Errors (vchMessage varchar(255), ID int) CREATE INDEX idx_msg ON #Errors(ID, vchMessage) INSERT #Errors EXEC xp_readerrorlog SELECT vchMessage FROM #Errors WHERE vchMessage NOT LIKE '%Log backed up%' AND vchMessage NOT LIKE '%.TRN%' AND vchMessage NOT LIKE '%Database backed up%' AND vchMessage NOT LIKE '%.BAK%' AND vchMessage NOT LIKE '%Run the RECONFIGURE%' AND vchMessage NOT LIKE '%Copyright (c)%' ORDER BY ID DROP TABLE #Errors
