Only user processes can be killed!

Question

Post reply

Only user processes can be killed!

RollTide

Mr or Mrs. 500

Points: 540
More actions
September 8, 2005 at 10:54 am

#88965

All,
Yesterday, a user kicked off a script on a test box to wipe out a large table and reload it. He used a delete from instead of a truncate command and let the process run all night(doh!). When I came in today, the database was locked up. We tried to kill his process, but were unsuccessful. We restarted SQL Server Service to hopefully put the process into rollback, but the process remains as follows(from sp_who2):
SPID Status BlkBy DBName Command
----- ---------- ----- --------- ----------------
10 BACKGROUND 10 warehouse AWAITING COMMAND
If I try to kill, I get the following:
'Only user processes can be killed.'
When restarting, the process remains. Any thoughts!?! I am a novice with SQL server and could really use some advice!

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jo Pattyn SSC-Dedicated Points: 32341 More actions · Answer 1

Jo Pattyn

SSC-Dedicated

Points: 32341

September 8, 2005 at 11:09 am

#588470

and SPID 10 is?

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 2

Anything less than 50 is generally a system SPID, you can't kill em and that's that

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 3

Looks to me like he (SQL Server) thinks the process you're running is a system-owned process. Are you sure the rollback hasn't completed?

RollTide Mr or Mrs. 500 Points: 540 More actions · Answer 4

If it is rolling back, wouldn't the Command be marked as ROLLBACK instead of AWAITIN COMMAND? That would make sense though if the process is rolling back, but why would the rollback be associated with a system process instead of the user process that originally ran?

noeld SSC Guru Points: 96590 More actions · Answer 5

Not sure what spid 10 is but deinitely if you "restarted" the service is the service who is going to run a recovery process on the user DB not a user spid!

* Noel

RollTide Mr or Mrs. 500 Points: 540 More actions · Answer 6

Thanks for the responses!

That must be our issue then....is there anyway to speed up such a rollback process? Or am I at the mercy of the DB.....Watch and wait, so to speak? The process ran for a good 10 hours so could take 30 or hours to rollback, right?

noeld SSC Guru Points: 96590 More actions · Answer 7

Watch and wait, so to speak? <-- YES

That's the price for "A.C.I.D."

* Noel

RollTide Mr or Mrs. 500 Points: 540 More actions · Answer 8

Any future readers may be interested.

Couldn't wait on the rollback so we stopped sql server, blew away the database, and restored from backup.

That watchin' and waitin' can be painful!