May 24, 2018 at 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
May 24, 2018 at 8:54 am
WoundedParrot - Thursday, May 24, 2018 8:43 AMI 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
May 24, 2018 at 9:00 am
NorthernSoul - Thursday, May 24, 2018 8:54 AMWoundedParrot - Thursday, May 24, 2018 8:43 AMI 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?
ThanksIt 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.
May 24, 2018 at 9:13 am
Crisis over, bizarrely restarting my SSMS has released the lock. Bizarre, since it was doing next to nothing!
You live and learn!
May 24, 2018 at 10:15 am
It could be that you have OE or some other dialog with a lock. I've seen that. Sometimes disconnecting in SSMS (OE + windows) helps.
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