Database Stuck in Single User Mode

Question

Post reply

Database Stuck in Single User Mode

WoundedParrot

SSCommitted

Points: 1644
More actions
May 24, 2018 at 8:43 am

#362460

I know this has been asked countless times, and there are loads of solutions on the internet about it but I feel like I've explored every avenue.
I changed a database to SINGLE_USER mode so I could restore over the top of it, and it's claiming now:
Msg 5064, Level 16, State 1, Line 16 Changes to the state or options of database 'dbname' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Now, using sp_who2 tells me there are nothing but system processes accessing the database, they all have spids less than 50 so I can do nothing about them. I've tried killing everything else and running this code:
USE [master] SET DEADLOCK_PRIORITY HIGH --exec sp_dboption '[dbname] ', 'single user', 'FALSE'; ALTER DATABASE dbname SET MULTI_USER WITH NO_WAIT ALTER DATABASE dbname SET MULTI_USER WITH ROLLBACK IMMEDIATE
Many times.
At this point I'm thinking my only solution is to restore my database under a new name and change the app accordingly.
Do I have anything else I can try?
Thanks
May 27, 2018 at 11:30 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

WoundedParrot - Thursday, May 24, 2018 8:43 AM

I know this has been asked countless times, and there are loads of solutions on the internet about it but I feel like I've explored every avenue.
I changed a database to SINGLE_USER mode so I could restore over the top of it, and it's claiming now:
Msg 5064, Level 16, State 1, Line 16 Changes to the state or options of database 'dbname' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Now, using sp_who2 tells me there are nothing but system processes accessing the database, they all have spids less than 50 so I can do nothing about them. I've tried killing everything else and running this code:
USE [master] SET DEADLOCK_PRIORITY HIGH --exec sp_dboption '[dbname] ', 'single user', 'FALSE'; ALTER DATABASE dbname SET MULTI_USER WITH NO_WAIT ALTER DATABASE dbname SET MULTI_USER WITH ROLLBACK IMMEDIATE
Many times.
At this point I'm thinking my only solution is to restore my database under a new name and change the app accordingly.
Do I have anything else I can try?
Thanks

It used to be the case that all SPID's below 50 were system processes but no longer. Were you in Management Studio when you put the database into single user mode and if so is that session still open? That should still hold the single connection to the database.

Thanks

WoundedParrot SSCommitted Points: 1644 More actions · Answer 2

NorthernSoul - Thursday, May 24, 2018 8:54 AM

WoundedParrot - Thursday, May 24, 2018 8:43 AM

I know this has been asked countless times, and there are loads of solutions on the internet about it but I feel like I've explored every avenue.
I changed a database to SINGLE_USER mode so I could restore over the top of it, and it's claiming now:
Msg 5064, Level 16, State 1, Line 16 Changes to the state or options of database 'dbname' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Now, using sp_who2 tells me there are nothing but system processes accessing the database, they all have spids less than 50 so I can do nothing about them. I've tried killing everything else and running this code:
USE [master] SET DEADLOCK_PRIORITY HIGH --exec sp_dboption '[dbname] ', 'single user', 'FALSE'; ALTER DATABASE dbname SET MULTI_USER WITH NO_WAIT ALTER DATABASE dbname SET MULTI_USER WITH ROLLBACK IMMEDIATE
Many times.
At this point I'm thinking my only solution is to restore my database under a new name and change the app accordingly.
Do I have anything else I can try?
Thanks

It used to be the case that all SPID's below 50 were system processes but no longer. Were you in Management Studio when you put the database into single user mode and if so is that session still open? That should still hold the single connection to the database.

Thanks

Thanks for your reply.
Sadly not, my session is not the one holding the single connection.
Of all the sessions I see connected to the database I want to restore over, they're all blocking each other and they're issuing the TASK MANAGER command. When I've tried to kill these sessions I'm told they're system processes that can't be killed.