Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142

    Unanswered: Process long gone - Lock still exists, causing trouble.

    I have a SQL2005 SP2 server with a strange issue. There is a process that holds page locks on a DB, with a SPID of '-2'. There's (obviously) no corresponding process to kill, and no open transactions on that DB. DBCC comes up perfectly clean on that DB, as well as master. No memory issues that I am aware of.

    Is there a way to kill these locks?

    Any and all help is appreciated.

    -D.

    (Cross posted to SQLTeam. - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114306)
    Last edited by ReadySetStop; 11-12-08 at 08:28.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    A spid of -2 usually indicates a MS-DTC problem. These normally only show up on clustered servers, although I've heard of them showing up in other configurations too.

    -PatP

  3. #3
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    Pat,

    Someone over on SQLTeam also suggested that. Following the web links brought me to http://support.microsoft.com/kb/954669. I've already asked the web developers to see if they have corresponding errors on the IIS side.

    Also, a co-worker found out that you can KILL by UOW, as well as by SPID. Here's a script generator for this issue, in case anyone else runs into it.

    Code:
    /* Generate a script to kill orphaned DTC Transactions. */
    
    SELECT 'KILL ' + CAST(request_owner_guid AS CHAR(36))
    FROM sys.dm_tran_locks
    WHERE request_session_id = -2
     AND request_owner_guid <> '00000000-0000-0000-0000-000000000000'
    Thanks for the help.

    -D.
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •