Error 1222: lock request timeout period exceeded 
Author Message
 Error 1222: lock request timeout period exceeded

I have SQL server 2000 with about 50-75 connections and have been
having a problem with blocking.  I find out about it when users start
getting timeout errors in a VB application.  Up until now I was able
to find the SPID in the current activity section of the enterprise
manager and kill the process that was doing the blocking, which freed
up the others that were timing out.  Today, it has gotten worse (or
something else is happening).  I can't even open the current activity
without getting the error "Error 1222: lock request timeout period
exceeded".  This has happend about 4 times today.  When I am able to
see the current activity and show the blocking spid, the sql query
that is being run is a simple select that runs quickly from the query
analyzer.

Please help.

Joel Hammond



Tue, 27 Apr 2004 06:15:44 GMT
 Error 1222: lock request timeout period exceeded

Is it possible that the table the simple query is working from is simply an
unindexed heap?
Try posting some DDL and a sample query.
Try using SQL Profiler to see what's happening when the locks occur.


Quote:
> I have SQL server 2000 with about 50-75 connections and have been
> having a problem with blocking.  I find out about it when users start
> getting timeout errors in a VB application.  Up until now I was able
> to find the SPID in the current activity section of the enterprise
> manager and kill the process that was doing the blocking, which freed
> up the others that were timing out.  Today, it has gotten worse (or
> something else is happening).  I can't even open the current activity
> without getting the error "Error 1222: lock request timeout period
> exceeded".  This has happend about 4 times today.  When I am able to
> see the current activity and show the blocking spid, the sql query
> that is being run is a simple select that runs quickly from the query
> analyzer.

> Please help.

> Joel Hammond




Tue, 27 Apr 2004 18:17:56 GMT
 Error 1222: lock request timeout period exceeded
I get *always* the same error when trying to look for the current activity
in EM, but when i execute sp_who2 show no one blocked, any idea?

-rt


Quote:
> Is it possible that the table the simple query is working from is simply
an
> unindexed heap?
> Try posting some DDL and a sample query.
> Try using SQL Profiler to see what's happening when the locks occur.



> > I have SQL server 2000 with about 50-75 connections and have been
> > having a problem with blocking.  I find out about it when users start
> > getting timeout errors in a VB application.  Up until now I was able
> > to find the SPID in the current activity section of the enterprise
> > manager and kill the process that was doing the blocking, which freed
> > up the others that were timing out.  Today, it has gotten worse (or
> > something else is happening).  I can't even open the current activity
> > without getting the error "Error 1222: lock request timeout period
> > exceeded".  This has happend about 4 times today.  When I am able to
> > see the current activity and show the blocking spid, the sql query
> > that is being run is a simple select that runs quickly from the query
> > analyzer.

> > Please help.

> > Joel Hammond




Wed, 28 Apr 2004 01:41:16 GMT
 Error 1222: lock request timeout period exceeded
Joel,

Check out Q308518. It appears to describe the problem you are having with
the 1222 error. As for the blocking issue, that may take some research to
find out what is going on. You might want to consider open a case with
Microsoft Product Support to investigate it. Q224453 discusses resolving
blocking issues as well.

You can view any Microsoft Knowledge Base aricle by entering mskb <article
number> in the URL  line in Internet Explorer.

Rand Boyd
Microsoft SQL Server Support

Please reply only to the newsgroups.
When posting, please state the version of SQL Server being used and the
error number/exact error  message text received, if any.

This posting is provided AS IS, with no warranties, and confers no rights.



Wed, 28 Apr 2004 00:43:42 GMT
 Error 1222: lock request timeout period exceeded
I also encounter similar problem with SQL 7.0. My situation having user
session around 120 sessioins. "Timeout problem" in client application occurs
when some SPID process hold the database resources indefinitely and does not
release by itself (I have asked user what he is doing with the client VB
application and he said he had finished the process, may be adding or
updating the records). I need to kill the process manually in order to free
up the resources for other process.

Any idea to free up or kill the SPID which hold the resource and not release
it?

Any idea to set the client VB application with no timeout value (currently
the client is using VB6 + ADO2.5)?



Quote:
> Joel,

> Check out Q308518. It appears to describe the problem you are having with
> the 1222 error. As for the blocking issue, that may take some research to
> find out what is going on. You might want to consider open a case with
> Microsoft Product Support to investigate it. Q224453 discusses resolving
> blocking issues as well.

> You can view any Microsoft Knowledge Base aricle by entering mskb <article
> number> in the URL  line in Internet Explorer.

> Rand Boyd
> Microsoft SQL Server Support

> Please reply only to the newsgroups.
> When posting, please state the version of SQL Server being used and the
> error number/exact error  message text received, if any.

> This posting is provided AS IS, with no warranties, and confers no rights.



Wed, 28 Apr 2004 01:17:46 GMT
 Error 1222: lock request timeout period exceeded
following the KB article, find a SPID who has a lock over the 1,2,3 in db 2,
among other objects.  Then i tried to kill him, with no result, now i get
the following situation:

sp_lock:

65 7 0 0 DB                  S GRANT
65 2 6 0 TAB                  IX GRANT
65 2 1 0 TAB                  IX GRANT
65 2 3 0 TAB                  IX GRANT
65 2 2 0 TAB                  IX GRANT
65 2 12 0 TAB                  IX GRANT
65 2 9 0 TAB                  IX GRANT
65 2 11 0 TAB                  IX GRANT
65 2 1 1 KEY (2100b0f9d6e7)   X GRANT
65 2 3 2 KEY (030152da2d22)   X GRANT
65 2 0 0 PAG 1:95             X GRANT
65 2 99 0 RID 1:142:0          X GRANT
65 2 0 0 PAG 1:142            X GRANT
65 2 3 1 KEY (23000cdb62e4)   X GRANT
65 2 3 1 KEY (2200e274d7f6)   X GRANT
65 2 3 1 KEY (2500d08409c1)   X GRANT
65 2 1 3 KEY (2100c5f9b0a3)   X GRANT
65 2 2 1 KEY (2100d603a420)   X GRANT
65 2 3 2 KEY (250259c4eab1)   X GRANT
65 2 3 2 KEY (8b02a03066cb)   X GRANT
65 2 3 1 KEY (240069bcde5c)   X GRANT
65 2 3 1 KEY (2600b5e3b579)   X GRANT
65 2 0 0 IDX IDX: 2:478224161 X GRANT
65 2 478224161 0 TAB                  Sch-M GRANT
65 2 3 2 KEY (33020f9f9b3e)   X GRANT
65 2 1 2 KEY (9a167e0d9a91)   X GRANT
65 2 3 2 KEY (bc018f215cc6)   X GRANT

after kill 65 i've got th

sp_who2 :
65    RUNNABLE                       sigdo RTF2   .   Sigdopack
KILLED/ROLLBACK 1356172 71 10/17 20:04:54 SQL Query Analyzer 65

dbcc inpubuffer(65):
Language Event 0 exec master.dbo.sp_sdidebug 'off'

kill 65 with statusonly:
SPID 65: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds.

the same result for two hours,

and still getting the error on EM.

any suggestion?

-rt



Quote:
> Joel,

> Check out Q308518. It appears to describe the problem you are having with
> the 1222 error. As for the blocking issue, that may take some research to
> find out what is going on. You might want to consider open a case with
> Microsoft Product Support to investigate it. Q224453 discusses resolving
> blocking issues as well.

> You can view any Microsoft Knowledge Base aricle by entering mskb <article
> number> in the URL  line in Internet Explorer.

> Rand Boyd
> Microsoft SQL Server Support

> Please reply only to the newsgroups.
> When posting, please state the version of SQL Server being used and the
> error number/exact error  message text received, if any.

> This posting is provided AS IS, with no warranties, and confers no rights.



Wed, 28 Apr 2004 03:49:45 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. SQL Server 7: Error Msg - Error: 1222, Severity: 16, State: 50 Lock Request Timeout period exceeded

2. Error 1222 = Lock request time out period exceeded

3. Error 1222 : Lock request time out period exceeded in Enterprise Manager

4. Lock request time period exceeded error

5. Lock timeout error 1222

6. Lock timeout error 1222

7. trapping error 1222 (lock timeout)

8. Unstable sql server - locking/blocking/1222 errors help pls

9. 1222 (lock time out) is untrappable

10. Lock 1222 with no blocks

11. Lock Request Time out Period ..

12. Lock request timeout when viewing locks


 
Powered by phpBB® Forum Software