Tuesday, September 27, 2011

SQL Server Error 1222 Lock Request Time Out Period Exceeded

Issue Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222).
Issue: In Sql Server 2000 we face this issue very frequently due to bug. Please see the screen shot above, which we got while expanding the tables (+) button.

In SQL server 2000 cannot grant a lock to a transaction on a resource because another transaction already owns a conflicting lock on that resource, the first transaction becomes blocked waiting on that resource. We will get these errors due to any transaction containing the statement is not rolled back or canceled by SQL Server.

Workaround: 
Step 1. Check SP_who2 and SP_lock for any locks or blocks and who is blocking it.
Step 2. If you found any locks then kill those locks.
Step 3. If you are not found any blocks then push this databases to single user mode and bring back to Multi user mode after 10 seconds. then it will works fine. 
Step 4. After doing above steps then all users can access the table through EM or SSMS
Step 5. Microsoft released one bug fix for this as well check msdn.

Here is the SQL Query for changing the database options 
In 2000 for pushing the Database to Single User Mode, use below script. 
use master 
GO 
EXEC sp_dboption 'DatabaseName', 'single user', 'true' 
GO 

In 2000 for bringing the Database to Multi User Mode, use below script.
use master 
GO 
EXEC sp_dboption 'DatabaseName', 'single user', 'false' 
GO
How to solve SQL Server Error 1222, Alternate Fix ERROR 1222, lock request time out period exceeded, error 1222, SQL Server Adding Index error 1222, in sql server 2000, in sql server 2005, in sql server 2008 R2, in sql server 2012, error 1222 sql server 2008, lock request timeout period expired error 1222, sql server 2005 error 1222 sql server 2005, sql error 12222, lock request timeout period expired error 1222, error sql 2008

0 comments:

Post a Comment