SQL Server does a decent job logging errors and warnings into its Error Log and NTs 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 youre 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)
|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, Ill use this field for a process status. Ill 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 youre 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, Ill focus on stored procedures. When I start a stored procedure, Ill 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 Im 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
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 youll 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.