INF: Refresh of Current Activity in SEM May Cause "Error 1222: Lock Request time out period exceeded" Error Message

This article was previously published under Q308518
This article has been archived. It is offered "as is" and will no longer be updated.
SUMMARY
When you attempt to expand or refresh Current Activity in the SQL Enterprise Manager, you may receive this error message:
Error 1222: Lock Request time out period exceeded
MORE INFORMATION
A refresh of Current Activity calls the sp_MSset_current_activity stored procedure. In the definition of the stored procedure, the lock_timeout variable is set as 5000 milliseconds (ms) first. Then, two global temporary tables are created, which issue an IX locks on the table level and X locks on the Key level against these system tables:
  • tempdb..sysobjects
  • tempdb..sysindexes
  • tempdb..syscolumns
If the preceding locks are not granted within 5000 ms to the process, the query stops and the error message shown in the "Symptoms" section appears.

A second refresh usually results in success. However, if the locks are held for an extended period the error message may occur again. If you execute the sp_lock stored procedure, you can evaluate which process is holding locks on Object IDs 1, 2 and 3 of the dbid 2 (tempdb).

REFERENCES

For additional information about troubleshooting blocking problems on SQL Server, click the article number below to view the article in the Microsoft Knowledge Base:
224453 INF: Resolving SQL Server 7.0 or 2000 Blocking Problems

Properties

Article ID: 308518 - Last Review: 12/06/2015 05:55:08 - Revision: 3.2

Microsoft SQL Server 7.0 Standard Edition, Microsoft SQL Server 2000 Standard Edition

  • kbnosurvey kbarchive kbinfo KB308518
Feedback