Monitor Your Database Servers with DTS, Part 2: Errorlog Monitoring of SQL Servers

By : Neeraj Nagpal May 26, 2005

Printer friendly

In the first article in the series, I explained the setup of a report that ensures that backups are happening as per the backup policy. In this article, I will explain how to monitor SQL Server errorlog, leveraging the capabilities of DTS. Just in case you have not read the first article, in this series, I am covering how to monitor three elements of Microsoft SQL Server:

1. Backup SLA (Service Level Agreement) Report — to find any database that has not been backed up in the last 24 hours.
2. Errorlog check — to report errors in SQL Server errorlog for any of the servers in an enterprise.
3. Space monitoring — to report databases that have less than 10 percent free space.

Errorlog Monitoring of SQL Servers

It is important for any DBA to know if there are any errors in errorlog. Timely knowledge of errors can prevent major problems. DTS is used to bring the information from all the servers to one place so you can run queries against that data, and take action based on the results.

You can read the SQL Server errorlog by issuing the extended stored procedure — "xp_readerrorlog". The process uses a DTS package to consolidate the output of xp_readerrorlog from all the SQL Servers to a central server. It then runs the queries against the central repository to check for strings like "error" or "fail." If you want, you can include "1105" or "9002," which are the error numbers for a data file that is full or a log file that is full.

These are four parts of the reporting process:

1. Run xp_readerrorlog on each server and save the data in one of the tables in dbamaint on a local server.
2. Transfer the records from each server to a central repository.
3. Look for predefined strings in the records of all the servers.
4. Send an e-mail report.

In this case the central repository server is "SQLSRVCNTREP" and the database with central repository is "dbamaint." The query may need to be changed for 6.5 version servers. For this setup you need to create a "dbamaint" database on all the servers that need to be monitored. Create three tables on each server to hold errorlogs.

How does it work?

The script running the DTS package and other sqls to monitor can be scheduled in NT Scheduler on your central repository or monitoring server. The multiple steps taken to set up the errorlog monitoring are given below:

1. Task "error_log_chk" is scheduled in NT Scheduler to run every 30 minutes.
   
   
2. The scheduled job runs a batch file that is stored in "E:\dba\Scripts\errorlog_chk_1.bat". You can choose the location of the script depending on your environment. (View the text of the scripts.)
3. The first step in the batch script is to run the DTS package "err_log_chk". The NT command issued to run the DTS package is
   
   dtsrun /S SQLSRVCNTREP /N err_log_chk /E >>%DBAOUT%\error_log_dts.log
   
   The package can be seen using SQL Enterprise manager.
   
   
4. Some groundwork was done for the package to run.
   1. The dbamaint database was created on all servers.
   2. The tables were created in dbamaint on all servers.
   3. The errorlog was populated in a table local to the server. (View the create table script.) The query is different for SQL Server 2000 and SQL Server 6.5.
5. This package brings the errorlogs of all the SQL Servers and stores it in the table "dbamaint..errorlog_all_srv"
6. The DTS package runs two steps for all the servers:
   1. Select new rows of the errorlog.
   2. Bring it to SQLSRVCNTREP.
7. The next step is to find the new rows in the central repository and look for strings like "error" and "fail." To achieve this, a previous copy of the table errorlog_all_srv is kept in errorlog_all_srv1. (View the text of the scripts.)
   
   isql -S%SRVNAME% -E -i %DBASQL%\errlog_chk_1.sql -o%DBAOUT%\errlog_chk_1.out -n -w3000 -t120
8. Page "dbaoncall" in case of critical errors, for example, if there is a default segment or log segment filled up on any of the server. For example, Tivoli telalert can be used to page the on-call person. "xp_cmdshell" is used to run the executable to page. You can use a similar approach and any utility.
   
   isql –S%SRVNAME% -E -I%DBASQL%\errlog_chk_1a.sql –o%DBAOUT%\errlog_chk_1a.out –n –w3000 –t120
9. For less critical errors you can send an e-mail. In this case I used "xp_cmdshell" to run the executable to send e-mail. (View the text of the scripts.)
   
   You can use any SMTP client to send e-mail. "Sendmail" and "blat" are among a few. Blat is freeware and can be downloaded from Internet.
   
   isql -S%SRVNAME% -E -i %DBASQL%\errlog_chk_2.sql -o%DBAOUT%\errlog_chk_2.sql -n -w3000 -t120