August 18, 2003 at 10:51 am
A client application is hanging intermittently, causing locks on database. When I identify the blocking session and issue a kill command, it doesn't work. Any suggestions ?
August 18, 2003 at 3:16 pm
We've noticed similar "Zombie SPIDs" recently as well. And this was after applying MSSQL 2K SP3.
Most of our senarios involves a SPID executing a remote stored proc on a remote linked server via a trusted connection. On occasion, we also notice the Zombie SPID has "TM REQUEST" displayed in the COMMAND column of sp_who2 output. We've been told that TM REQUEST may be related to (or a byproduct from) having NT Fibers turned on. After we disabled NT Fibers on the server hosting the Zombie SPID and had rebooted that night, the problem never returned.
Oddly enough, we found more info on "TM REQUEST" from Google than any MS BOL, KB or TechNet search.
Go figure.
- DBA@AMC
quote:
A client application is hanging intermittently, causing locks on database. When I identify the blocking session and issue a kill command, it doesn't work. Any suggestions ?
- Bit
August 18, 2003 at 8:43 pm
Hi there
I cant really add to this, but similar posts in a range of groups have related similar issues to:
a) fibre mode
b) parallel operations being performed..
here is a good summary of the TM Request op from google groups:
"TM stands for Transaction Manager. The current command being set to TM
Request means that the thread is currently servicing a DTC (Distributed Transaction Coordinator) request, either to enlist in a DTC transaction, or defect from it, or commit it, or so on."
(S. Ashwin, microsoft.public.sqlserver.server post).
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
August 19, 2003 at 3:23 am
Sometimes, a rollback needs to be done.
This may take some time.
Simply re-run sp_who2 after the KILL statement and check the status of the SPID.
August 19, 2003 at 12:03 pm
Sometimes you can monitor the progress of a rollback with (assuming that the spid is 53):
kill 53
kill 53 with statusonly --if time remaining displays as 0, then the process may not die, and you may have to bounce SQL Server
Jon
August 19, 2003 at 12:03 pm
Sometimes you can monitor the progress of a rollback with (assuming that the spid is 53):
kill 53
kill 53 with statusonly --if time remaining displays as 0, then the process may not die, and you may have to bounce SQL Server
Jon
September 25, 2003 at 4:30 am
Update on this issue - the problem occurred again and, while we were investigating, a scheduled backup of the transaction log ran.
This resolved the issue [removed all locks] without any action from us. Anybody able to offer any insight on why this happened ?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply