Search Wiki:

How to Automate Common DBA Tasks

Jonathan Kehayias, Oct 21, 2008

Database Administrators often spend hours each day doing the same repetitive tasks; checking Error Logs, Backups, Drive Space, Agent History, Job Logs, and other common “checklist” items. It is fairly easy to automate these common tasks in SQL Server 2000, 2005 and 2008 using the tools that are already available in the Operating System and SQL Server. To do this, a combination of TSQL, VBScript, WMI, and the SQL Server Agent and Windows Task Scheduler can be used. This article will break the various items to monitor up into sub-articles to keep topics focused and separate. The sub-articles will be in two classifications:

TSQL monitoring through SQL Agent

Since DBA's monitor Database Servers, it is only natural that a majority of the monitoring that they do utilizes TSQL. Since this is the case, the natural way to automate these tasks is with the use of the SQL Server Agent. As a part of automation, the first item that will need to be setup is a method of providing notifications to the appropriate parties when an exception based event is found during the automated monitoring. SQL Server 2000 is somewhat limited in its native notification abilities. For security purposes the examples provided will not use SendMail which is native to SQL Server 2000, but instead a customized stored procedure based on the CDOSys stored procedure from Microsoft which does not require a IMAP client to be installed on the SQL Server. For SQL Server 2005 and 2008, Database Mail should be used as it is native and the best option available.

Once a method of providing notifications is configured it is possible to begin monitoring the SQL Server with TSQL scripts. Items that can be easily monitored with TSQL include:

Return to Top

VBScript/WMI monitoring through Windows Task Scheduler

Along with monitoring for events internal to SQL Server, it is also important for to monitor items external to SQL Server. Since Windows Server 2000, VBScript and WMI have been available, and have been used for automating various tasks for administrators. VBScript can use the CDO object to send email natively which makes it a very good way to provide notifications of exceptions found. Items that can be monitored with VBScript and WMI include:

Return to Top

For additional information please see:

DBA Checklist

Automating DBA Processes (TechEd 2008)

Page Created By: Jonathan Kehayias, Oct 21, 2008
Last edited Dec 1 2008 at 9:49 PM  by JonathanKehayias, version 6
KEROBIN wrote  Aug 12 2011 at 12:22 PM  
Very nice article

msheehan wrote  Jun 20 2013 at 2:32 PM  
Do you have any updated scripts for "How to Automate Common DBA Tasks"? For example, a script that monitors "LUNS" and not just the "drive", and also more information on how you are configuring the SQL Agent jobs to alert on this. I'm not sure I've found the correct/entirety of how you set this up. Thanks!

Page view tracker