Error/Fix: Msg 924. Get Database Out of Single User Mode
While doing DBA activities, sometimes I have to take the database into Single_User mode before I can perform the tasks required.
But quite often than I want to admit, I lose a single_user connection to some application/user. This creates big problem for me as it risks my activity and downtime. Below is the error message we receive in case some other session/user has the single_user connection to the database –
|
1 2 3 4 |
Msg 924, Level 14, State 1, Line 1 Database 'DBA' is already open and can only have one user at a time. Msg 924, Level 14, State 1, Line 3 Database 'DBA' is already open and can only have one user at a time. |
Resolution:-
After this incident happened to me a couple of times, I created the below tsql query to get out of any such situation when this risky situation comes up.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
use master; go set nocount on; set deadlock_priority 10; declare @db_name varchar(100); set @db_name = 'DBA'; declare @sqlAlterDatabase nvarchar(2000); declare @sqlKillSession nvarchar(2000); set @sqlAlterDatabase = 'ALTER DATABASE ['+@db_name+'] SET MULTI_USER WITH rollback immediate;'; -- Keep looping while database is in SingleUser mode while exists (select 1/0 from sys.databases where name = @db_name and user_access_desc = 'SINGLE_USER') begin declare cur_ssns cursor local forward_only for select distinct 'kill '+convert(varchar,tl.request_session_id)+' ' from sys.dm_tran_locks tl where tl.resource_database_id = db_id(@db_name) and tl.request_session_id <> @@SPID open cur_ssns fetch next from cur_ssns into @sqlKillSession while @@FETCH_STATUS = 0 begin begin try exec (@sqlKillSession); exec (@sqlAlterDatabase); end try begin catch print error_message() end catch fetch next from cur_ssns into @sqlKillSession end close cur_ssns deallocate cur_ssns end go |
In the above tsql code, we create a loop, and kill one session at a time while at the same time, we try to bring the database back to multi_user mode. This way, we keep looping and killing sessions having the single_user connection of the database until the database gets out of single_user mode.
I hope this will be helpful to DBA out there facing this issue. If you like the article, do Like & Share.
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.