Hunting and killing orphaned processes

I shall await the comments from emotional teenagers with interest, I have just spent the last 30 minutes wondering why I could not restore a SQL server database which had had some problems. It turned out the culprit was pesky orphaned processes, so in true Elma Fudd style I went hunting them and brutally slaughtered any I found. (If it wasn't for the fact that Warner Bros would probably sue my arse off I would have put a jaunty picture of Elma in at this point but you will just have to imagine over to the right there is Elma complete with deer stalker and double barrelled shotgun)

So you want to do something on SQL and its telling you you cannot because the database is in use, first ring round the office and make doubly sure no-one is on it. Next use query profiler to examine current connections but don't expect to see every process in the list, it will allow you to see active connections though and you might find someone on there who didn't realise it :o)

Take a deep breath and open query analyser, the command to see a complete list of processes is sp_who so type

exec sp_who

Then if you can see your pesky orphaned connection you use its spid to kill it, so type:

kill spid
exec sp_who

and they will magically dissappear from the list :o)

Everyone is happy and you can get on with the day, maybe after a congratulatory cuppa and biscuit.