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


The information in this article applies to:

  • Microsoft SQL Server 7.0
  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q308518

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


Modification Type:MajorLast Reviewed:10/31/2003
Keywords:kbinfo KB308518
©2003 Microsoft Corporation. All rights reserved.