SQLServerCentral.com / Sarbanes-Oxley / SQL Server 7,2000 / A blocking problem / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 22:39:48 GMT20RE: A blocking problemhttp://www.sqlservercentral.com/Forums/Topic244355-161-1.aspxThere are two issues around blocking in SP4. One's a real bug, the other isn't.The first one is a bug in the lock manager that manifests during DBCC INDEXDEFRAG. The lock manager erroneously holds onto NULL mode locks when INDEXDEFRAG drops its page locks, eventually resulting in escalation to a table lock. This will block other queries. There is a hotfix available. Details can be found at http://support.microsoft.com/default.aspx/kb/907250The other issue is an enhancement to the blocking info displayed. It may look like a SPID is blocking itself but this is in fact normal during synchronous IO. More info at http://support.microsoft.com/default.aspx/kb/906344AFAIK there are no issues that would cause the problem you describe - recommend you call PSS to assist you.ThanksFri, 16 Dec 2005 09:58:00 GMTPaul RandalRE: A blocking problemhttp://www.sqlservercentral.com/Forums/Topic244355-161-1.aspxsearch at http://support.microsoft.comFri, 16 Dec 2005 09:05:00 GMTmimorrRE: A blocking problemhttp://www.sqlservercentral.com/Forums/Topic244355-161-1.aspx<P>Thanks</P><P>Do you know the bug #? and where I can obtain the patch</P><P> </P><P>-- Peter Lo</P>Fri, 16 Dec 2005 08:50:00 GMTlopeterRE: A blocking problemhttp://www.sqlservercentral.com/Forums/Topic244355-161-1.aspxThere is an issue (bug) with "SELF BLOCKING" which arose with either SP3 or SP4 of SQL2K. Search here (or sqlteam.com) for info on same. A patch is available. Paul Randal (of Microsoft SQL Dev Team) would have posted the info.Fri, 16 Dec 2005 03:33:00 GMTAndrewMurphyA blocking problemhttp://www.sqlservercentral.com/Forums/Topic244355-161-1.aspx<P>In my production environment, I have a stored procedure which was called by an application several time. This stored procedure was used to return a new sequence number for the 'key' passed as an argument. For example, if appliication needs to add a new customer record, this stored procedure was called and returned a new cust_num for the new record. The 'last_sequence_oid' table has the column keyname and the column storing the last sequence number used for the 'keyname'.</P><P>Recently, we experienced a blocking problem with the 'last_sequence_oid' table. By examing the sysprocesses table, I found that the blocker was the apps calling the stored procedure. The blocker sysprocesses record has shown that the blocked column was 0, waittime was 0, waittype was 0x000, and the status was 'AWATING FOR COMMAND'.</P><P>If I killed the application process, all the login sessions blocked were able to continue.</P><P>We could not reproduce the blocking problem in test environment. And blocking problem occasionally occurred in production environment. SQL Profiler trace did not show errors or exceptions.</P><P>Can anyone share his experience with me to investigate this problem. </P><P> </P><P>Regards,</P><P>-- Peter Lo</P><P> </P><P> </P><P> </P>Wed, 14 Dec 2005 21:38:00 GMTlopeter