+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: Exit single-user mode (/Thread-Exit-single-user-mode)
Pages:
1
2
Exit single-user mode - supersurprisevnzpaqy - 07-31-2023
Currently, my database is in Single User mode. When I try to expand me database, I get an error:
> The database 'my_db' is not accessible.(ObjectExplorer)
Also, when I try to delete the database, I get the error:
> Changes to the state or options of database 'my_db' cannot be made at
> this time. The database is in single-user mode, and a user is
> currently connected to it.
How do I exit out of single-user mode? I don't have any user using this database.
When I try to browse my site with IIS, the error I get is:
> An unhandled exception was generated during the execution of the
> current web request. Information regarding the origin and location of
> the exception can be identified using the exception stack trace below.
I feel as though the single-user mode is causing this.
RE: Exit single-user mode - mastodontidae817738 - 07-31-2023
To switch out of Single User mode, try:
`ALTER DATABASE [my_db] SET MULTI_USER`
To switch back to Single User mode, you can use:
`ALTER DATABASE [my_db] SET SINGLE_USER`
RE: Exit single-user mode - acrylyl663530 - 07-31-2023
Not sure if this helps anyone, but I had the same issue and could not find the process that was holding me up. I closed SSMS and stopped all the services hitting the local instance. Then once I went back in and ran the exec sp_who2, it showed me the culprit. I killed the process and was able to get the Multi_User to work, then restart the services. We had IIS hitting it every few minutes/seconds looking for certain packages.
RE: Exit single-user mode - michelkirxgcjxj - 07-31-2023
I tried this is working
ALTER DATABASE dbName SET MULTI_USER WITH ROLLBACK IMMEDIATE
RE: Exit single-user mode - antiferroelectric622033 - 07-31-2023
Even I come across same problem, not able to find active connections to my_db to kill it but still shows same error. I end up disconnecting all possible SSMS connections for any database on the Server, create a new connection from SSMS and change it to Multi user.
-- Actual Code to change my_db to multi user mode
USE MASTER;
GO
ALTER DATABASE [my_db] SET MULTI_USER
Note: This seems to be a possible bug in SQL Server 2005!
RE: Exit single-user mode - dundalk851 - 07-31-2023
I ran across the same issue this morning. It turned out to be a simple issue. I had a query window open that was set to the single user database in the object explorer. The sp_who2 stored procedure did not show then connection. Once I closed it, I was able to set it to
RE: Exit single-user mode - unwaterlike449678 - 07-31-2023
The following worked for me:
USE [master]
SET DEADLOCK_PRIORITY HIGH
exec sp_dboption '[StuckDB]', 'single user', 'FALSE';
ALTER DATABASE [StuckDB] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [StuckDB] SET MULTI_USER WITH ROLLBACK IMMEDIATE
RE: Exit single-user mode - maxzmmecy - 07-31-2023
We just experienced this in SQL 2012.
A replication process jumped in when we killed the original session that set it to single user. But sp_who2 did not show that new process attached to the DB.
Closing SSMS and re-opening then allowed us to see this process on the database and then we could kill it and switch to multi_user mode immediately and that worked.
I can't work out the logic behind this, but it does appear to be a bug in SSMS and is still manifesting itself in SQL 2012.
RE: Exit single-user mode - alysianbvoye - 07-31-2023
I had the same problem, and the session_id to kill was found using this query:
Select request_session_id From sys.dm_tran_locks Where resource_database_id=DB_ID('BI_DB_Rep');
RE: Exit single-user mode - semanticists414287 - 07-31-2023
First, find and `KILL` all the processes that have been currently running.
Then, run the following `T-SQL` to set the database in `MULTI_USER` mode.
USE master
GO
DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '
FROM master..sysprocesses
WHERE spid > 50 AND dbid = DB_ID('<Your_DB_Name>')
EXEC(@kill);
GO
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [<Your_DB_Name>] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [<Your_DB_Name>] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO