Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2002-11-07 : 09:31:37
|
| Yesterday afternoon a co-worker issued a KILL on against a spid who's inputbuffer revealed a "bulk insert"...the spid was killed because no one claimed ownership. It's host was our production box (so it was either run from the console or as part of a job) and the user was a sql auth user, not a domain user.The spid was still in rollback when everyone left, despite several kill attempts.The spid was still in rollback when I came in the AM. It had blocked several vital jobs including a backup job. I issued a kill and the spid vanished, releasing its locks.So the question: What might cause a killed bulk insert to hang in rollback status for so long? I don't think it was really rolling back that long. SQL 7 SP 3, nothing in logs except the kills.Jay White{0} |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-11-07 : 09:49:59
|
| Suspect the kill wouldn't do anything until the bcp exe terminates unless it was batched.Sometimes a rollback can take an extremely long time for the amount of data it has to rooll back.Look at the physical io in sysprocesses to see if it is doing anything.A reboot of the server can reduce recovery time from several hours to a couple of minutes (decide how dangerous you think this would be).Bit surprised your kill the following day worked but maybe it was the first one done after bcp.exe finished the import.To answer your question - I have no idea.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
hung SPID in ROLLBACK status