Kill a SPID
-
I know some people get a kick out of running the KILL command. Heck, I'm sure all of us enjoy stopping a runaway process from an annoying user at times. But not everyone seems to understand exactly how databases work. It's not necessarily a knock on the original poster, after all, most of us had to learn about the ACID properties at some point. Perhaps even after someone dropped a database in our lap without warning.
If you kill a spid, I saw some confusion about data loss in a recent thread. You won't lose data, but you could still have a problem. It's not a technical problem and the storage engine inside SQL Server ensures that the transaction conforms to the ACID properties to ensure data integrity.
The users, however, might or might not realize that their "work" was not done. It depends on the error handling of your application, what message (if any) that is presented to the user, and if they're even still around. I've seen people start processes they expected to run long and then leave their workstation. If there wasn't a message on it later, they might assume the transaction had gone through.
Even if you tell them you're killing the process, they might still think that some amount of work is completed. There are all sorts of users, with all different kinds of expectations out there, so you should be sure that they understand exactly what is happening.
And be sure that they know their "work" is lost. From the point of view of someone doing data entry, if the transaction rolls back and the application can't handle it, they will have to enter information again. So their work is essentially lost.
Those of us in technology sometimes forget the impact of our systems in the real world. Even when things work well or as designed, they may still be a problem for real people that have real tasks to get done.
-
We use a custom kill procedure killx
This procedure first gets DBCC INPUTBUFFER of the spid that you are going to kill. Additionally the sysprocesses (sys.dm_exec_ view for 2005) data related to the spid. Then kills the process. Lasty sends a mail notification to the client saying that all work is lost including date-time and program info.
That procedure works fine until system is under heavy load or deadlocked in which case we use legacy kill command
Cheers,
Zubeyir
-
I know I'm a bit out of topic here but what about the strategy of, sometimes, killing the users? It would surely save us a lot of time....
(I could not resist posting this comment
) -
Let the user lead - get them to kill the front end first (3 finger salute, task manager etc) then once that's done kill any stray connections remaining. Then they are certain their job hasn't completed.
It's very rare to need to kill anyway - sign of very badly written system usually!!
-
Or maybe Stefano is on the right track
-
There the sp dbo.kill_all comes...
-
Back when I was involved more on the DBA side of things, in a period of a few years, I had to use the kill command only a handful of times. It was always for the same thing - legacy FoxPro-based front-end ERP system would hang on the client side and run amok in the SQL Server back-end database, causing weird locks and other havoc. Even after ending the client side, the database would still be in trouble. Once I killed the processes, all was well. But there was never any consistency in what triggered it.
-
One place I worked had sp_who2 as one of their tabs on the application so the users could see who was blocking them.... very badly written application with all the typical no-nos, like keeping a transaction open while a user was typing in values.... Of course very lunch or when people would leave for the day they would leave one open.
The users would then go to the sp_who2 and shut down every machine that showed as having a block.... since the mahcine these where all hard shutdown.
First thing I did was develop a leadblocker procedure for the tab so they only had to shut down the lead
-
Nice command documentation for *NIX Steve
It is a shame MS does not have better pictures
http://msdn2.microsoft.com/en-us/library/aa933230(sql.80).aspx
http://msdn2.microsoft.com/en-us/library/ms173730.aspx
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Hmm - who's Spid and what has he done to get a contract out on him?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
I haven't had to kill a SPID in a long, long time.
Locked records are a problem especially yesterday for some reason, but no SPIDS.
Maybe I should go check on the users and see what they are doing?
Now where did I leave the key to the gate to let them out with...
-
I have only done this a few times when it was vital that the user get out so we could reboot the server. One time, we sent them messages, tried calling them and actually walked over to their area (they weren't around). Since the rest of the company was waiting for our services to be back up and we saw that it was affecting our bandwidth (everyone went on the 'net), I finally had no choice.
Of course, once I did it and we successfully rebooted the server, this user claimed that they lost all their work and had to do overtime. ¡Qué lastima!
-
One of the great benefits of working in a Terminal Server environment:
"Log off now or I'll do it for you".
No hung process or SPIDS to deal with when I can close them out "normally".
Love the shadow feature. Also great when the user calls and I can actually see what they are talking about and point out what they are doing wrong!
No more walking around to individual users especially when some of your users are hundreds or even thousands of miles away.
-
...
I went up there, I said, "Shrink, I wanna kill. I wanna kill! I wanna see blood and gore and guts and veins in my teeth! Eat dead, burnt bodies! I mean: Kill. Kill!"
And I started jumpin' up and down, yellin' "KILL! KILL!" and he started jumpin' up and down with me, and we was both jumpin' up and down, yellin', "KILL! KILL! KILL! KILL!" and the sergeant came over, pinned a medal on me, sent me down the hall, said "You're our boy". Didn't feel too good about it.
...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
...
And you can get anything you want,
at ...
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply