Error: 1222 Lock request time period exceeded

Question

Post reply

Error: 1222 Lock request time period exceeded

Jpotucek

SSCoach

Points: 18583
More actions
May 19, 2005 at 10:55 am

#89009

Error: 1222 Lock request time period exceeded
I get this error when I try and expand the
Management
Current Activity
Process Info Menu in Enterprise Manager
I have never seen this before and I'm not really sure where to go looking for the error code. Nothing unusual in Event View Logs or SQL Server Logs..?????????????

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jpotucek SSCoach Points: 18583 More actions · Answer 1

I found this article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;308518

but it's unclear to me if it's even something to worry about????

Dinand Veldman SSC Enthusiast Points: 152 More actions · Answer 2

Hi,

Try to run the system stored procedure sp_who2 in Query Analyzer to find out if a (dead) lock is present

How?

Type exec sp_who2 in Query Analyzer and hit [Enter]. Then look in the column BlkBy if a record shows a ProcesID. If so, you can kill the proces by typing

kill {procid} [Enter]

in Query Analyzer. {procid} is the number you would see in the column BlkBy when a lock is present

paksyeet SSCommitted Points: 1596 More actions · Answer 3

any one..

pls healp... i got the same problem and did sp_who2 but BlkBy column returns only "." character..

tchenry62 SSC Rookie Points: 44 More actions · Answer 4

OK, I think I can shed some light on this problem... Here is a quick script to identify SPIDs that are causing the "Error 1222: Lock request time out period exceeded." and/or "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." that was provided by Andrew Chen @ siusic.com

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

This script will show which table was locked, the process that locked the table and the login name used by the process. Once you find out which process is locking a table, you can issue a "kill" on that SPID. The offending process will be causing a lock on the syscolumns, syscomments, and sysobjects tables.

Also, what I found when running sp_who2 was that the offending process has a status of "sleeping" and a command of "AWAITING COMMAND". So, trying to find processes that are blocked and blocking (deadlocked) won't clear up the 1222 error.

You'll have to use SQL Profiler and track down the offending SQL event/command.

Good luck,

Tom Henry

Jamie Bates SSC Rookie Points: 25 More actions · Answer 5

This was awesome. I have added my VB solution using ADO to help identify the req_spid.

On Error GoTo errLockTable

'1. determine if the tdatWeeklyDetail table is locked.

'2. return the req_spid for this file

'3. use the KILL command of the given req_spid

Dim rstLockTable As ADODB.Recordset

Dim cmdKillLockTable As ADODB.Command

Dim strLockTable As String

strLockTable = "SELECT DISTINCT a.req_spid" & vbLf & _

"FROM Master.dbo.syslockinfo a (NOLOCK) JOIN Master.dbo.sysprocesses b (NOLOCK) ON a.req_spid=b.spid" & vbLf & _

"WHERE Object_Name(a.rsc_objID)='tmakWeeklyDetail'"

Set rstLockTable = New ADODB.Recordset

rstLockTable.Open strLockTable, "Provider=sqloledb;Data Source=KSA;Initial Catalog=TempDB;User Id=;Password=;"

Set cmdKillLockTable = New ADODB.Command

With cmdKillLockTable

.Cancel

.ActiveConnection = "Provider=sqloledb;Data Source=KSA;Initial Catalog=TempDB;User Id=;Password=;"

.CommandTimeout = 30

.CommandText = "KILL " & rstLockTable.Fields("req_spid").Value

.Execute

End With

Exit Sub

errLockTable:

Debug.Print Err.Number & vbLf & Err.Description

Stop