DB is stuck in Single-user mode- SQL server 2012
Question
Monday, June 15, 2015 11:35 AM | 1 vote
Hi
I have a DB that is stuck in single-user mode.
I believe I tried everything, please help!
I get this error when trying to access to db properties:
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Database 'DBNAME' is already open and can only have one user at a time. (Microsoft SQL Server, Error: 924)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.5058&EvtSrc=MSSQLServer&EvtID=924&LinkId=20476
This is all I tried so far:
1. Restarting the server
2. sp_who and then kill process ID that is using this DB
3. stopping web server(sharepoint) that`s using this db
4. alter database "DB-NAME" set multi_user
5. ALTER DATABASE "DB-NAME" SET MULTI_USER WITH NO_WAIT
6. ALTER DATABASE "DB-NAME" SET MULTI_USER WITH ROLLBACK IMMEDIATE
What else can I do??
HELP!!
Thank you all!!
All replies (4)
Monday, June 15, 2015 11:51 AM âś…Answered
Thank you all, I fixed it.
This is how:
1. sp_who
2. killed proID
3. stop tp web application polls with user from #1
4. restarted SQL server
5. ALTER DATABASE "DB-NAME" SET MULTI_USER WITH ROLLBACK IMMEDIATE
6. start to application pools from #3
Thanks again!
:)
Monday, June 15, 2015 11:41 AM | 1 vote
Hello - Can you tell us what happens when you execute this statement:
ALTER DATABASE DB-NAME SET MULTI_USER
Do you get any error ? Can you check if there are any other connections to this database by querying:
SELECT * FROM sys.sysprocesses WHERE dbid = YourDBId
Good Luck!
Please Mark This As Answer if it solved your issue.
Please Vote This As Helpful if it helps to solve your issue
Monday, June 15, 2015 11:44 AM | 2 votes
close all open connections, make sure you close all open SSMS windows and connections.
Open master database and run below
use mastergoalter database db_name set multi_usergo
Does this works, again make sure nothing is accessing DB
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Monday, June 15, 2015 11:55 AM
use DAC (Dedicated Admin Connection). Make sure you have enabled it first
In SSMS type in** admin:<instanceName>** for Server Name
after connecting to master ALTER DATABASE DB-NAME SET MULTI_USER
Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue