none
spid -4 blocking the transactions RRS feed

  • Question

  • Hi All,

    We encountered a situation today where the spid -4 was blocking the transactions. We're not able to kill it as it was not treated as a valid spid by SQL server. We tried to get the UOW using below query but no result was returned.

    select * from master..syslockinfo
    where req_spid = -4

    Since this was not a production impacting server we restarted the SQL Service as we didnt have any other option.
    We've learned that it might be the orphan transctions of MSDTC.

    However, I need to understand what can be done to resolve this issue as I cannot restart SQL Server for this.

    Please help!

    Regards
    Mayur Kashikar

    Wednesday, January 30, 2013 11:52 AM

Answers

  • 2:1:1 is PFS Page and 2:1:3 is SGAM Page. There pages are fixed for these objects and will not change. It is not uncommon to notice contention on these pages. To avoid contention on these pages, you would need to follow tempdb best practices and tune the queries which are heavily using tempdb
    Wednesday, February 6, 2013 12:32 AM

All replies

  • ID of the session that is blocking the request. If this column is NULL, the request is not blocked, or the session information of the blocking session is not available (or cannot be identified).

    -2 = The blocking resource is owned by an orphaned distributed transaction.

    -3 = The blocking resource is owned by a deferred recovery transaction.

    -4 = Session ID of the blocking latch owner could not be determined due to internal latch state transitions.

    Please refer the below link:

    http://msdn.microsoft.com/en-us/library/ms179881.aspx

    http://www.sqlservercentral.com/blogs/sqltechnet/2012/08/06/blocking-in-sql-server-by-spid-2-orphan-distributed-transactions/

    http://www.sqlserverclub.com/articles/how-to-deal-with-negative-spids-in-sql-server.aspx


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, January 30, 2013 12:09 PM
  • Thanks Latheesh!

    However, I need to understand what can be done to resolve this issue as I cannot restart SQL Server for this.

    I couldn't find information on how to resolve this.

    Regards
    Mayur Kashikar

    Wednesday, January 30, 2013 3:31 PM
  • What do you see as the wait resource? you may query sys.sysprocesses and check wait resource column, it may look something like dbid:fileid:pageid

    Wednesday, January 30, 2013 4:00 PM
  • It is important you mention SQLserver and Windows versions
    Wednesday, January 30, 2013 9:24 PM
  • The wait resource was 2:1:3
    Thursday, January 31, 2013 2:58 AM
  • SQL Server 2008 R2 SP1 CU1 and CU2

    Windows Server 2008 R2 Enterprise Edition

    Thursday, January 31, 2013 2:59 AM
  • please follow below article

    http://support.microsoft.com/kb/2154845

    Friday, February 1, 2013 3:40 AM
  • Thanks Keerthi!
    However, this has already been taken care of.
    However, I need to understand what can be done to resolve this (-4 spid) issue as I cannot restart SQL Server for this every time.

    Thanks
    Mayur Kashikar

     

    Monday, February 4, 2013 8:03 AM
  • Spid = 14 is not a separate process. Hence we need to look at the process or object where the real problem resides. You may check the metadata object of the page first. DBCC Page(2,1,3,3). Look at the metadata: objected.

    Find out the objectId using the below:

    Use <db>

    Select object_name(?)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, February 4, 2013 8:13 AM
  • The spid was -4 (Negative 4), not 14.

    However, we couldn't find much info on it using DBCC Page at that time. I, unfortunately, do not have the result of the DBCC Page saved with me. ALso, since it's a tempdb in question, I'm assuming that the metadata will keep changing, not sure though!

    Tuesday, February 5, 2013 9:41 AM
  • 14 was a typo.Sorry for it. Yes you cant get the info now. As you asked in future, you may try the steps given.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, February 5, 2013 10:01 AM
  • Thanks Latheesh!

    I'll check that and update with the information...

    Tuesday, February 5, 2013 3:20 PM
  • 2:1:1 is PFS Page and 2:1:3 is SGAM Page. There pages are fixed for these objects and will not change. It is not uncommon to notice contention on these pages. To avoid contention on these pages, you would need to follow tempdb best practices and tune the queries which are heavily using tempdb
    Wednesday, February 6, 2013 12:32 AM
  • Thanks for the info Keerthi. This was useful. I'll take care of this...
    Wednesday, February 6, 2013 9:08 AM