Single User Mode

I had an interesting experience setting up a copy of a productive system. Everything was working fine up until I wanted to restore the backup of the database. Then I got an error that the database backup was incomplete. Since it was 1am in the morning I thought now is the time to make sure that we had a backup of the productive system. So I start the backup. After about 20 minutes the backup fails and sets the database into a Single User mode. This can be seen in the Microsoft SQL Management studio next to the database. When selecting the properties of this database I got an error message to the effect that the database was opened by another user. Since it was early in the morning I decided to restart the server and while the server was restarting to lookup more details over the single user mode with the hope that in the time it takes to find out more information the reboot would solve the issue. Well, the server did not restart. Being remote this posed some problems. Apparently the night before one of the led’s on harddisk of this server started to shine orange instead of green and that in the event log there where entries like “Drive Array Physical Status Change… has a new status of 3 … 3=failed”

When the system administrators got back in the office the faulty disk was changed and the RAID was restored. But we still had the single user mode problem. From my search on the internet I found 2 commands:

alter database db-name set SINGLE_USER
alter database db-name set MULTI_USER

So I tried the second of these commands and got the message to the effect that the database was in single user mode and a user is already connected to it. I thought this was a little odd since the database server had just restarted. My next attempt was to use a stored procedure within master to kill all connections to this database and detatch it. This looked like

ALTER PROCEDURE [dbo].[sp_Detach_DB_FORCED]
@DATABASE_NAME as VARCHAR(40)
AS
BEGIN

DECLARE @spid int, @str varchar(1000), @DBname varchar (50)
SET @DBname = @DATABASE_NAME

DECLARE curGetSpids
CURSOR FOR
SELECT p.Spid
FROM master..sysprocesses p, master..sysdatabases d (NOLOCK)
where p.dbid = d.dbid
and d.name = @DBname


OPEN curGetSpids
FETCH NEXT FROM curGetSpids INTO @spid
WHILE(@@FETCH_STATUS<>-1)
BEGIN
SELECT @str='KILL '+CONVERT(varchar(3),@spid)
EXEC (@str)
FETCH NEXT FROM curGetSpids INTO @spid
END
CLOSE curGetSpids
DEALLOCATE curGetSpids

EXEC sp_detach_db @DBname
END

Unfortunately this came back with a similar set of messages. After digging further in the internet I found

Sp_who

This lists the spid of all processes that are connected to databases hosted on the server. It turns out that the database was locked by a select statement of a job that had become frozen. To get rid of this connection

Kill spid

Then the alter database statement worked and we all lived happily ever after :)