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.