Lesson Learned #1: Capturing a TSQL Command Timeout

  • Article

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.

 

CREATE EVENT SESSION ssEventoTimeout
ON DATABASE
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
as
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))
{
awConnectionDb.Open();
SqlCommand cmd1 = awConnectionDb.CreateCommand();
cmd1.CommandTimeout = 5;
cmd1.CommandText = string.Format("usp_Timeout");
cmd1.ExecuteNonQuery();
}

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