Error The database is in Single User Mode and a User is Currently Connected to it
With the release of SCCM 2012 SP1, We can have multiple SUPs under one primary server. One scenario that I want to test was Remote SUP with shared DB. Prior to SUP role installation, we need to install WSUS 3.0 SP2 on the remote SUP server along with WSUS patches (2720211 and 2734608).WSUS is installed successfully with shared DB (DB is located at my primary server). However, when I tried to install the patch it was giving me the following error.I’ve checked MSI3f93c.log file and then MWusCa.log. The second log was more specific.
Error 1722. There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor. Action ExInstallSqlQuery, location: C:\Windows\Installer\MSIC2B8.tmp, command: -S ACNCMPRI -Q “
Database ‘SUSDB’ is already open and can only have one user at a time.
Changes to the state or options of database ‘SUSDB’ cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.Msg 5069, Level 16, State 1, Server ACNCMPRI, Line 1
ALTER DATABASE statement failed.
Now how to resolve this issue? Logged into SQL studio at my ConfigMgr 2012 SP1 primary server and checked SUSDB. Yes, that was in single user mode. Now, how to change single user mode to Multi User mode? Again search helped to find out a solution. Following are the SQL queries to resolve the issue.
1. Need to find out that user and session id.
SQL query/statement -
select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
where d.name = ‘SUSDB’
2. Note down the “spid” of the user from the above query result and kill that session.
SQL Statement – “kill 52”
Where 52 was session ID.
3. Change the SUSDB to multi user mode.
SQL Statement -
ALTER DATABASE SUSDB