Kill SPIDs through automated job(while restore)

Question

Post reply

Kill SPIDs through automated job(while restore)

RGP

SSCarpal Tunnel

Points: 4381
More actions
July 17, 2008 at 11:27 am

#194071

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

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

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

Robert Sterbal SSChampion Points: 11050 More actions · Answer 1

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: 148809 More actions · Answer 2

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: 11050 More actions · Answer 3

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: 148809 More actions · Answer 4

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: 101095 More actions · Answer 5

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

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 More actions · Answer 7

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.