Set database from SINGLE USER mode to MULTI USER
use master
GO
declare @sql as varchar(20), @spid as int
select @spid = min(spid) from master..sysprocesses where dbid = db_id(‘sample’) and spid != @@spid
while (@spid is not null)
begin
print ‘Killing process ‘ + cast(@spid as varchar) + ‘ …’
set @sql = ‘kill ‘ + cast(@spid as varchar)
exec (@sql)
select
@spid = min(spid)
from
master..sysprocesses
where
dbid = db_id(‘sample’)
and spid != @@spid
end
ALTER DATABASE DB_NAME SET MULTI_USER; GO
———————————————–
select
d.name,
d.dbid,
spid,
login_time,
nt_domain,
nt_username,
loginame
from sysprocesses p
inner join sysdatabases d
on p.dbid = d.dbid
where d.name = ‘sample’
GO
You can add the option to rollback your change immediately.
ALTER DATABASE DB_NAME
SET MULTI_USER
WITH ROLLBACK IMMEDIATE
USE master;
GO
ALTER DATABASE sample
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE sample
SET READ_ONLY;
GO
ALTER DATABASE sample
SET MULTI_USER;
GO
–Make Database Read Only
USE [master]
GO
ALTER DATABASE sample SET READ_ONLY WITH NO_WAIT
GO
–Make Database Read/Write
USE [master]
GO
ALTER DATABASE sample SET READ_WRITE WITH NO_WAIT
GO