Table of Contents
lock request time out period exceeded. (microsoft sql server, error: 1222)
SQL Server Error 1222 lock request time out period exceeded is related to the time out of query due to locking of resources in SQL server.
The error message “Lock request timeout period exceeded Error 1222” indicates that a transaction is holding a lock on the target resource for a longer period than the query could wait for.
This could be caused by a transaction running with a BEGIN TRAN but no ROLLBACK or COMMIT, potentially locking system tables.
To resolve this issue, identify the active transaction details in the SQL Server Management Studio by executing the command “DBCC opentran()”.
If the issue is believed to be caused by a transaction executed through the SSMS, shutting the SSMS may be a solution. This is a slightly drastic step but should usually resolve the problem.
SQL Server Error 1222 lock request time out period exceeded
SELECT
[s_tst].[session_id],
[s_es].[login_name] AS [Login Name],
DB_NAME (s_tdt.database_id) AS [Database],
[s_tdt].[database_transaction_begin_time] AS [Begin Time],
[s_tdt].[database_transaction_log_bytes_used] AS [Log Bytes],
[s_tdt].[database_transaction_log_bytes_reserved] AS [Log Rsvd],
[s_est].text AS [Last T-SQL Text],
[s_eqp].[query_plan] AS [Last Plan]
FROM
sys.dm_tran_database_transactions [s_tdt]
JOIN
sys.dm_tran_session_transactions [s_tst]
ON
[s_tst].[transaction_id] = [s_tdt].[transaction_id]
JOIN
sys.[dm_exec_sessions] [s_es]
ON
[s_es].[session_id] = [s_tst].[session_id]
JOIN
sys.dm_exec_connections [s_ec]
ON
[s_ec].[session_id] = [s_tst].[session_id]
LEFT OUTER JOIN
sys.dm_exec_requests [s_er]
ON
[s_er].[session_id] = [s_tst].[session_id]
CROSS APPLY
sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est]
OUTER APPLY
sys.dm_exec_query_plan ([s_er].[plan_handle]) AS [s_eqp]
where [s_tdt].[database_transaction_begin_time] is not null
ORDER BY
[Begin Time] ASC;
Learn about SQL Server detecting logical consistency based issues and how to resolve them.
Learn about SQL Server error 1222 and how to resolve the lock request time out…
Discover the new features of Microsoft SQL Server 2022 and how they compare to previous…
SQL Server Error : 427, Severity: 20. Could not load the definition for constraint ID…
SQL Server Error : 204, Severity: 20. Normalization error in node %ls.
SQL Server Error : 21, Severity: 20. Warning: Fatal error %d occurred at %S_DATE. Note…
This website uses cookies.