'Lock request time out period exceeded' when connecting to the database from SSMS

Published 05-19-2021 09:48 AM 1,448 Views
Microsoft

When you connect to the database using SQL Server Management Studio (SSMS), It will perform several queries to gather the information necessary for the user interface. If one of the queries performed by SSMS is blocked, you may face a lock time-out in the connection:

 

Thamires_Lemes_0-1621442395428.png

 

You can use the PowerShell script below, while attempting to connect, to identify the session that is causing the block:

 

Note: It is necessary to set values for the highlighted variables. It will create CSVs with the result of the queries in the $OutputFolder.

 

#Connect to SQL and run QUERY

$SQLServer = ""

$SQLDBName = ""

$SQLUsername = ""

$SQLPassword = ""

$OuputFolder = "C:"

 

#To get information about sessions that are blocking and being blocked

$SqlQuery = "SELECT current_timestamp as [CURRENT_TIMESTAMP]

       , DB_NAME(dtl.resource_database_id) AS database_name

       , req.session_id AS blocked_sessionID

       , ses.program_name blocked_programName

       , ses.host_name blocked_hostname

       , ses.login_name blocked_login

       , CASE ses.transaction_isolation_level

              WHEN 1 THEN 'ReadUncomitted'

              WHEN 2 THEN 'ReadCommitted'

              WHEN 3 THEN 'Repeatable'

              WHEN 4 THEN 'Serializable'

              WHEN 5 THEN 'Snapshot'

       END blocked_isolation_level

       , REPLACE(REPLACE(sqltext.TEXT, CHAR(13), ' '), CHAR(10), ' ') AS blocked_last_query

       , req.status AS [blocked_status]

       , req.command AS blocked_command

       , req.cpu_time AS blocked_cpuTime

       , req.total_elapsed_time AS blocked_totalElapsedTime

       , blocked_tran.transaction_id blocked_transaction_id

       , osw.blocking_session_id AS blocker_SessionID

       , blocker_ses.program_name blocker_programName

       , blocker_ses.host_name blocker_hostName

       , blocker_ses.login_name blocker_login

       , CASE blocker_ses.transaction_isolation_level

              WHEN 1 THEN 'ReadUncomitted'

              WHEN 2 THEN 'ReadCommitted'

              WHEN 3 THEN 'Repeatable'

              WHEN 4 THEN 'Serializable'

              WHEN 5 THEN 'Snapshot'

       END blocker_isolation_level

       , REPLACE(REPLACE(iif(blocker_sqltext.TEXT is NULL,blocker_sqltext2.event_info,blocker_sqltext.TEXT), CHAR(13), ' '), CHAR(10), ' ') AS blocker_last_query

       , blocker_req.status AS [blocker_status]

       , blocker_req.command AS blocker_command

       , blocker_req.cpu_time AS blocker_cpuTime

       , blocker_req.total_elapsed_time AS blocker_totalElapsedTime

       , blocker_proc.lastwaittype blocker_last_waittype

       , blocker_proc.last_batch blocker_last_batch

       , blocker_proc.open_tran blocker_open_tran

       , blocker_tran.transaction_id blocker_transaction_id

       , blocker_proc.cmd blocker_command

       , dtl.request_mode AS lockRequestMode

       , dtl.resource_type AS lockResourceType

       , dtl.resource_subtype AS lockResourceSubType

       , osw.wait_type AS taskWaitType

       , osw.resource_description AS taskResourceDescription

       , osw.wait_duration_ms

FROM sys.dm_exec_requests req

INNER JOIN sys.dm_exec_sessions ses on ses.session_id = req.session_id

CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext

INNER JOIN sys.dm_tran_locks dtl on dtl.request_session_id = req.session_id

INNER JOIN sys.dm_os_waiting_tasks osw on osw.session_id = req.session_id

LEFT JOIN sys.dm_tran_session_transactions blocked_tran on blocked_tran.session_id =req.session_id

INNER JOIN dbo.sysprocesses blocker_proc on osw.blocking_session_id = blocker_proc.spid

LEFT JOIN sys.dm_exec_requests blocker_req on blocker_req.session_id = osw.blocking_session_id

LEFT JOIN sys.dm_exec_sessions blocker_ses on blocker_ses.session_id = osw.blocking_session_id

LEFT JOIN sys.dm_tran_session_transactions blocker_tran on blocker_tran.session_id =osw.blocking_session_id

OUTER APPLY sys.dm_exec_sql_text(blocker_req.sql_handle) AS blocker_sqltext

OUTER APPLY sys.dm_exec_input_buffer(osw.blocking_session_id,0) as blocker_sqltext2;"

 

#To get the locks that are being held by the sessions that are blocking and being blocked

$SqlQuery2 = "

select DB_NAME(locks.resource_database_id) AS database_name

 , locks.request_session_id

 , locks.resource_type, locks.resource_subtype

 , locks.resource_description

 , locks.resource_associated_entity_id

 , locks.resource_lock_partition

 , locks.request_mode

 , locks.request_type

 , locks.request_status

 , locks.request_reference_count

 , locks.request_lifetime

 , locks.request_exec_context_id

 , locks.request_request_id

 , locks.request_owner_type

FROM sys.dm_exec_requests req

INNER JOIN sys.dm_os_waiting_tasks osw on osw.session_id = req.session_id

INNER JOIN sys.dm_tran_locks locks on osw.blocking_session_id = locks.request_session_id or (osw.session_id = locks.request_session_id and osw.blocking_session_id is not null)

order by locks.request_session_id;"

 

#Dummy query to test if queries are running successfully, in case the previous ones do not return data

$SqlQuery3 = "select CURRENT_TIMESTAMP as timestamp, @@SERVERNAME as server_name, DB_NAME() as database_name, @@SPID as session_id;"

 

#Connect to SQL Server

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $SQLUsername; Password = $SQLPassword"

 

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.Connection = $SqlConnection

 

#Create the objects that will be used to run the queries

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

 

#Run 1st query

$DataSet = New-Object System.Data.DataSet

$SqlCmd.CommandText = $SqlQuery

$SqlAdapter.SelectCommand = $SqlCmd

$SqlAdapter.Fill($DataSet)

 

#Output RESULTS of 1st query to CSV

$DataSet.Tables[0] | Export-Csv -Delimiter ';' -Path "$OuputFolder\lock_waits_$(get-date -f yyyy-MM-dd-HH.mm.ss).csv" -NoTypeInformation

 

#Run 2nd query

$DataSet = New-Object System.Data.DataSet

$SqlCmd.CommandText = $SqlQuery2

$SqlAdapter.SelectCommand = $SqlCmd

$SqlAdapter.Fill($DataSet)

 

#Output RESULTS of 2nd query to CSV

$DataSet.Tables[0] | Export-Csv -Delimiter ';' -Path "$OuputFolder\lock_list_$(get-date -f yyyy-MM-dd-HH.mm.ss).csv" -NoTypeInformation

 

#Run 3rd query

$DataSet = New-Object System.Data.DataSet  

$SqlCmd.CommandText = $SqlQuery3

$SqlAdapter.SelectCommand = $SqlCmd

$SqlAdapter.Fill($DataSet) 

 

#Output RESULTS of 3rd query to CSV

$DataSet.Tables[0] | Export-Csv -Delimiter ';' -Path "$OuputFolder\test_connection_$(get-date -f yyyy-MM-dd-HH.mm.ss).csv" -NoTypeInformation

 

#Close the connection

$SqlConnection.Close()

 

 

In the CSV files, you should be able to see the last queries of the sessions involved in the block, their isolation level, if they are inside a transaction and the locks they are holding. This should help you understand why the block is happening.

 

This PowerShell script uses two queries from this blog post. Please refer to this blog post for additional information.

 

References:

https://techcommunity.microsoft.com/t5/azure-database-support-blog/troubleshooting-high-lock-wait-ti...

 

%3CLINGO-SUB%20id%3D%22lingo-sub-2368943%22%20slang%3D%22en-US%22%3E'Lock%20request%20time%20out%20period%20exceeded'%20when%20connecting%20to%20the%20database%20from%20SSMS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2368943%22%20slang%3D%22en-US%22%3E%3CP%3E%3C%2FP%3E%0A%3CDIV%20class%3D%22WordSection1%22%3E%0A%3CP%20class%3D%22MsoNormal%22%3EWhen%20you%20connect%20to%20the%20database%20using%20SQL%20Server%20Management%20Studio%20(SSMS)%2C%20It%20will%20perform%20several%20queries%20to%20gather%20the%20information%20necessary%20for%20the%20user%20interface.%20If%20one%20of%20the%20queries%20performed%20by%20SSMS%20is%20blocked%2C%20you%20may%20face%20a%20lock%20time-out%20in%20the%20connection%3A%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-bottom%3A%200mm%3B%22%3E%26nbsp%3B%3C%2FP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Thamires_Lemes_0-1621442395428.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F281873i4A0E0E85978FBC7C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Thamires_Lemes_0-1621442395428.png%22%20alt%3D%22Thamires_Lemes_0-1621442395428.png%22%20%2F%3E%3C%2FSPAN%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-bottom%3A%200mm%3B%22%3EYou%20can%20use%20the%20PowerShell%20script%20below%2C%20while%20attempting%20to%20connect%2C%20to%20identify%20the%20session%20that%20is%20causing%20the%20block%3A%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-bottom%3A%200mm%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-bottom%3A%200mm%3B%22%3ENote%3A%20It%20is%20necessary%20to%20set%20values%20for%20the%20highlighted%20variables.%20It%20will%20create%20CSVs%20with%20the%20result%20of%20the%20queries%20in%20the%20%24OutputFolder.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-bottom%3A%200mm%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CTABLE%20class%3D%22MsoTableGrid%22%20style%3D%22border-collapse%3A%20collapse%3B%20border%3A%20none%3B%22%20border%3D%221%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%0A%3CTBODY%3E%0A%3CTR%3E%0A%3CTD%20width%3D%22623%22%20valign%3D%22top%22%20style%3D%22width%3A%20467.5pt%3B%20border%3A%20solid%20windowtext%201.0pt%3B%20padding%3A%200mm%205.4pt%200mm%205.4pt%3B%22%3E%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%23Connect%20to%20SQL%20and%20run%20QUERY%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20background%3A%20yellow%3B%22%3E%24SQLServer%20%3D%20%22%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20background%3A%20yellow%3B%22%3E%24SQLDBName%20%3D%20%22%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20background%3A%20yellow%3B%22%3E%24SQLUsername%20%3D%20%22%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20background%3A%20yellow%3B%22%3E%24SQLPassword%20%3D%20%22%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%20background%3A%20yellow%3B%22%3E%24OuputFolder%20%3D%20%22C%3A%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%23To%20get%20information%20about%20sessions%20that%20are%20blocking%20and%20being%20blocked%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlQuery%20%3D%20%22SELECT%20current_timestamp%20as%20%5BCURRENT_TIMESTAMP%5D%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%2C%20DB_NAME(dtl.resource_database_id)%20AS%20database_name%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20req.session_id%20AS%20blocked_sessionID%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20ses.program_name%20blocked_programName%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20ses.host_name%20blocked_hostname%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20ses.login_name%20blocked_login%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20CASE%20ses.transaction_isolation_level%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20WHEN%201%20THEN%20'ReadUncomitted'%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20WHEN%202%20THEN%20'ReadCommitted'%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20WHEN%203%20THEN%20'Repeatable'%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20WHEN%204%20THEN%20'Serializable'%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20WHEN%205%20THEN%20'Snapshot'%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20END%20blocked_isolation_level%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20REPLACE(REPLACE(sqltext.TEXT%2C%20CHAR(13)%2C%20'%20')%2C%20CHAR(10)%2C%20'%20')%20AS%20blocked_last_query%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20req.status%20AS%20%5Bblocked_status%5D%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20req.command%20AS%20blocked_command%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20req.cpu_time%20AS%20blocked_cpuTime%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20req.total_elapsed_time%20AS%20blocked_totalElapsedTime%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20blocked_tran.transaction_id%20blocked_transaction_id%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20osw.blocking_session_id%20AS%20blocker_SessionID%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20blocker_ses.program_name%20blocker_programName%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20blocker_ses.host_name%20blocker_hostName%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20blocker_ses.login_name%20blocker_login%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20CASE%20blocker_ses.transaction_isolation_level%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20WHEN%201%20THEN%20'ReadUncomitted'%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20WHEN%202%20THEN%20'ReadCommitted'%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20WHEN%203%20THEN%20'Repeatable'%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20WHEN%204%20THEN%20'Serializable'%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20WHEN%205%20THEN%20'Snapshot'%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20END%20blocker_isolation_level%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20REPLACE(REPLACE(iif(blocker_sqltext.TEXT%20is%20NULL%2Cblocker_sqltext2.event_info%2Cblocker_sqltext.TEXT)%2C%20CHAR(13)%2C%20'%20')%2C%20CHAR(10)%2C%20'%20')%20AS%20blocker_last_query%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20blocker_req.status%20AS%20%5Bblocker_status%5D%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20blocker_req.command%20AS%20blocker_command%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20blocker_req.cpu_time%20AS%20blocker_cpuTime%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20blocker_req.total_elapsed_time%20AS%20blocker_totalElapsedTime%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20blocker_proc.lastwaittype%20blocker_last_waittype%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20blocker_proc.last_batch%20blocker_last_batch%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20blocker_proc.open_tran%20blocker_open_tran%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20blocker_tran.transaction_id%20blocker_transaction_id%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20blocker_proc.cmd%20blocker_command%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20dtl.request_mode%20AS%20lockRequestMode%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20dtl.resource_type%20AS%20lockResourceType%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20dtl.resource_subtype%20AS%20lockResourceSubType%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20osw.wait_type%20AS%20taskWaitType%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20osw.resource_description%20AS%20taskResourceDescription%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%2C%20osw.wait_duration_ms%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3EFROM%20sys.dm_exec_requests%20req%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3EINNER%20JOIN%20sys.dm_exec_sessions%20ses%20on%20ses.session_id%20%3D%20req.session_id%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3ECROSS%20APPLY%20sys.dm_exec_sql_text(req.sql_handle)%20AS%20sqltext%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3EINNER%20JOIN%20sys.dm_tran_locks%20dtl%20on%20dtl.request_session_id%20%3D%20req.session_id%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3EINNER%20JOIN%20sys.dm_os_waiting_tasks%20osw%20on%20osw.session_id%20%3D%20req.session_id%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3ELEFT%20JOIN%20sys.dm_tran_session_transactions%20blocked_tran%20on%20blocked_tran.session_id%20%3Dreq.session_id%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3EINNER%20JOIN%20dbo.sysprocesses%20blocker_proc%20on%20osw.blocking_session_id%20%3D%20blocker_proc.spid%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3ELEFT%20JOIN%20sys.dm_exec_requests%20blocker_req%20on%20blocker_req.session_id%20%3D%20osw.blocking_session_id%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3ELEFT%20JOIN%20sys.dm_exec_sessions%20blocker_ses%20on%20blocker_ses.session_id%20%3D%20osw.blocking_session_id%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3ELEFT%20JOIN%20sys.dm_tran_session_transactions%20blocker_tran%20on%20blocker_tran.session_id%20%3Dosw.blocking_session_id%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3EOUTER%20APPLY%20sys.dm_exec_sql_text(blocker_req.sql_handle)%20AS%20blocker_sqltext%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3EOUTER%20APPLY%20sys.dm_exec_input_buffer(osw.blocking_session_id%2C0)%20as%20blocker_sqltext2%3B%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%23To%20get%20the%20locks%20that%20are%20being%20held%20by%20the%20sessions%20that%20are%20blocking%20and%20being%20blocked%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlQuery2%20%3D%20%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3Eselect%20DB_NAME(locks.resource_database_id)%20AS%20database_name%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%200mm%200mm%2027.0pt%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%2C%20locks.request_session_id%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%200mm%200mm%2027.0pt%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%2C%20locks.resource_type%2C%20locks.resource_subtype%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%200mm%200mm%2027.0pt%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%2C%20locks.resource_description%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%200mm%200mm%2027.0pt%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%2C%20locks.resource_associated_entity_id%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%200mm%200mm%2027.0pt%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%2C%20locks.resource_lock_partition%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%200mm%200mm%2027.0pt%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%2C%20locks.request_mode%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%200mm%200mm%2027.0pt%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%2C%20locks.request_type%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%200mm%200mm%2027.0pt%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%2C%20locks.request_status%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%200mm%200mm%2027.0pt%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%2C%20locks.request_reference_count%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%200mm%200mm%2027.0pt%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%2C%20locks.request_lifetime%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%200mm%200mm%2027.0pt%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%2C%20locks.request_exec_context_id%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%200mm%200mm%2027.0pt%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%2C%20locks.request_request_id%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%200mm%200mm%2027.0pt%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%2C%20locks.request_owner_type%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3EFROM%20sys.dm_exec_requests%20req%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3EINNER%20JOIN%20sys.dm_os_waiting_tasks%20osw%20on%20osw.session_id%20%3D%20req.session_id%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3EINNER%20JOIN%20sys.dm_tran_locks%20locks%20on%20osw.blocking_session_id%20%3D%20locks.request_session_id%20or%20(osw.session_id%20%3D%20locks.request_session_id%20and%20osw.blocking_session_id%20is%20not%20null)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3Eorder%20by%20locks.request_session_id%3B%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%23Dummy%20query%20to%20test%20if%20queries%20are%20running%20successfully%2C%20in%20case%20the%20previous%20ones%20do%20not%20return%20data%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlQuery3%20%3D%20%22select%20CURRENT_TIMESTAMP%20as%20timestamp%2C%20%40%40SERVERNAME%20as%20server_name%2C%20DB_NAME()%20as%20database_name%2C%20%40%40SPID%20as%20session_id%3B%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%23Connect%20to%20SQL%20Server%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlConnection%20%3D%20New-Object%20System.Data.SqlClient.SqlConnection%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlConnection.ConnectionString%20%3D%20%22Server%20%3D%20%24SQLServer%3B%20Database%20%3D%20%24SQLDBName%3B%20User%20ID%20%3D%20%24SQLUsername%3B%20Password%20%3D%20%24SQLPassword%22%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlCmd%20%3D%20New-Object%20System.Data.SqlClient.SqlCommand%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlCmd.Connection%20%3D%20%24SqlConnection%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%23Create%20the%20objects%20that%20will%20be%20used%20to%20run%20the%20queries%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlAdapter%20%3D%20New-Object%20System.Data.SqlClient.SqlDataAdapter%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%23Run%201st%20query%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24DataSet%20%3D%20New-Object%20System.Data.DataSet%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlCmd.CommandText%20%3D%20%24SqlQuery%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlAdapter.SelectCommand%20%3D%20%24SqlCmd%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlAdapter.Fill(%24DataSet)%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%23Output%20RESULTS%20of%201st%20query%20to%20CSV%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24DataSet.Tables%5B0%5D%20%7C%20Export-Csv%20-Delimiter%20'%3B'%20-Path%20%22%24OuputFolder%5Clock_waits_%24(get-date%20-f%20yyyy-MM-dd-HH.mm.ss).csv%22%20-NoTypeInformation%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%23Run%202nd%20query%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24DataSet%20%3D%20New-Object%20System.Data.DataSet%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlCmd.CommandText%20%3D%20%24SqlQuery2%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlAdapter.SelectCommand%20%3D%20%24SqlCmd%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlAdapter.Fill(%24DataSet)%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%23Output%20RESULTS%20of%202nd%20query%20to%20CSV%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24DataSet.Tables%5B0%5D%20%7C%20Export-Csv%20-Delimiter%20'%3B'%20-Path%20%22%24OuputFolder%5Clock_list_%24(get-date%20-f%20yyyy-MM-dd-HH.mm.ss).csv%22%20-NoTypeInformation%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%23Run%203rd%20query%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24DataSet%20%3D%20New-Object%20System.Data.DataSet%20%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlCmd.CommandText%20%3D%20%24SqlQuery3%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlAdapter.SelectCommand%20%3D%20%24SqlCmd%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlAdapter.Fill(%24DataSet)%26nbsp%3B%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%23Output%20RESULTS%20of%203rd%20query%20to%20CSV%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24DataSet.Tables%5B0%5D%20%7C%20Export-Csv%20-Delimiter%20'%3B'%20-Path%20%22%24OuputFolder%5Ctest_connection_%24(get-date%20-f%20yyyy-MM-dd-HH.mm.ss).csv%22%20-NoTypeInformation%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%23Close%20the%20connection%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200mm%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%24SqlConnection.Close()%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-bottom%3A%200mm%3B%20line-height%3A%20normal%3B%22%3E%3CSPAN%20style%3D%22font-size%3A%209.5pt%3B%20font-family%3A%20Consolas%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3C%2FTD%3E%0A%3C%2FTR%3E%0A%3C%2FTBODY%3E%0A%3C%2FTABLE%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-bottom%3A%200mm%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-bottom%3A%200mm%3B%22%3EIn%20the%20CSV%20files%2C%20you%20should%20be%20able%20to%20see%20the%20last%20queries%20of%20the%20sessions%20involved%20in%20the%20block%2C%20their%20isolation%20level%2C%20if%20they%20are%20inside%20a%20transaction%20and%20the%20locks%20they%20are%20holding.%20This%20should%20help%20you%20understand%20why%20the%20block%20is%20happening.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-bottom%3A%200mm%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-bottom%3A%200mm%3B%22%3EThis%20PowerShell%20script%20uses%20two%20queries%20from%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Ftroubleshooting-high-lock-wait-time-and-lock-time-out%2Fba-p%2F2368875%22%20target%3D%22_blank%22%3Ethis%20blog%20post%3C%2FA%3E.%20Please%20refer%20to%20this%20blog%20post%20for%20additional%20information.%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-bottom%3A%200mm%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%20style%3D%22margin-bottom%3A%200mm%3B%22%3EReferences%3A%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Ftroubleshooting-high-lock-wait-time-and-lock-time-out%2Fba-p%2F2368875%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-database-support-blog%2Ftroubleshooting-high-lock-wait-time-and-lock-time-out%2Fba-p%2F2368875%3C%2FA%3E%3C%2FP%3E%0A%3CP%20class%3D%22MsoNormal%22%3E%26nbsp%3B%3C%2FP%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2368943%22%20slang%3D%22en-US%22%3E%3CP%3EWhen%20you%20connect%20to%20the%20database%20using%20SQL%20Server%20Management%20Studio%20(SSMS)%2C%20It%20will%20perform%20several%20queries%20to%20gather%20the%20information%20necessary%20for%20the%20user%20interface.%20If%20one%20of%20the%20queries%20performed%20by%20SSMS%20is%20blocked%2C%20you%20may%20face%20a%20lock%20time-out%20in%20the%20connection.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%26nbsp%3Bcan%20use%20a%20PowerShell%20script%2C%20while%20attempting%20to%20connect%2C%20to%20identify%20the%20session%20that%20is%20causing%20the%20block.%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Co-Authors
Version history
Last update:
‎May 19 2021 09:48 AM
Updated by: