How to trace SQL Server database errors and exceptions with SQL Profiler

07 Oct 2015

Sometimes, when errors or exceptions happen you can’t just attach a debugger to your web app - for example, errors may happen only in production, and you can only sit and watch the error logs fly past, or sometimes there are even no meaningful logs available. Maybe you need to debug a closed system - proprietary component/NuGet package that fails. Maybe it’s a combination of several factors

How to catch the bastard

But if you know from the logs or other sources that error occurs when a database is accessed, you can find out which queries fail and why. Firstly, you need to attach SQL Profiler to an instance of SQL Server. Run SQL Profiler, from “File” menu select “New trace…”, then enter your server address and connection credentials, click “Connect”.

“Trace properties” window will appear, where on “General” tab, under “Use the template” select “Blank”

Trace window

Then on “Event Selection” tab click “Column filters” and enter your database name under “Like” filter. You can skip this step if you have just one database, otherwise, you’re risking being flooded with too many tracing messages.

Edit filter

Then again on “Event Selection” tab, in the event grid, select following events:

Errors and warnings

Stored procedures

TSQL

And then click “Run”. You should get a stream of events that looks something like the screen below. Do not freak out if there are too many events and they run screaming at you, which happens if you have a big system with lots of users. What you’re really interested in is lines that have “Exception” in the “EventClass” column, like this:

Events trace

And that exception is caused by the command just above, one with the title RPC: Starting:

Real cause, bitch!

So in this instance we can clearly tell that certain table is missing its ID column, which could be a result of missing/incomplete database schema update.

WANT MORE? SUBSCRIBE HERE

Do you hate wasting time on stupid bugs, environment problems and trying to make sense of obscure documentation? I’m the same! That’s why whenever I find a faster or easier way of doing anything related to software development, I share that with you here, on my blog.

So if you don’t want to miss my next article, sign up to the mailing list below. There’ll be lots more time-saving and frustration-avoiding advice there, so sign right up. I never spam, period.

Subscribe now and get helpful tips on developing .NET apps - never miss a new article.

You can unsubscribe at any time. I'll never share your email with anyone else.