Kill SPIDs through automated job(while restore)

  • RGP

    SSCarpal Tunnel

    Points: 4381

    Comments posted to this topic are about the item Kill SPIDs through automated job(while restore)

  • Robert Sterbal

    SSChampion

    Points: 10988

    This is our attempt at it:

    declare @SPId varchar(1000)

    Print '[SQLTRIX] Killing active connections'

    Print ''

    SELECT @SPId = COALESCE(@SPId,'')+'KILL '+CAST(SPID AS VARCHAR)+'; '

    FROM Master..SysProcesses WHERE DB_NAME(DBId) = '?DB_NAME?'--@DBNAME

    and spid >= 50 and SPId <> @@spid

    PRINT @SPId

    EXEC(@SPId)

    Print ''

    GO

    I find we can't start the restore fast enough about 1/4 of the time.

    412-977-3526 call/text

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    Or this:ALTER DATABASE SQLTRIX SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    You can use RESTRICTED USER instead of SINGLE USER if that's more appropriate (in other words if none of the processes that are likely to connect to the database will do so as db_owner or higher).

    John

  • Robert Sterbal

    SSChampion

    Points: 10988

    John Mitchell-245523 (12/2/2016)


    Or this:ALTER DATABASE SQLTRIX SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    You can use RESTRICTED USER instead of SINGLE USER if that's more appropriate (in other words if none of the processes that are likely to connect to the database will do so as db_owner or higher).

    John

    Do you have any issues of another process getting in there or does the system make the process the SINGLE USER?

    412-977-3526 call/text

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    I'm not sure exactly how it works, but I don't think I've ever had a restore fail on database in use after setting it to single user. I suppose it's possible, which is why I said restricted user may be more appropriate in some cases.

    John

  • ScottPletcher

    SSC Guru

    Points: 98479

    Personally I've had much lesser delays/stalls using this command:

    ALTER DATABASE SQLTRIX SET OFFLINE WITH ROLLBACK_IMMEDIATE;

    And you can get into devilish issues with "SINGLE_USER". You need to make sure you are in the db, sometimes the single_user takes SQL a while to "disassociate". Also a real pain if some other task happens to jump in ahead of you in getting to the db and it becomes the single user, and you're totally locked out of the db.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    ScottPletcher (12/2/2016)


    Personally I've had much lesser delays/stalls using this command:

    ALTER DATABASE SQLTRIX SET OFFLINE WITH ROLLBACK_IMMEDIATE;

    And you can get into devilish issues with "SINGLE_USER". You need to make sure you are in the db, sometimes the single_user takes SQL a while to "disassociate". Also a real pain if some other task happens to jump in ahead of you in getting to the db and it becomes the single user, and you're totally locked out of the db.

    +1

    😎

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    John Mitchell-245523 (12/2/2016)


    Or this:ALTER DATABASE SQLTRIX SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    You can use RESTRICTED USER instead of SINGLE USER if that's more appropriate (in other words if none of the processes that are likely to connect to the database will do so as db_owner or higher).

    John

    Thanks for the tip.

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

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