T-SQL Logging

This is a framework and standard for handling logging different system messages including Debug, Error, Fatal, Info, Trace, and Warn for aiding in trouble shooting, auditing and tractability of the T-SQL system.

Philosophy

We want to make sure that if anything happens in the execution of T-SQL that is abnormal, that we log out what occurred to help with troubleshooting and understanding what happened in action of the process. Here is an overview of the standard log levels and what they are used for.

• Trace – very detailed logs, which may include high-volume information such as protocol payloads. This log level is typically only enabled during development
• Debug – debugging information, less detailed than trace, typically not enabled in production environment.
• Info – information messages, which are normally enabled in production environment
• Warn – warning messages, typically for non-critical issues, which can be recovered or which are temporary failures
• Error – error messages – most of the time these are Exceptions
• Fatal – very serious errors!

Components

1. Base Database
   1. This is the database that will hold the logged data and the utility objects for the logging. It is the core base database for GWB
2. dbo.Log
   1. This is the table that will hold all of the logs.
3. dbo.spLog
   1. This is the stored procedure that has the logic for logging and does the logging action.
   2. It has few different input parameters to help with different logging scenarios.
      1. ObjectID – Required – This is the ID of the object this is Logging
      2. DatabaseID – Optional – This is the ID of the database that is logging. It will resolve the ID of the database if not passed in.
      3. LogLevel – Optional – This is passed in to tell the system what type of log message this is. It will resolve to error if not passed.
      4. AdditionalInfo – Optional – This is so that you can pass in a message you want to log. This is for more information or trace type logging.

The components code example can be found on my Github.

https://github.com/DerekHackett/T-SQL-Logging

Architecture Diagram

Usage

1. You will want to make sure that Base Database is deployed to your SQL server.
2. When you want to log any of the of the type of messages presented above all you need to do is include the a execute call out to spLog and it will process your log request for you.

Example Error:

Example Trace:

Logging flickr photo by pjohnkeane shared under a Creative Commons (BY-SA) license