THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Logging for post-processing

As a practicing database professional, I'm sure you have done a lot of logging, I mean, output and/or error logging from your scripts, your scheduled jobs, and any other programs you might have written. The primary purpose of logging, of course, is to facilitate troubleshooting in case something goes wrong. 

Often, people don't give much thought to the format of what is written in the log file. They are much concerned about what is logged. They are much more interested in being able to find the clue if something does go wrong. But the format can be a big factor in your ability to find that clue or how efficiently you can find that clue. This is especially true in a relative large or busy environment where you may have a lot of log files, or the volume of the log files is significant.

In other words,  the log file format is important if you want to go beyond inspecting them manually to being able to automate that inspection. And in that regard, a little bit more attention to the format to ensure its consistency and scripting friendliness can go a long way.

Recently, I have been doing some deadlock analysis with a script to scan the SQL Server errorlogs, and process and summarize the output logged with the trace flag 1222. It's easy to identify where the trace 1222 output for a specific deadlock begins because it always begins with a string 'dealock-list' on a new line (along with the usual prefix columns). But it's more difficult, though not terribly hard to get around if you use the right data structure, to determine where the log for a specific deadlock ends. Ideally, it would have been great for the output of a specific deadlock to have a clear start and a clear finish, each with its own string pattern on a separate line. The same practice should apply to the log files you create. In my own experience, I have found myself writing scripts to summarize the output files from all sorts of DBA or even application jobs.

I should also note that in addition to facilitating troubleshooting,  you probably would often log performance related stats in output files. You'd be well served to ensure that these performance stats are logged in a consistent format as well, if you want to avoid having to manually search and copy them to an Excel worksheet.

Published Wednesday, November 17, 2010 3:31 PM by Linchi Shea



Sam Greene said:

If you haven't seen splunk before, you should take a look at it.  I think it's pretty useful for making some sense of nasty log files.

November 17, 2010 3:23 PM

Linchi Shea said:

Thanks. I didn't know splunk and just googled it and read a few pages on it. Sounds interesting. In my own case, I almost inevitably find myself circling back to Perl to write throw-away scripts in processing the log files.

November 17, 2010 3:29 PM

AaronBertrand said:

In my previous position, I had the luxury of dictating the format of log files from a variety of proprietary applications.  Since I wrote the log file consumption processes, the rules were pretty simple: if you didn't match my format, the data didn't get into the database.

November 17, 2010 9:14 PM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement