SQL Server Performance Forum – Threads Archive

Lock request timeout period exceeded in EM and QA

One of my Business Analysts is getting this error in QA and EM. I think I’ve read about this somewhere before but can’t find anything on MSKB or here. I’ve had the problem myself but I’ve always found it to be caused by blocking. But she doesn’t appear to be getting blocked. Any ideas? It’s SQL2ksp3a client tools on Win XP Pro. TIA Tom Pullen
DBA, Oxfam GB

In EM is a common problem, because a GUI interface.
But, in QA no.
When is getting error?.
Running a query in QA, or with a simple sp_who?
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

No – browsing Stored Procs in the object browser in QA. Maybe it uses the same underlying objects or something? I suspect she is somehow blocking herself but I can’t quite work out how, why or when! You know how it is… Tom Pullen
DBA, Oxfam GB

Yes I think is using same undelying objects.
I use the following sp. DROP PROCEDURE sp_rk_blocker_blockee
GO CREATE PROCEDURE sp_rk_blocker_blockee
AS set transaction isolation level read uncommitted
set nocount on declare @blocker_spid int, @blockee_spid int, @blockee_blocker int
declare @blockee_waitime int IF EXISTS
(SELECT * FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses))
BEGIN
DECLARE blocker_cursor CURSOR FOR
SELECT spid FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked=0 DECLARE blockee_cursor CURSOR FOR
SELECT spid, blocked, waittime FROM master.dbo.sysprocesses WHERE blocked > 0
OPEN blocker_cursor
FETCH NEXT FROM blocker_cursor INTO @blocker_spid
WHILE (@@FETCH_STATUS =0 )
BEGIN
Select ‘Blocker: ‘,@blocker_spid
exec sp_executesql N’dbcc inputbuffer(@Param)’,N’@Param int’, @blocker_spid
–SELECT Blocked = spid FROM master.dbo.sysprocesses WHERE blocked = @blocker_spid
OPEN blockee_cursor
FETCH NEXT FROM blockee_cursor INTO @blockee_spid, @blockee_blocker, @blockee_waitime WHILE (@@fetch_status = 0)
BEGIN
–SELECT Blocked = spid FROM master.dbo.sysprocesses WHERE blocked = @blocker_spid
–Select ‘EE: ‘, @blockee_blocker, ‘ Er: ‘,@blocker_spid
IF (@[email protected]_spid)
BEGIN
SELECT ‘Blockee: Waittime:’, @blockee_spid, @blockee_waitime
exec sp_executesql N’dbcc inputbuffer(@Param)’,N’@Param int’, @blockee_spid
END
FETCH NEXT FROM blockee_cursor INTO @blockee_spid, @blockee_blocker, @blockee_waitime
END
CLOSE blockee_cursor
FETCH NEXT FROM blocker_cursor INTO @blocker_spid
END
CLOSE blocker_cursor
DEALLOCATE blockee_cursor
DEALLOCATE blocker_cursor –go
END
ELSE
SELECT ‘No blocking processes found!’
go –exec sp_rk_blocker_blockee
–SELECT * FROM master.dbo.sysprocesses where blocked = 0
— dbcc inputbuffer (129)
— exec sp_leadblocker
— sp_lock 90
— May be you can run it in some kind of loop. Also I use diagnostic manager from netiq 24×7 this tool give me a lot information, also blocking and is WS tool only, no server instalation are need.
HTH Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

This isn’t a bug, it’s just that server is overloaded or application is poorly designed.
The Current Activity screen of EM is very susceptible to locking in the databases, especially master. You can use sp_who2 instead of EM but it will suffer too from this condition, though not quite as much. LOCK_TIMEOUT is a per-process setting, so you need to change the code for the process. -1 is the default value which means "wait forever". The stored procedure that implements Current Activity includes a "SET LOCK_TIMEOUT 8000" if memory serves. See whether this KBAhttp://support.microsoft.com/defaul…port/kb/articles/q286/2/86.asp&NoWebContent=1 is related to the issue. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Yeah, I’d looked at that article but it definitely doesn’t apply. The server isn’t overloaded and there is no "poorly designed app" as such – this is a reporting server, from which Analysis Services cubes are built overnight. The only apps using it are QA and EM for ad-hoc queries. Thanks for your suggestions, Satya and Luis. I will try to pin down the blocking, and maybe look and increasing the LOCK_TIMEOUT if necessary. Tom Pullen
DBA, Oxfam GB

BTw any issues on h/w side such as Memory, processor etc.# Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

No. She had IMPLICIT_TRANSACTIONS set ON for some reason. This was leaving uncommitted transactions open (e.g. from doing alter table.. add column), she must’ve been blocking herself on sysobjects. Thanks for all your help. Tom Pullen
DBA, Oxfam GB

Tried by setting this option to OFF or close the connection after the transaction. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>