SQL Server 2000 DBCC being blocked by "Process ID -2"

Question

SQL Server 2000 DBCC being blocked by "Process ID -2"

Gurvinderpal S Narula

SSC Enthusiast

Points: 149
More actions
April 18, 2007 at 8:35 am

#175866

If this is not the right forum for this question, please excuse me since I'm a newbie around here. !
Well here's a situation we've been running into recently.
We have a DBCC job that executes every night that 'updates statistics' on all the databases. Off late the job seems to hang every time and it get's blocked by a process id "-2" (yes with the '-' before the 2). We've never been able to find this blocking process. It's not on the process info tab and neither is this -2 process on the "Locks / Process ID" of Current Activities Monitor. So we really don't have a clue on what this process does or how it gets invoked. Eventually the DBCC job itself needs to be killed (as it then locks up other processes and causes a cacading affect). The issue always happens on a particular database (we've not tracked the specific object, but we should be able to collect that information fairly easily). And this DB is accessed though a J2EE application using SQL Server 2000 JDBC driver. The application uses XA Transactions and correspondingly uses SQL Servers 2000 JDBC XA Transaction stored procedures.
Does anyone know what the process id of -2 stands for ? Does any one have details on how it gets invoked and if there are any issues with SQL Server or the JDBC driver that could cause this 'zombie' process to get fired ? Any insight at this point will be extermely helpful. We are currently clueless on what's causing our DBCC jobs to fail and how to update the statistics properly.
Thanks in advance,
Gurvinder

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

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

A process ID of -2 indicates an orphaned connection. In order to kill it you need to look up it's unit of work (UOW) id from the syslocks table and when execute KILL UOW.

For more info see http://msdn2.microsoft.com/en-us/library/aa933230(SQL.80).aspx

Markus

[font="Verdana"]Markus Bohse[/font]

Gurvinderpal S Narula SSC Enthusiast Points: 149 More actions · Answer 2

Markus,

Thanks for the info. That is very very helpful.

I do have one followup question though. Is there a way to detect and automatically 'timeout' the orphaned DTC transactions ? I mean instead of manually going in there and identifying the UOM, is there a way to specify a timeout in SQL where SQL detects that the connection has timedout and should roll back the UOW ?

Gurvinder

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

Gurvinder,

I'm not aware of any standard solutin for this problem. You might be able to scripts something which checks on a regular bases for proces id -2 and when looks up the UOW, but as I said I don't have such a script.

I think it also depends how often these orphan processes appear.

Markus

[font="Verdana"]Markus Bohse[/font]

Gurvinderpal S Narula SSC Enthusiast Points: 149 More actions · Answer 4

Thanks again Markus, It should be easy enough to script. We can run it just b4 the DBCC jobs are executed. That should do it.