For a SQL 2016 instance, a DB creation date of 2003 looks odd. This T-SQL command will kill the session ID 59: KILL 59; GO – Vaccano Feb 2 '12 at 21:30 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. The connection that originally put the database into single user mode is … So if you are reading this blog by getting this as a search result, you might be in a tough situation. I'm using SQL Server 2005. :-/ – Joshua F. Rountree Sep 6 '16 at 13:04 The database is in single-user mode, and a user is currently connected to … Finally I had the drop the database and re-create it from scratch. There's nothing wrong with the connection, and nothing wrong with the login. I logged on as sa, but I could not bring the database to muti-user mode. In the right-pane, right-click the instance of SQL Server, and then click Properties. In SQL Server Configuration Manager, in the left pane, select SQL Server Services. 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 … Hi, Suppose you had a database stuck in single user mode that is in a busy OLTP environment. You would only need to put the SQL Server instances in single user mode if you were restoring the master database. On the Startup Parameters tab, in the Existing parameters box, select -m and then click Remove. 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 First of all this is not normal to start SQL Server in single user mode. Given that: 1. 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 . We can also start SQL Server single user mode using the Command Prompt. Starting SQL Server single user mode using Command Prompt. For user databases, you have to make sure there are no active connections to the database you're restoring. These next few steps change SQL Server back to multi-user mode. 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. 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). 2. Still cannot make this work. 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. Even after running EVERYTHING it still says it cannot make this work because the database is running in single-user mode. 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. 1. Sometimes, it is not possible to change to emergency mode to single user mode because there are several active connections. 3. In some situations, like restoring system database or during disaster recovery you may need to start SQL in single user mode. I logged in through DAC. We were able to get it out of single user mode (finally) but it kept going right back in.