Replication & Maintenance Plans

[NickCorlett]

I have an database on an instance of SQL Server 2000 which is configured to run transactional replication, this is currently configured to run 24x7. I also have a scheduled Database Maintenance Plan which performs a database integrity check and attempts to repair minor problems.

The integrity check is failing as it is unable to put the database in single-user mode, I presume that this is because the replication is running constantly and therefore there are always connections? Am I correct in assuming this?

As I see it I have two options either remove the repair minor problems from the integrity check or schedule a gap in the replication during which the check can be performed.

Has anyone else come across this? Are my assumptions correct?

 

[mrdenny]

Yes you are correct. It can't put the database into single user mode because of the replication. I would disable the auto repair in the maintenaince job, and do repairs by hand. Have the SQL Server email you the reports nightly. If there is a problem, stop the replication manually and repair the problem. That way you know what is happening before it happins, instead of comming in the next day to learn that the SQL Server is attempting to repair it self, and the business is down while the unscheduled repair is going on.


Since it is trying to do a repair you probably have a problem. Run DBCC CheckDB to find and fix the problem.

Denny

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