Table of Contents
SQL Server Error 1222 lock request time out period exceeded
The error message:
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.
Why and How to fix:
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.
Lock request time out period exceeded using SSMS
SQL Server Error 1222 lock request time out period exceeded
Related Messages are:
- how do i fix the lock request time out period exceeded in sql server
- lock request time out period exceeded. (microsoft sql server, error: 1222)
- sql server lock request time out period exceeded when expanding table
- how to fix lock request time out period exceeded
- com microsoft sqlserver jdbc sqlserverexception lock request time out period exceeded
- lock request time out period exceeded drop table
- sql server rebuild index lock request time out period exceeded
- dbcc opentran
Why is happening this error?
Lock request time out period exceeded using SSMS
Now below query can give the list of long running queries..
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;