Auto-Switching to Single-User Mode? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Auto-Switching to Single-User Mode?

I’m not sure why this is happening…but it’s annoying… We just migrated from SQL 7.0 to 2000. I have a maintenance job that runs
during the weekend — nothing fancy, just the usual that SQL creates when you
use the wizard. One table, "fusetalk" (the bulletin board software), keeps
going into single-user mode for some reason. All I’m doing is a full backup
and a transaction log dump, which happens to all the other data tables, too.
But this is the only one that spontaneously goes into single-user mode. Any help would be much appreciated. Oh, and is there a way to get a database
out of single-user mode without rebooting the service?
In SQL 7 there was an issue: FIX: Database Remains in Single User Mode with the Database Maintenance Plan Option "Repair Any Minor Problems"
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q259551 Should have been fixed when you upgraded though. Just in case check if you have "Repair Any Minor Problems" check on the maintenance plan. To get out from single user mode just Right click -> Properties and change the user mode on the database.
Hi Argyle, Thanks for the info. I wonder if it’s possible that the bug still exists because my DB used to be 7.0? I restored a backup from 7.0 to the 2000 install, and all seems fine, but maybe problems like this linger on. I can’t change the user mode on the DB with a right-click because it only allows for 1 user, and since the DB is in constant use during business hours, I can never get in. I guess maybe I need to shut down the app for a second and do what needs to be done?
BTW, I do have "Repair Any Minor Problems" checked on the maintenance plan. Isn’t this what is recommended? If this isn’t necessary, I can uncheck this and it’ll probably eliminate the problem in the future…
You can edit that db maintenance plan to uncheck that option.
And the workaround mentioned in thishttp://support.microsoft.com/kb/276234/EN-US/ KBA. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>