Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Seven Monitoring Scripts

By Joseph Sack, (first published: 2003/06/13)

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!

  1. 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
  1. 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
  1. 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
  1. 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
  1. 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
  1. 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
  1. 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
Total article views: 48580 | Views in the last 30 days: 46
 
Related Articles
FORUM

Scripting Stored Procedures

Scripting Stored Procedures

FORUM

Query Editor SSMS 2008 R2 Strange Script

Scripting Stored Procedures

SCRIPT

consolidated script generator for multiple stored procedures

Consolidated script generator for multiple stored procedures (existing and/or newly created) in MS S...

SCRIPT

Generate Drop / Create Script for views, stored Procedures

This Script generates drop and create scripts for views or stored procedures.

ARTICLE

SQL Server Stored Procedures and SET options

SET options on stored Procedures work a bit different when compared with ad-hoc queries. These diffe...

Tags
administration    
monitoring    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones