SQL Server – Bring Database from Single User Mode to MultiUser Mode

0
13

 

Its often troubles DBAs where once a DB went to Single User Mode it cant revert back to Multi User mode, the error looks some what like this

Error Getting : Changes to the state or options of database cannot be made at this time. The database is in single-user mode, and a user is currently connected to it

Use this Proc to Revert ( replace dbname accordingly )

DECLARE @ID INT
DECLARE @MSG CHAR(8), @DataName sysname
select @DataName='<dbname>’
1 = 1
BEGIN
Set RowCount 1

SELECT
@id = spid
FROM
Master..Sysprocesses P,
Master..Sysdatabases D
WHERE
D.Name=@DataName
AND
D.dbid = P.dbid

IF @@rowcount = 0
break

SELECT @msg = ‘KILL ‘ + convert(char(8) ,@ID)
Print @msg
EXEC( @msg)
–break
END

GO

ALTER DATABASE <dbname>
I_USER

GO

Source : http://www.sqlservercentral.com/Forums/Topic343275-146-1.aspx

LEAVE A REPLY

Please enter your comment!
Please enter your name here