Binaryworld - Stored procedure to take a database in Single User Mode/Multi User Mod ... [ SQL Server -> Server Operations ]

Sometimes it is must to take a specific database in single user mode. The most known issue is database/table or index corruption. To fix this problem you have to run DBCC CHECKDB with REPAIR_REBUILT or REPAIR_FAST option and to do so you have to take your database in single user mode.

Here is a very useful stored procedure which will take care of killing all connection except your connection from which you running all SQL commands. I give all credt for this SP to Nicholas Carey.

You have to specify fully qualified name if you run this SP from a different database. For example if you create this sp in master database and you want to bring Northwind in single user mode then you can run the following commands in Query Analyzer.

Click here to copy the following block

USE Northwind
go
exec master.dbo.sp_db_set_single_user 'Northwind'
-- Or --
-- exec master.dbo.sp_db_set_single_user

Stored Procedure to set DB in Single User Mode

Click here to copy the following block

CREATE procedure dbo.sp_db_set_single_user
@dbname   sysname = NULL ,
@TimeOutMins int   = 5
as

declare
  @currdb   sysname    ,
  @sql    varchar(8000) ,
  @tmFrom   datetime   ,
  @rc     int      ,
  @kill_spids int

--
-- if @dbname is not supplied, use the current database
-- and make sure it is in canonical form.
--
-- This little piece of SQL is necessary due to the fact
-- that db_name() will return different results depending
-- on whether the stored procedure is executed with a
-- qualified name (eg, 'exec master.dbo.sp_set_multi_user')
-- or an unqualified name ('exec sp_set_multi_user'.)
--
-- Just another feature of MS SQL Server.
--
select @dbname = case coalesce(@dbname,'')
           when '' then ( select db_name(dbid)
                  from master.dbo.sysprocesses
                  where spid = @@spid
                 )
           else     ( select db_name( db_id(@dbname) )
                 )
          end

--
-- check database name. Since we can't put MASTER into
-- single user mode, we're not even going to try.
--
if ( @dbname = 'master' OR @dbname = 'msdb' )
begin
  RAISERROR( '''MASTER'' and ''MSDB'' may not be set to single user mode.' , 18, 1 )
  return 1
end

--
-- cursor to enumerate all the non-system spids in the database.
-- we only want to look at spids with a non-nil hostprocess in order
-- to bypass SQL Server's internal spids (with no database context)
-- and SQL Agent's spids in the MSDB database. We also exclude our
-- own spid, since you're not allowed to commit suicide with the
-- kill command (although I'm sure the underlying DBCC command has
-- no problems with that :0 )
--
declare
  KillCmds cursor for select KillCmd = 'kill ' + convert(varchar,spid)
            from master.dbo.sysprocesses
            where dbid    = db_id( @dbname )
             and hostprocess <> ''
             and spid    <> @@spid

--
-- establish base time from which we compute the
-- timeout.
--
select @tmFrom = current_timestamp

------------------------------------------------------
-- attempt to set to single user mode
-- while not in single user mode
-- AND we haven't (yet) timed out...
-- * kill all the spids in the database
-- * set the database to single user
------------------------------------------------------

--
-- (attempt to) switch the database to single user mode
--
print 'setting single user mode...'
exec sp_dboption @dbname, 'single user', 'true'

while (   0 = databaseproperty( @dbname , 'IsSingleUser' )
     and datediff(second,@tmFrom,current_timestamp) < (60*@TimeOutMins)
)
begin

  print case databaseproperty( @dbname , 'IsSingleUser' )
      when 0 then '...failed, retrying...'
      else    '...set'
     end

  --
  -- kill all the spids in the database
  --
  print 'killing spids in database ' + @dbname + '...'
  open KillCmds
  fetch KillCmds into @sql
  while ( @@fetch_status = 0 )
  begin
   print @sql
   exec ( @sql )
   fetch KillCmds into @sql
  end
  close   KillCmds
  print '...killed'

  --
  -- (attempt to) switch the database to single user mode
  --
  print 'setting single user mode...'
  exec sp_dboption @dbname, 'single user', 'true'

end

--
-- deallocate the cursor we declared above
--
deallocate KillCmds

--
-- if we got the database into single user mode,
-- then we're hunky-dory
-- otherwise, we're SOL
--
select @rc = case databaseproperty( @dbname , 'IsSingleUser' )
         when 1 then 0
         else    1
        end

print '...set single user ' + case @rc when 0 then 'ok' else 'FAILED' end

FINI:
--
-- return to caller with return code set
--
return @rc

GO

And to bring a specified database back to multi-user mode you can use the following stored procedure.

can run the following commands in Query Analyzer to set Northwind to Multi User Mode.

Click here to copy the following block

USE Northwind
go
exec master.dbo.sp_db_set_multi_user 'Northwind'
-- Or --
-- exec master.dbo.sp_db_set_multi_user

Stored Procedure to set DB in Multi User Mode

Click here to copy the following block

create procedure dbo.sp_db_set_multi_user
@dbname sysname = NULL
as

-- if @dbname is not supplied, use the current database
-- and make sure it is in canonical form.
--
-- This little piece of SQL is necessary due to the fact
-- that db_name() will return different results depending
-- on whether the stored procedure is executed with a
-- qualified name (eg, 'exec master.dbo.sp_set_multi_user')
-- or an unqualified name ('exec sp_set_multi_user'.)
--
-- Just another feature of MS SQL Server.
--
select @dbname = case coalesce(@dbname,'')
           when '' then ( select db_name(dbid)
                  from master.dbo.sysprocesses
                  where spid = @@spid
                 )
           else     ( select db_name( db_id(@dbname) )
                 )
          end

--
-- check database name. Since we can't put MASTER into
-- single user mode, there's no point in taking *out*
-- of single user mode, is there?
--
if ( @dbname <> 'master' )
begin

  print 'setting database ' + @dbname + ' multi-user...'

  exec sp_dboption @dbname, 'single user', 'false'

  print '...set'

end

return 0
GO