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.
declare @spid table
id int identity(1, 1),
insert into @spid
where dbID = 5
declare @sql varchar(100), @counter int = 1
while @counter <= (select max(id) from @spid)
select @sql = 'kill ' + convert(varchar, spid)
where id = @counter
select @counter += 1
alter database myDatabase set multi_user with rollback immediate