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 AND A.type = 'SRV' AND B.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
