Sql-server – SQL Server 2000 spid -2

fragmentationsql-server-2000

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.

Best Answer

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;
GO
SELECT req_transactionUOW -- UOW GUID
  FROM syslockinfo 
  WHERE req_spid = -2;

You can then use:

KILL 'GUID_from_above';
Related Question