September 23, 2010 at 6:57 am
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
LCK_M_SCH_M means schema modification.....
September 23, 2010 at 7:18 am
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
September 23, 2010 at 8:27 am
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
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply