Kill SPIDs through automated job(while restore)

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

  • 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

  • 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

  • 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

  • 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

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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

    😎

  • 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 7 (of 7 total)

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