Single user mode question | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Single user mode question

Our maintance plan that backs up the trans logs, etc. has been failing with the error "Must be in single user mode". Changing it to single user mode sounds easy but what does that really mean? The database runs our website and if only one person can get to the website at a time that would really be bad. Am I missing something?
This happens due to the ‘Check database integrity’ option in your maintenance plan. This option runs ‘DBCC CHECKDB’ in the background and the database needs to be in single-user mode while it attempts to repair any errors. Try with ‘Exclude indexes’ option and see if this error goes away. If it does, check the maintenance plan log after it finishes and run DBCC CHECKDB manually if you find any errors in this log.
As referred be aware that SQL Server can make database repairs only when the database is in single-user mode. The maintenance plan tries to set the database to single-user mode before running the repair command, but if any users are in the database, the statement fails and the scheduled job reports an error and doesn’t execute. Once the database is in single-user mode, no other users can connect to the database while the DBCC command with REPAIR_FAST is running, so users might not be able to use the database for a couple of minutes for each gigabyte of data in your database. Also, no other scheduled jobs can run until sqlmaint sets the database back to multiuser mode. So although BOL recommends including the repair option, you need to evaluate your database’s usage patterns and schedule maintenance for times you’re sure the database isn’t in use or run the repair option manually if the DBCC command reports an error. 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.
]]>