I have a SQL Server 2000 machine that has a pretty big database on it - almost a terabyte in size.

There's a particular table that has been a source of some production issues and any attempt to try to fix it seems to cause the same problem....self-blocking (I know it's not a big deal on SQL Server 2000 SP4 but then a blocking spid of -2).

Even something like SELECT count(1) FROM <MyProblemTable> causes this spid(-2) to appear. I went into the table properties and there's about 1.5 million rows which isn't much. I have no problems running the same count statement on much larger sized tables. I did notice the fragmentation is quite high on this table (around 45%) but any attempt to either rebuild or reorganize wreaks havoc on the system. I tried running the reorganize during the weekend for like 8 hours and the job was still running and people were complaining of time-out issues.

Then the other day, I tried using the import/export wizard to copy the table to my local instance and it ran fine for the first 250K rows and I was also monitoring sysprocesses and saw the spid for the ETL process. It was fine for those first 250K rows...then the wizard row count wouldn't increment anymore. Then I check the spid on 2K box and it was blocked by spid(-2) again.

What the hell is wrong with the table? Is there an integrity issue? The only way to run SELECT statements against it without this happening is to use NOLOCK.

  • 2
    You are brave, hosting 1 TB of data in 14-year old software that has long since sailed past its end of support date... – Aaron Bertrand Sep 26 '13 at 16:03
  • @user27810 Are you using MSDTC ? If so, you can kill the negative spid using select req_transactionUOW from master..syslockinfo where req_spid = -2 where UOW = Unit of workID for distributed transactions. and then use the KILL UOW command. See (technet.microsoft.com/en-us/library/ms173730(v=sql.90).aspx) – Kin Shah Sep 26 '13 at 16:10

From the documentation on sp_who:

In SQL Server 2000, all orphaned DTC transactions are assigned the SPID value of '-2'. Orphaned DTC transactions are distributed transactions that are not associated with any SPID. Thus, when an orphaned transaction is blocking another process, this orphaned distributed transaction can be identified by its distinctive '-2' SPID value. For more information, see KILL.

You can find the actual owner of the work using:

USE master;
SELECT req_transactionUOW -- UOW GUID
  FROM syslockinfo 
  WHERE req_spid = -2;

You can then use:

KILL 'GUID_from_above';

SPID -2 is an orphaned distributed transaction.

If you have a Unit of Work (UOW) for the SPID the you can KILL the UOW. You get this from:

SELECT request_owner_guid 
FROM sys.dm_tran_locks 
WHERE request_session_id = -2

If the OUW is all zeros 00000000-0000- etc then you need a different approach to get rid of the SPID -2. I have notes saying:

 sp_configure 'in-doubt xact resolution', 2; -- Aborts the transaction
  • Not sure that DMV is available in SQL Server 2000 but good information, nonetheless. – swasheck Sep 26 '13 at 16:30
  • Ah, yes. Well you can try the DBCC dbrecover('Dbname') instead. – RLF Sep 26 '13 at 18:38

The best explanation is given in the PSS blog How It Works: Orphan DTC Transaction (Session/SPID = -2):

Let me clarify the term Orphaned. A -2 is not Orphaned it means there are NO ENLISTED SESSIONS on the SQL Server but the transaction is active yet. Let me give you can example.

  1. Begin DTC Transaction with DTC Transaction Manager
  2. Connect To SQL and enlist SPID 50 - Transaction imported to SQL Server and communications established with the DTC Manager and session enlisted
  3. T-SQL work done on SPID
  4. Disconnect Session – Transaction still tracked by SQL to hold locks and such but no session enlisted so reporting now shows (-2)

What you have is an application that has done work in a DTC transaction against SQL and not committed or aborted it until a later point in time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.