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

by Contributed | May 19, 2021 | Technology

This article is contributed. See the original author and article here.

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:

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 “$OuputFolderlock_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 “$OuputFolderlock_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 “$OuputFoldertest_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-time-and-lock-time-out/ba-p/2368875

Experiencing Alerting failure for Log Search Alerts – 05/19 – Resolved

by Contributed | May 19, 2021 | Technology

This article is contributed. See the original author and article here.

Final Update: Wednesday, 19 May 2021 15:58 UTC

We’ve confirmed that all systems are back to normal with no customer impact as of 05/19, 15:45 UTC. Our logs show the incident started on 05/19, 14:30 UTC and that during the 1 hour 15 minutes that it took to resolve the issue customers may have experienced issues with missed or delayed alerts in Fairfax.

Root Cause: Engineering team has determined that part of the backend workflow for processing Log Search Alerts became unhealthy after it had reached an operational threshold.
Incident Timeline: 1 Hour & 15 minutes – 05/19, 14:30 UTC through 05/19, 15:45 UTC

We understand that customers rely on Log Search Alerts as a critical service and apologize for any impact this incident caused.

-Vincent

Cisco Releases Security Updates for Multiple Products  

by Scott Muniz | May 19, 2021 | Security, Technology

This article is contributed. See the original author and article here.

Cisco has released security updates to address vulnerabilities in multiple Cisco products. A remote attacker could exploit some of these vulnerabilities to take control of an affected system. For updates addressing lower severity vulnerabilities, see the Cisco Security Advisories page.

CISA encourages users and administrators to review the following Cisco advisories and apply the necessary updates:

Troubleshooting high lock wait time and lock time-out

by Contributed | May 19, 2021 | Technology

This article is contributed. See the original author and article here.

Locks are held in the database to ensure data consistency. If there are errors due to lock time-out or performance issues due to lock waits, the recommendation is to review the transactions that are involved in the locks to check if it is possible to change anything in their logic or isolation level to improve concurrency and avoid the blocks. Blocks can also be caused by transactions taking longer than expected and, in this case, it may require query tuning.

We can use T-SQL while the issue is happening to identify the queries involved in the block and the applications that are running them.

With the query below, we can see the sessions involved in the block, last query they ran, and which one is the head of the block. It might already give us enough information to identify where the issue is:

–Blocking tree

SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ‘ ‘), CHAR (13), ‘ ‘ ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE (‘0’, 4–LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N’    ‘ + REPLICATE (N’|         ‘, LEN (LEVEL)/4 – 1) +
CASE WHEN (LEN(LEVEL)/4 – 1) = 0
THEN ‘HEAD –  ‘
ELSE ‘|——  ‘ END
+ CAST (SPID AS NVARCHAR (10)) + N’ ‘ + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO

If the information provided by the last query is not enough and we have to look deeper into the sessions that are part of the block, we can run the query below:

–Details about the sessions that are blocking and being blocked:

SELECT current_timestamp as [CURRENT_TIMESTAMP]