Error 1222: Lock request time out period exceeded
-
Our server is set up with connections settings with a timeout of 0 (unlimited). However we have suddenly developed a problem in Enterprise Manager.
When accessing Enterprise Manager, Management, Current Activity, Process Info, we get the error
Error 1222: Lock request time out period exceeded
Refreshing or disconnecting/connecting the server does not clear the error. Users of the production application, which uses that server, are experiencing difficulties but still able to work and log in. There is only the one application on that server.
Has anyone got any ideas? Nothing has been changed in SQL Server for some considerable time.
Madame Artois
-
The Lock request time out is set from the client application in your case EM. Opening Enterprise Manager, Management, Current Activity, Process Info actually executes a series of stored procedures which to be honest are not really optimized. Depending on which type of locks are placed on your server it can happen that one of these stored procedures keeps waiting until EM says it's enough I won't wait any longer.
My advice is use sp_who2 or sysprocesses to monitor the sessions instead.
Markus
[font="Verdana"]Markus Bohse[/font]
-
-
Hi
That latest link does not work or there is a temporary problem with the site.
I also get the time out error.
I've managed to trace it back to a "SELECT INTO" statement in an SP which runs for about 26 minutes in a job where one of the 3 tables in the select sits on another server but I'm not sure if this has anything to do with it.
What I did however do was change the code (not mine by the way) to drop the table then recreate it and then do INSERT INTO , SELECT FROM instead of the SELECT INTO. and this resolves the issue.
My hypothesis is that the SELECT INTO locks the system table which stores the new entry for the destination table and the EM can not access this table to build/refresh the list in its menu's thus causing the lock error.
However having said that and having to come to know this system which I work on quite a bit there are "SELECT INTO" strewn all over in SP's which would mean there is an additional reason which I believe is probably the combination of 3rd table sitting on the foreign server.
Doug
-
Doh! Same error happened to me in the SQL Server Management Studio. Apparently I set the query text editor options to use implicit transactions. So if I run update/ddl queries without commiting I would get this error, since the DB is still locked by that transaction. Calling commit at the end helps to get rid of it.
Hope this helps...
-
serge2000 (7/3/2009)
Doh! Same error happened to me in the SQL Server Management Studio. Apparently I set the query text editor options to use implicit transactions. So if I run update/ddl queries without commiting I would get this error, since the DB is still locked by that transaction. Calling commit at the end helps to get rid of it.Hope this helps...
Man I could kiss you. 😛
-
I'm glad that helped. A "thank you" would be enough though 😀
-
THANK YOU.
-
Someone had a query analyzer window hopen than had a BEGIN TRAN statement with no ROLLBACK or COMMIT statement and it was locking-up the system tables. Wow, what a horrible problem to try to fix!
-
Thanks a lot.....
- S Hodkinson - Thursday, September 21, 2006 6:23 AM
Our server is set up with connections settings with a timeout of 0 (unlimited). However we have suddenly developed a problem in Enterprise Manager.
When accessing Enterprise Manager, Management, Current Activity, Process Info, we get the error
Error 1222: Lock request time out period exceeded
Refreshing or disconnecting/connecting the server does not clear the error. Users of the production application, which uses that server, are experiencing difficulties but still able to work and log in. There is only the one application on that server.
Has anyone got any ideas? Nothing has been changed in SQL Server for some considerable time.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply