Friday, December 5, 2014

How to get out of SINGLE_USER mode


USE [master] 
GO

     SELECT 'kill ' + cast(spid as varchar)
       FROM master.sys.sysprocesses
      WHERE dbid = DB_ID('dbname')
GO

     SELECT 'kill ' + cast(request_session_id as varchar)
       FROM   sys.dm_tran_locks
      WHERE resource_database_id = DB_ID('dbname') 
GO

     SELECT 'kill ' + cast(session_id as varchar)
       FROM sys.dm_exec_requests 
      WHERE database_id = DB_ID('dbname')
GO

--KILL any processes connected to dbname



SET DEADLOCK_PRIORITY HIGH
GO
exec sp_dboption '[dbname] ', 'single user', 'FALSE';
GO
ALTER DATABASE [dbname] SET MULTI_USER WITH NO_WAIT
GO
ALTER DATABASE [dbname] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [dbname] SET READ_WRITE WITH NO_WAIT
GO

No comments:

Post a Comment