Post reply

SPID -2

  • September 23, 2010 at 6:57 am

    #226750

    Had some blocking issue. A user spid was being blocked by spid -2. have never seen an spid numbered in negative.Couldnt find an entry for spid -2 in sysprocesses.Checked sys.dm_os_waiting_tasks and found it there as LCK_M_SCH_M. Can somebody pls shed some info on this.

  • September 23, 2010 at 7:08 am

    #1226295

    LCK_M_SCH_M means schema modification.....

  • September 23, 2010 at 7:18 am

    #1226304

    Spid -2 is an orphaned distributed transaction.

    Query sys.dm_tran_lock for rows with session_id = -2. There's a column called something about 'unit of work'. It's a uniqueidentifier. Find the value and pass that as a parameter to KILL to get rid of the locks.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 23, 2010 at 8:27 am

    #1226362

    Just wanted to confirm the column in tran_lock

    is it request_owner_guid or request_owner_lockspace_id or lock_owner_address for that unique identifier column

  • September 23, 2010 at 10:42 am

    #1226456

    This one: (from Books Online)

    request_owner_guid : uniqueidentifier

    GUID of the specific owner of this request. This value is only used by a distributed transaction where the value corresponds to the MS DTC GUID for that transaction.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply