Capturing Ad Hoc Information
This article continues the same topic of Automatically Gathering
Server Information. If you want to read the previous articles, click on one of these links:
Getting a Count of Database Users – Part 1
Automatically Gather Database Size – Part 2
Automatically Gathering Log Space – Part 3
Getting Organized – Part 4
A Daily Report – Part 5
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 msg field 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
March 2001
