Zombie Transaction in process -2 Blocking other Transactions

[A. Tillman]

We are having a really big problem with a zombie process/transaction
that is blocking other processes. When looking at Lock/ProcessID
under Current Activity I see a bunch of processes that are blocked by
process 94 and process 94 is blocked by process -2. I assume -2 is a
zombie that has an open transaction. I cannot find this process to
kill and it seems that this transaction is surviving database
restarts. I know which table is locked up and when I run a select *
from this table it never returns. Does anyone have any ideas as to
how to kill is transaction.

Any help is appreciated.
A. Tillman

[Erland Sommarskog]

A. Tillman (at*********@yahoo.com) writes:

We are having a really big problem with a zombie process/transaction
that is blocking other processes. When looking at Lock/ProcessID
under Current Activity I see a bunch of processes that are blocked by
process 94 and process 94 is blocked by process -2. I assume -2 is a
zombie that has an open transaction. I cannot find this process to
kill and it seems that this transaction is surviving database
restarts. I know which table is locked up and when I run a select *
from this table it never returns. Does anyone have any ideas as to
how to kill is transaction.

-2? This sounds like locks. The process that held the locks experienced
a fatal problem, and SQL Server were not able to release it locks. I'm
afraid your only option is to restart SQL Server to resolve the condition.

If this condition appears often, you should consider opening a case
with Microsoft support. It is likely that this condition is due to a bug
in SQL Server.

Which version of SQL Server are you running? I have seen negative spids,
but only on SQL 6.5 as I can recall.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[A. Tillman]

We are using SQL Server 2000.

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

A. Tillman (at*********@yahoo.com) writes:

We are having a really big problem with a zombie process/transaction
that is blocking other processes. When looking at Lock/ProcessID
under Current Activity I see a bunch of processes that are blocked by
process 94 and process 94 is blocked by process -2. I assume -2 is a
zombie that has an open transaction. I cannot find this process to
kill and it seems that this transaction is surviving database
restarts. I know which table is locked up and when I run a select *
from this table it never returns. Does anyone have any ideas as to
how to kill is transaction.

-2? This sounds like locks. The process that held the locks experienced
a fatal problem, and SQL Server were not able to release it locks. I'm
afraid your only option is to restart SQL Server to resolve the condition.

If this condition appears often, you should consider opening a case
with Microsoft support. It is likely that this condition is due to a bug
in SQL Server.

Which version of SQL Server are you running? I have seen negative spids,
but only on SQL 6.5 as I can recall.

[A. Tillman]

SQL Server 2000 Service Pack 3. This survived a database restart.

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

A. Tillman (at*********@yahoo.com) writes:

We are having a really big problem with a zombie process/transaction
that is blocking other processes. When looking at Lock/ProcessID
under Current Activity I see a bunch of processes that are blocked by
process 94 and process 94 is blocked by process -2. I assume -2 is a
zombie that has an open transaction. I cannot find this process to
kill and it seems that this transaction is surviving database
restarts. I know which table is locked up and when I run a select *
from this table it never returns. Does anyone have any ideas as to
how to kill is transaction.

-2? This sounds like locks. The process that held the locks experienced
a fatal problem, and SQL Server were not able to release it locks. I'm
afraid your only option is to restart SQL Server to resolve the condition.

If this condition appears often, you should consider opening a case
with Microsoft support. It is likely that this condition is due to a bug
in SQL Server.

Which version of SQL Server are you running? I have seen negative spids,
but only on SQL 6.5 as I can recall.

[Greg D. Moore \(Strider\)]

"A. Tillman" <at*********@yahoo.com> wrote in message
news:c5**************************@posting.google.c om...

SQL Server 2000 Service Pack 3. This survived a database restart.
Only time I've seen that is a massive rollback.

(which of course if you restart the DB before it's done, it simply has to be
done again.)


Erland Sommarskog <es****@sommarskog.se> wrote in message

news:<Xn**********************@127.0.0.1>...

A. Tillman (at*********@yahoo.com) writes:

We are having a really big problem with a zombie process/transaction
that is blocking other processes. When looking at Lock/ProcessID
under Current Activity I see a bunch of processes that are blocked by
process 94 and process 94 is blocked by process -2. I assume -2 is a
zombie that has an open transaction. I cannot find this process to
kill and it seems that this transaction is surviving database
restarts. I know which table is locked up and when I run a select *
from this table it never returns. Does anyone have any ideas as to
how to kill is transaction.

-2? This sounds like locks. The process that held the locks experienced
a fatal problem, and SQL Server were not able to release it locks. I'm
afraid your only option is to restart SQL Server to resolve the condition.
If this condition appears often, you should consider opening a case
with Microsoft support. It is likely that this condition is due to a bug
in SQL Server.

Which version of SQL Server are you running? I have seen negative spids,
but only on SQL 6.5 as I can recall.