Article ID: 308518 - View products that this article applies to.
This article was previously published under Q308518
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
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:
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).
REFERENCESFor additional information about troubleshooting blocking problems on SQL Server, click the article number below to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/224453/EN-US/ )INF: Resolving SQL Server 7.0 or 2000 Blocking Problems