May 2, 2006 at 1:14 pm
I have created a DB Maintenance Plan which performs Optimizations, Integrity and Backup Tasks. For the Integrity Tasks, I have checked the "Attempt to repair any minor problems" Options. When the Maintenance Plan run at 3 A.M. it returns an error stating, "ALTER DATABASE statement failed ... Repair statement not processed. Database needs to be in single user mode." I guess this tells me someone else is using the Database during this time. Is there any way to automatically force the database into single user mode within the DB Maintenance Plan? Please provide examples. We have SQL Server 2000 Standard Edition (sp3) running on Windows 2003 Enterprise Edition.
Thanks in advance, Kevin
May 2, 2006 at 3:29 pm
I am not sure whether there is a way to do that within the Maintenance Plan, but you can do it using the following SQL statement.
alter database <databaseName> set single_user with rollback immediate
The "With rollback immediate" will rollback all open transactions, so you might want to choose other options (from BOL)
We usually set up our Maintenance Plans without checking the "Attempt to repair minor problems" checkbox, and if the job fails, then perform the repair by running the above statement manually, since this will cause an outage.
May 3, 2006 at 6:53 am
It sound like you are trying to do minor repair on system tables with the maintenance plan
It does require a single user mode to do system tables
regards
wayne
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply