A general logging t-sql process for your processes

October 2, 2000

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)	

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