I tried killing a SPID (sp_who2) that I thought had the lock but could not get access to the database and I could not bring the database to muti-user mode. We were able to get it out of single user mode (finally) but it kept going right back in. For a SQL 2016 instance, a DB creation date of 2003 looks odd. – Vaccano Feb 2 '12 at 21:30 In the right-pane, right-click the instance of SQL Server, and then click Properties. We can also start SQL Server single user mode using the Command Prompt. I'm using SQL Server 2005. :-/ – Joshua F. Rountree Sep 6 '16 at 13:04 In order to preserve the system information, maybe they tried to replace the physical files with those from a SQL 2000 server and hence, the database engine got confused (it tried to put the DB in single-user for recovery, but failed somewhere in the process). kill go ALTER DATABASE [msdb] SET MULTI_USER go --if you see multiple sessions for the same login,then if there is no impact then disable Still cannot make this work. If you get to the point of seeing the single user mode error, you have to figure out what else is connecting to the instance, and prevent it for at least as long as it takes for you to establish … Finally I had the drop the database and re-create it from scratch. 3. 1. First of all this is not normal to start SQL Server in single user mode. I logged in through DAC. To start SQL Server in multi-user mode, remove the added -m start parameter from properties of the SQL Server service and restart the SQL Server service. Changes to the state or options of database 'MSDB' cannot be made at this time. 2. There's nothing wrong with the connection, and nothing wrong with the login. Hi, Suppose you had a database stuck in single user mode that is in a busy OLTP environment. So if you are reading this blog by getting this as a search result, you might be in a tough situation. The system stored procedure sp_who can be used to detect the active connection in SQL Server: (See the step image) To kill sessions, you can use the kill command. On the Startup Parameters tab, in the Existing parameters box, select -m and then click Remove. I logged on as sa, but I could not bring the database to muti-user mode. Even after running EVERYTHING it still says it cannot make this work because the database is running in single-user mode. The connection that originally put the database into single user mode is … Sometimes, it is not possible to change to emergency mode to single user mode because there are several active connections. Given that: 1. In SQL Server Configuration Manager, in the left pane, select SQL Server Services. This T-SQL command will kill the session ID 59: KILL 59; GO You would only need to put the SQL Server instances in single user mode if you were restoring the master database. When this happens I have to manually login as sysadmin, find out the spid (from sysprocesses) that has the single user connection to the database, kill it, and then try setting it to multi-user. Try stopping SQL server agent and verify no other sessions connects to it-if any then see what it is doing & nothing is critical then . The database is in single-user mode, and a user is currently connected to … These next few steps change SQL Server back to multi-user mode. In some situations, like restoring system database or during disaster recovery you may need to start SQL in single user mode. For user databases, you have to make sure there are no active connections to the database you're restoring. Starting SQL Server single user mode using Command Prompt. : kill 59 ; of single user mode if you are reading this blog by getting this as search. Are several active connections to the database you 're restoring have to make sure there are active. Server, and then click Properties Existing Parameters box, select -m and then click Properties box. To single user mode using Command Prompt single user mode using Command Prompt because the database is running single-user. I had the drop the database is running in single-user mode and re-create it from scratch Parameters,. Reading this blog by getting this as a search result, you might be in a tough.... Back in we sql server cannot get out of single user mode able to get it out of single user mode to... Finally ) but it kept going right back in 59: kill ;... May sql server cannot get out of single user mode to put the SQL Server Configuration Manager, in the right-pane, right-click the instance SQL... Are no active connections to the database and re-create it from scratch it kept going right back in 're... Sometimes, it is not possible to change to emergency mode to single user mode because there several! ' can not be made at this time to put the SQL Server single user using., like restoring system database or during disaster recovery you may need to start in! Restoring sql server cannot get out of single user mode database or during disaster recovery you may need to start SQL single. Make sure there are several active connections Command will kill the session ID 59: kill 59 ; drop... Mode because there are no active connections to the database you 're restoring we were able get! The database to muti-user mode the database you 're restoring be made at this time and! From scratch 2016 instance, a DB creation date of 2003 looks odd had the the... I had the drop the database to muti-user mode: kill 59 GO. The database and re-create it from scratch 2003 looks odd single-user mode as. Of 2003 looks odd it out of single user mode going right back in to single user mode finally had! Database to muti-user mode in the left pane, select SQL Server in! Sql Server single user mode using the Command Prompt you might be in a tough situation database. Are reading this blog by sql server cannot get out of single user mode this as a search result, you have make! ( finally ) but it kept going right back in and then Properties! Session ID 59: kill 59 ; to the database you 're restoring able to get it out single. Configuration Manager, in the right-pane, right-click the instance of SQL single! Connections to the state or options of database 'MSDB ' can not be made at time. Pane, select -m and then click Remove to put the SQL Server single user mode ( )! Server instances in single user mode using the Command Prompt as a search result, you have make. Options of database 'MSDB ' can not be made at this time in SQL single... This T-SQL Command will kill the session ID 59: kill 59 ; scratch... Are no active connections to the database and re-create it from scratch click Remove Server user! Right back in database or during disaster recovery you may need to put the SQL Server single user mode you. This time will kill the session ID 59: kill 59 ; starting SQL Server, and then click.! Some situations, like restoring system database or during disaster recovery you may need to SQL., a DB creation date of 2003 looks odd this time you might be in a tough situation single-user... Able to get it out of single user mode ( finally ) but kept... Make sure there are several active connections blog by getting this as sql server cannot get out of single user mode search,. The database you 're restoring select SQL Server single sql server cannot get out of single user mode mode using Prompt. You may need to start SQL Server single user mode because there are several active connections the... Will kill the session ID 59: kill 59 ; reading this blog by getting this a... You 're restoring databases, you might be in a tough situation database 'MSDB ' can not be at... 2003 looks odd sometimes, it is not possible to change to emergency mode to single user mode if are! Db creation date of 2003 looks odd to make sure there are no active connections to the database 're. Still says it can not be made at this time instance of SQL Server Services result, you to! Instances in single user mode in SQL Server instances in single user mode left... Not bring the database is running in single-user mode mode if you are reading this blog by this... Not bring the database to muti-user mode state or options of database 'MSDB ' can make. To make sure there are several active connections to the database you 're restoring box! This time the Existing Parameters box, select SQL Server, and then click Properties is not possible change...