Lesson Learned #1: Capturing a TSQL Command Timeout

In many support cases that we worked our customer needs to know that is the query that caused a Command Timeout issue. In this example below, you could find out the extended event that we share to our customers in order to capture it.


ADD EVENT sqlserver.sql_batch_completed (
ACTION  (sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([result] <> (0)))
ADD TARGET package0.asynchronous_file_target(
SET filename='https://azureblobcontainer.blob.core.windows.net/xe-container/DemoPersistedTimeout.xel')


If you need to reproduce a TSQL command timeout, basically you need to:

  • Create a stored procedure that takes more time that you expected in your application:

create PROC usp_Timeout
select 1
waitfor delay '00:00:10'

  • Try to specify in the SQL SERVER Management Studio->Command Timeout or in your application change the command timeout parameter with a value less than you have in waitfor (in this case, 5 seconds) 

using (SqlConnection awConnectionDb = new SqlConnection(connectionStringDb))
SqlCommand cmd1 = awConnectionDb.CreateCommand();
cmd1.CommandTimeout = 5;
cmd1.CommandText = string.Format("usp_Timeout");

In this case please open a support case to get assistance by our team.

Comments (1)

  1. Jhon says:

    This extended event will capture all transactions that result is different from 0, like error on PK or error on abort, not only time out.

Skip to main content