Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Database stuck in single user mode and can't kill System SPID

Posted on 2011-09-14
5
Medium Priority
?
1,410 Views
Last Modified: 2012-05-12
Hi,

I have a database on SQL server 2008 that's stuck in single user mode. I put it in that mode to do a restore. But the restore failed because it could not obtain exclusive access. When I go in to kill the SPID, I see that it's a system process spid < 20. There are multiple system spid's that are blocking each other and won't let me access the database. I tried to take the database offline, detach it, delete it. All of those failed. Is there any other way for me to gain access back into the database?

Thank you!
0
Comment
Question by:LV_DBA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 13

Assisted Solution

by:LIONKING
LIONKING earned 500 total points
ID: 36539748
Have you tried stopping the sql service and then starting it again?
0
 
LVL 25

Assisted Solution

by:TempDBA
TempDBA earned 500 total points
ID: 36541074
What the system spid says? Try running checkpoint and  running the following command let see what it has to say:-

ALTER DATABASE <dbname> SET MULTI_USER
0
 
LVL 10

Assisted Solution

by:Ramesh Babu Vavilla
Ramesh Babu Vavilla earned 500 total points
ID: 36541226
      alter database <dbname> set multi_user with rollback immediate
----------------------or------------
       alter database <dbname> set multi_user with no_wait
0
 
LVL 5

Accepted Solution

by:
VENKAT KOKULLA earned 500 total points
ID: 36547572
Try this command:

1) ALTER DATABASE MyDatabaseName SET MULTI_USER WITH ROLLBACK IMMEDIATE

2) If not, try the ADMIN:ServerName and it should open an emergency admin session

Then you can go into the SQL Server to ALTER DATABASE [name] SET MULTI_USER

3) If both of above points won't workout then restart the SQL Services and then run the command mentioned in the first point will surely resolve the issue.

0
 

Author Comment

by:LV_DBA
ID: 37160314
Thanks Everyone! I am sorry for not responding earlier. I had to restart SQL services and then run the ALTER DATABASE [name] SET MULTI_USER. I had to restart and run the command a couple of times before it would take.
 
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question