Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.

Kill All User Processes
Published: Jan 22, 2016
This is a quick and dirty bit of code which I’ve used on occasion when trying to put a database which is in single user mode back into multi user mode but which is accessed so frequently that all you receive is an error message stating you are the deadlock victim.

Again, it’s not a commonly used piece of code, but when I’ve needed it, this has been very useful.

Basically, you have a database in Single User mode and you want to put it back into Multi User mode, but the problem is that you’re getting the following:

The way to avoid this is to simply run a loop through the offending SPIDs, killing them and then immediately accessing the database yourself, therefore taking ownership of the single available connection.

This means that you can now put the database back into multi user mode.

use master

declare @spid table
id int identity(1, 1),
spid int
insert into @spid
select spid
from sysprocesses
where dbID = 5

declare @sql varchar(100), @counter int = 1

while @counter <= (select max(id) from @spid)
@sql = 'kill ' + convert(varchar, spid)
from @spid
where id = @counter

exec (@sql)

select @counter += 1

alter database myDatabase set multi_user with rollback immediate
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.


© Copyright 2020 SQLTraining Ltd.