In many support cases that we worked our customer needs to know that is the query that caused a Command Timeout issue, for example, receiving an error message like Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. In this example below, you could find out the extended event that we share to our customers in order to capture it.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123!!!!'
CREATE DATABASE SCOPED CREDENTIAL [https://azureblobcontainer.blob.core.windows.net/xe-container] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2019-12-12&ss=b&srt=sco&sp=rwdlacx&se=2020-10-28T23:38:22Z&st=2020-09-29T14:38:22Z&spr=https&sig=24TJMAf1hOzE2DDVqw1QRs....'
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')
ALTER EVENT SESSION ssEventoTimeout ON DATABASE STATE=START
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();
}
Right now, we need to stop the extended event running the following query and review the file generated in our blob storage.
ALTER EVENT SESSION ssEventoTimeout ON DATABASE STATE=STOP
Enjoy!