Log errors in SQL Server that would otherwise go unnoticed (+ examples)

When queries fail to execute I can see what goes wrong and adjust my query. Usually database objects like stored procedures get called by an API or another stored procedure. There’s no one there to notice when something goes wrong, usually you notice a few hours, days or weeks later when a client calls you saying that some data is missing.

Ideally you’d like to know as soon as possible why, when and where something went wrong in your database so you can fix your queries. This article shows you an easy way to monitor your database processes and detect when something goes wrong.

Preparation

SQL Server Management Studio does a good job catching a lot of syntax errors. Sometimes error cannot be expected though. Think about situations where you are dependent on user input or are creating queries using dynamic SQL. I’m going to use a simple query that illustrates unexpected user input; a totally unnecessary stored procedure that allows users to specify a table. The SP will then retrieve that table’s record count.

For people who are unfamiliar with dynamic SQL: in this SP we can determine the contents of a query. We declare a string called @query that contains the query we want to execute. Users can pass their schema name and table name to the stored procedure. The SP will then create the query and execute it.

Lets test if our SP actually works:

Great, it works! But what happens when we pass a non-existent schema or table?

The problem is that our script just fails and cuts out. When I am sitting at my laptop, executing this script I see and understand the error so I can do something about it. But imagine if this SP gets called by an API or SP’s. There is no user involved there so the errors could go unnoticed. It’s very important to detect these types of errors in our script so let’s see how we are going to to exactly that.

Detecting and logging errors

Detecting errors is easy with a Try-Catch block; most programmers are familiar with them. A lesser known feature in SQL Server is that you can also get all kinds of information in case your code fails. In the SP below we retriever more information with ERROR_MESSAGE() for example.

Executing this SP with some wrong parameters again EXEC dbo.tablecounter 'sys', 'columns'; results in the output below. Notice that we also get the name of the SP the error occurs in (obj_name).

Logging your errors

We’ve got the tools to create a very nice function to log all of our errors. Lets start with creating a table that will store all of our error information.

Now we modify our Stored Procedure from earlier so that it will log the error if one occurs. Also it will print out a message so we can test it:

Now we have a table that we can monitor to see all of the errors that occurred in our database where we implemented the error logging functionalities. Let’s try it a few times with the queries below. The first query will succeed; the rest will fail.EXEC dbo.tablecounter 'sys', 'columns';
EXEC dbo.tablecounter 'sys', 'taaables';
EXEC dbo.tablecounter 'nope', 'columns';
EXEC dbo.tablecounter null, 'columns';Select *
FROM dbo.ErrorLog;

Executing these procedures will result in the following records in our ErrorLog Table.

As you can see you can filter this table by the ErrorDateTime and my the perpetrator (userName). Also you can trace back the code with the ObjectName (our error took place in the “tablecounter ” stored procedure). Also notice that in our new script we have the possibility to RAISERRORs. We choose to let our script fail if either the schema name or table name is not set. We can then specify our own ErrorMessage (see record #3 in the ErrorLog table).

The possibilities are endless once we’ve implemented this table:

1. We can create a monitor that refreshes every minute and queries this table for records of the last minute
2. Add a new table to the ErrorLog that keeps track of its status (0 = new, 1 = solved etc)
3. We can use a trigger to extend functionalities even more by pushing messages into a message queue for example. You can connect a multitude of services on the message queue (receive an email as soon as an Error is Logged!)

Conclusion

Apply the Try-Catch blocks where needed in your code but don’t forget to log the errors. These provide valuable information for hunting down bugs that would otherwise go unnoticed. Happy hunting!

— Mike

P.S: like what I’m doing? Follow me!