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

Automatically Gathering Server Information Part 6

By Steve Jones,

Capturing Ad Hoc Information

Introduction

We are still not finished gathering information about SQL Servers. In the last article, I discussed putting all the information you have gathered into one report. This has worked great for me, but I am still missing information about events that occur on the SQL Server everyday.

The Problem

I have a series of jobs and processes that report their status to me when they run. I usually build the notifications in these processes to only let me know when there is a problem, however, I still get reports of failures at a variety of times and in a variety of forms. Plus I get numerous reports, each of which deals with a single item.

The problem gets worse when I go on vacation or am out of the office for a few days. I return to find daily reports from each server plus a series of miscellaneous emails notifying of other processes that completed or failed.

The Solution

I could have easily built a system for tracking these miscellaneous items in the same manner as I built a space tracking system. However, I am not usually concerned with tracking the history of these items (as I am with database space). After some thought, I decided to build an ad-hoc tracking system for gathering all of this information into a single place.

Those of you who have read the previous part of this series are probably still talking of the brilliant table design I presented in Part 5 (If you haven't read it, check out Part 5 and the amazing T-SQL code I wrote :) ). Well, I am about to present another design that is very similar in nature.

CREATE TABLE DBALog
(       DBALogID int identity( 1, 1),
        entrydt datetime,
        cat char( 20),
        msg varchar( 500)
)

Amazed yet?

This table is basically a log table that can hold any type of information. The first field is simply a handle to make editing easier. The date field is the field that I use when I generate the report using my process tracking system. The cat field is for categorizing the ad hoc information. Each process inserts it's own value here that describes which process is reporting information. The msgfield holds the actual information being reported by the process.

To report on this information, I have a procedure that runs as part of the job that assembles my DBA reports and gathers up the information in this table and adds the new information to the report. The stored procedure is pretty simple, so I will not spend the time explaining it (unless I get a bunch of emails asking how it works). Here is the code for the table and the procedure:

Conclusions

As always, I welcome feedback, especially if you see some way to improve this process that I may have missed. If you have ideas for other information that you think is important, let me know. I may not have thought of it and will probably tackle the project and spread the word.

Steve Jones
© dkRanch.net March 2001


Return to Steve Jones Home

Total article views: 5476 | Views in the last 30 days: 1
 
Related Articles
ARTICLE

Automatically Gathering Server Information Part 5

The fifth part of Steve Jones's series on having SQL Server automatically report information to a DB...

ARTICLE

Automatically Gathering Server Information Part 4

The fourth part of Steve Jones's series on having SQL Server automatically report information to a D...

ARTICLE

Automatically Gathering Server Information Part 7

The seventh part of Steve Jones's series on having SQL Server automatically report information to a ...

ARTICLE

Automatically Gathering Server Information Part 8

The eighth part of Steve Jones's series on having SQL Server automatically report information to a D...

ARTICLE

Automatically Gathering Server Information Part 2

The second part of Steve Jones's series on having SQL Server automatically report information to a D...

Tags
 
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