SQL Server Error 1222 Lock Request Time Out Period Exceeded

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