SQL Code Basics: Reusable Event Logging Utility

on March 3, 2016

When I created the SQLIndexWorkbook database [which was later renamed to BabbyNames], I purposefully shipped minimal code. I plan to add more as I build it, but I wanted to keep the first version dead simple.

photo-1456556845129-6c1dea4bd327

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.