Home Forums SQL Server 2008 SQL Server 2008 Administration Kill All Spid(s) according to Database Name RE: Kill All Spid(s) according to Database Name

  • June 14, 2012 at 9:17 am

    #1501088

    SQLKnowItAll (6/14/2012)

    Lowell, in what cases do you use this? For the operation of restoring a database, I would prefer to use the

    ALTER DATABASE DatabseName

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO because of the possibility of some process beginning after I have killed them all. So under what situations do you use your SP?

    actually, for me, it's two things;

    convenience and typing: easier to call a short-named proc. typing the full command would annoy me.

    i have an sp_kill2 which calls the same ALTER DATABASE you mentioned for the situations when i need to kick off and restore.

    but the one i posted is very handy for when i run into some developer that is in the middle of a transaction while stepping thru the code, and then went to lunch...getting the hostnames back for the spids I killed tells me who was the blocker who went to lunch...just more information for me , i guess.

    at my shop, it happens too much, since people get called into meetings and stuff a lot.

    literally yesterday i had to do it just to know who was blocking my dev databbase and open it back up to use again...

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!