SQL Code Basics: Reusable Event Logging Utility

photo-1456556845129-6c1dea4bd327When I created the SQLIndexWorkbook database, I purposefully shipped minimal code. I plan to add more as I build it, but I wanted to keep the first version dead simple.

But even “dead simple” includes a reusable logging utility. Every SQL Server database should have one, and they should be easy to use.

Logging Makes Your Life Easier

When things go wrong, the question, “What happened?” comes up a lot. Logging is how you answer that question.

You can’t, and shouldn’t log everything, because logging events can slow you down. And you shouldn’t always log to a database, either– you can keep logs in the application tier as well, no argument here.

But most applications periodically do ‘heavy’ or batch database work. And when those things happen, it can make a lot of sense to log to the database. That’s where this logging comes in.

1. Have a Standard Procedure for Logging Events

I learned about SQL Server in an environment that had a really great architect. He designed databases really well, and he also had a lot of common sense. He built a logging utility that was used in every SQL Server database in the environment. It was dead simple to install, and having it meant:

  • Everyone always knew where to look for the event history
  • Nobody had an excuse for not doing event logging

2. Put Logging Procedures and Tables in their Own Schema

There’s one huge perk to using a dedicated schema for logging: your ops people will be able to find the objects really, really fast.

I prefer the schema name EVT. It’s three letters. It’s easy to remember. It stands for event. But the name doesn’t really matter at all.

3. Check Logging Code Into Source Control by Itself

Your event logging code isn’t part of the application itself. It needs to be checked into its own repo/depot/whatever, and versioned on its own. This makes life a lot easier for everyone, as well.

This May Sound Like Common Sense, But…

Are you actually doing it? Because I’ve found a whole lot of people in the world who aren’t.

There’s no reason not to start now.

Previous Post
SQL Server’s YEAR() Function and Index Performance
Next Post
What Resets sys.dm_db_index_usage_stats and Missing Index DMVs?

Related Posts

No results found

2 Comments. Leave new

  • Derek B. Bell
    March 3, 2016 1:06 pm

    I agree, even though I don’t use it as often as I should.

    Comes in very handy when supporting complex SQL jobs or building data marts or other complex data sets. I’ve also used them to identify bottlenecks in the execution pipeline, so I knew where to focus on optimizations, indexing, etc.

    Reply
  • […] Kendra Little discusses having a reusable event logging tool for your database work: […]

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu