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.

Pages: 1 2




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |