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

How to Add/Remove a New Server to the Monitoring Process?

1. Create the dbamaint database on the new server.

   if not exists (select * from master..sysdatabases where name=’dbamaint’)
   create database dbamaint
   go

2. Create a new “Microsoft OLE DB connection” to the new server in the DTS package.

   

3. Create a new “Execute SQL task” in DTS package with the following query:

   use dbamaint

   go
   if not exists (select * from dbamaint..sysobjects where name=’errorlog_srv’ and type=’U’)
   create table dbamaint..errorlog_srv
   (server_name varchar(25) default @@servername,errlog_text varchar(255),c2 int)

   if exists (select * from dbamaint..sysobjects where name=’temp_errorlog’ and type=’U’)
   drop table dbamaint..temp_errorlog

   create table dbamaint..temp_errorlog
   (server_name varchar(25) default @@servername,errlog_text varchar(255),c2 int)

   insert dbamaint..temp_errorlog (errlog_text,c2)
   exec master..xp_readerrorlog

   if exists (select * from dbamaint..sysobjects where name=’new_errorlog_rows’ and type=’U’)
   drop table dbamaint..new_errorlog_rows

   create table dbamaint..new_errorlog_rows
   (server_name varchar(25) default @@servername,errlog_text varchar(255),c2 int)

   insert dbamaint..new_errorlog_rows (errlog_text,c2)
   select errlog_text,c2 from dbamaint..temp_errorlog where errlog_text not in (select errlog_text from dbamaint..errorlog_srv)

   –select * from dbamaint..new_errorlog_rows

   insert dbamaint..errorlog_srv
   select * from dbamaint..new_errorlog_rows

   

4. Create a “Transform Data Task Properties” task and enter the following query:

   select * from dbamaint..new_errorlog_rows

   

5. Add the workflow so that the transform step runs after the “Execute sql task.”

   

6. Don’t forget to save the DTS package.

You have now successfully completed the setup of errorlog monitoring.

In the next article, I will explain how to set up the space monitoring report.

]]>