SQL Server does a decent job logging errors and
warnings into it’s Error Log and NT’s application log, but these are mostly for
things that have either have gone wrong or to trap errors that you have created yourself.
But what about if you just want to log information about a job or process you’re
running to make sure the steps are running the way you expected and also giving job
information to your clients. I have come up with a way to implement a generic logging
system that you can use for your processes.
There are a couple of parts in making this work: where to
keep the logging table and creating it; writing to the table; utilizing the table; and
table maintenance.
Where to keep the logging table and creating it – I
have a simple database, called dbatasks on all of our SQL Servers. I keep dba-related
stuff like this in this database. Create or pick a database to create the table (DDL
Below). I keep only one copy of this table per server.
CREATE TABLE dbo.JobLoadLog (process char (8) NOT NULL, msgtxt char (80) NOT NULL, msgstamp datetime NOT NULL, status char (1) NOT NULL, stepid int NOT NULL) |
|
Column |
|
|---|---|
| Process | Assign a unique process name, so that you can view/find it later |
| Message | The actual message you want written to the log table |
| MsgStamp | Holds the timestamp of when the line was written (I use getdate()) |
| Status | When I insert a row, I’ll use this field for a process status. I’ll use a ‘P’ for processing, ‘C’ for complete, and an ‘F’ if the job failed. (We’ve used a trigger tied to this table to do things like automatic corrective measures, based on the combination of process and status.) |
| StepId | A number denoting which step of the job you’re on. A crude ‘percent complete’ indicator. |
Writing to the table – I use this with both DTS
packages (Query Task) and stored procedures. For the purpose of this article, I’ll
focus on stored procedures. When I start a stored procedure, I’ll define two
variables:
@proc_name char(8) — The process name
@msgtxt varchar(80) — The message your inserting into the table
When I write to the table, I use two lines:
Select @msgtxt = "This is my log message"
Insert dbatasks..jobloadlog values (@proc_name, @msgtxt, getdate(), ‘P’, 20)
You can build any sort of string, combining SQL Server
global/local variables and functions, and text. Let your imagination go wild.
Utilizing the table – Since I keep one table per server, writing selects all
of the time could be a task in itself. I usually create a view that formats the
information for a process/date I’m interested in. Below is a sample of the type of
view I use to look at the data:
CREATE VIEW dbo.ABC_DayJobLog AS
SELECT TOP 300 ‘Process’ = process, ‘Log_Message’ = left (msgtxt,59),
‘Time_Stamp’ = msgstamp, ‘Stat’ = status,
‘PctCmplt’ = stepid
FROM dbatasks..jobloadlog
WHERE (process = ‘ABCLOAD’)
and
msgstamp > CONVERT(varchar, GETDATE(), 1)
ORDER BY msgstamp, stepid
This particular sample gives me the info I want, sorted how
I want it and the line length isn’t too bad.
Table maintenance – I have setup a one step job, which runs weekly and purges
any log entries older than 60 days from the JobLoadLog table. The SQL for the step is
below:
delete dbatasks..jobloadlog where msgstamp <
CONVERT(varchar, GETDATE()-60, 1)
This logging system works very well for me and is a
consistent means I use to log the information I consider important to both my clients and
me. It has saved me time in diagnosing problems and I can see which steps executed and
which did not. Hopefully you’ll find it useful too. I also have a version of this
(even simpler) which you can use to write to .txt files. Click
here to see it.
If you have any questions about this process, please feel free to email me.
Mike Metcalf
