Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

Problem: Normally, executing the Rebuild Index will be completed quickly, but today I encountered the Job of Rebuild Index Running all the time. Manually Rebuild, again failing, report “Lock Request Time out Period Exceeded. (Microsoft SQL Server, Error: 1222)”, as shown below:

Solution:
Look at the SPID and SQL Text of the current Running, especially for long-running ones, find the SPID associated with the tables that execute Rebuild Index, and KILL it. What I currently have is an SQL that has been running for hours, and after killing the SPID, it can Rebuild Index properly.

SELECT r.session_id, r.status, r.start_time, r.command, s.text, r.wait_time, r.cpu_time, 
r.total_elapsed_time, r.reads, r.writes, r.logical_reads, r.transaction_isolation_level 
FROM sys.dm_exec_requests r 
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE R.STATUS='running'

In addition, you can check the information related to Lock:

select distinct object_name(a.rsc_objid), a.req_spid, b.loginame 
from master.dbo.syslockinfo a (nolock) join 
master.dbo.sysprocesses b (nolock) on a.req_spid=b.spid 
where object_name(a.rsc_objid) is not null

View the specified SPID

USE master;  
GO  
EXEC sp_who '267' --process_id;  
GO

For more detailed information:

DECLARE @SessionID INT=63
SELECT
     SPID                = er.session_id 
     ,Status             = ses.status 
     ,[Login]            = ses.login_name 
     ,Host               = ses.host_name 
     ,BlkBy              = er.blocking_session_id 
     ,DBName             =DB_Name(er.database_id) 
     ,CommandType        = er.command 
     ,SQLStatement       = st.text 
     ,ObjectName         =OBJECT_NAME(st.objectid) 
     ,ElapsedMS          = er.total_elapsed_time 
     ,CPUTime            = er.cpu_time 
     ,IOReads            = er.logical_reads + er.reads 
     ,IOWrites           = er.writes 
     ,LastWaitType       = er.last_wait_type 
     ,StartTime          = er.start_time 
     ,Protocol           = con.net_transport 
     ,ConnectionWrites   = con.num_writes 
     ,ConnectionReads    = con.num_reads 
     ,ClientAddress      = con.client_net_address 
     ,Authentication     = con.auth_scheme 
 FROM sys.dm_exec_requests er 
 OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st 
 LEFT JOIN sys.dm_exec_sessions ses 
 ON ses.session_id = er.session_id 
 LEFT JOIN sys.dm_exec_connections con 
 ON con.session_id = ses.session_id 
 WHERE er.session_id > 50 
     AND @SessionID IS NULL OR er.session_id = @SessionID 
 ORDER BY
     er.blocking_session_id DESC
     ,er.session_id

 

Read More: