Maintenance plans failing when not in single-user mode

[divinyl]

Hi all

It is a known bug that maintenance plans including db integrity checks will fail if the database is not in single user mode - IF the "Attempt to repair any minor problems" check box is checked.

Is it really necessary for SQL Server to be fixing these problems? What is considered a 'minor' problem? And will these problems be logged so that I can fix them manually if necessary?

Any recommendations appreciated.

Thanks
Div

 

[mrdenny]

If you don't fix the minor problems they will grow into major problems which require data loss to correct.

Minor problems are usually minor data corruption, or an data page that isn't where it's supose to be.

If you don't try to fix them automatically (which I recommend not doing) you'll need to check your maintenance logs and when there is a problem use DBCC to identify the problem, and the repair level, then correct the problem.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[divinyl]

Thanks Denny - you are brilliant at replying quickly and accurately!

OK - looks like we will have to dedicate some downtime per month or so that we can schedule these maintenance plans because they simply fail if not in single user mode...

Thanks
Div

 

[mrdenny]

If they are failing, you need to manually run DBCC CHECKDB and see what's going on. If they are failing it's usually do to a database problem that needs to be repaired.

I'd recommend manually repairing the problems, instead of leaving it up to the SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[divinyl]

Hi Denny

The issue with the mainetanance plans failing is a known bug in SQL 2000. It occurs if you are running db intergrity checks with the include index option - but only if you select the "Attempt to repair minor problems". The issue occurs because the db is not in single user mode. Here is the KB article:

http://support.microsoft.com/?id=290622

So looks like we'll have to schedule monthly downtime where we can run a script that kills all connections to our databases and then runs the integrity checks. Does that sound about right? Do you think monthly checks is not frequent enough? Will post a separate thread on this too.

Thanks

 

[mrdenny]

See my other thread. It's by design, so by defination it's not a bug.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com