Capturing Time-Out Procedures using Extended Events

Due to various reasons, your procedures will fail after time out period elapsed.

 

But with the above generic error message, it is difficult to find out which procedure was failed.

Obvious way to find out timing out procedures are to debug your code. However, sometimes you might not be able to create this issue in your environments, since time-out error might depend on the environment setting or environment data.

So let us create a procedure which will time out.

Since this proc is having a delay for 20 hrs, it will obviously will fail with time out.

Let us call this procedure from C# application.

Though profiler can be used to capture this, running profiler may decrease your system performance. Microsoft introduced Extended Events to SQL Server, calming it is a light weight monitoring

In extended event, there is an event type called Event pairing, shows incomplete events. There are some events which are pairs such as lock acquired and lock released and from this event type we can capture incomplete events.

In our scenario, paired events are sp_statement_starting and sp_statement_completed and in case of a timing out, sp_statement_Completed event will not be completed.

You can create a extended event like following/

 

Then we can capture the event by running following query.

 

This result will give you

 

This means, though there are 11127 matched events, there is one orphan event which is what we are after.

If you expand the XML, it will give you the database id and the object id from which can find the culprit.

Interesting thing here is, sql_text is shown as Unable to retrieve SQL text, but if you execute this proc in SSMS, you will see the relevant proc in this XML it self.