A general logging t-sql process for your processes
October 2, 2000
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.
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
When I write to the table, I use two lines:
Select @msgtxt = "This is my log message"
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
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.