Capturing Ad Hoc Information
- Getting a Count of Database Users - Part 1
- Part 2 - Automatically Gathering Database Size
- Part 3 - Automatically Getting Log Space
- Part 4 - Getting Organized
- Part 5 - A Daily Report
- Part 6 - Capturing Ad hoc Information
- Part 7 - Checking Job History
- Part 8 - Tracking Your Objects
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