Maintenance plans Integrity repairs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Maintenance plans Integrity repairs

Author

Topic

Robwhittaker
Yak Posting Veteran

85 Posts

Posted - 2004-06-11 : 07:09:14

I've scheduled a maintenace plan using the wizard (I know all the forums say I should write them myself but I don't get the time).

The plan does an index data integrity check. If it finds an error it tries to correct the inconsistancey.

The problem is that the plan always fails at this point saying that Repair statement cannot be processed as the database is not in single user mode.

Can somebody advise me whats the best thing to do.

Should I try to get the database to switch to single user mode so that it can do this? Which strikes me as being very risky even at 1am on Sunday.

Should I drop this step, and get it to flag the "corrupt" databases, so that I can switch the databases with the users agreement to single user mode?

Can I just ignore this issue, it hasn't caused a problem yet?

thanks

Rob

MuadDBA

628 Posts

Posted - 2004-06-11 : 07:48:59

I tend to be leary of anytime a software program will try to "automaticaly" fix errors. I'd prefer to see what the errors are and decide on the corrective action myself, especially when the corrective action the softwre will take isn't documented very well.

If you get errors during the integrity check, you should schedule a time to attempt a repair and then do the check again. This way you control when the server attempts the repair, you can do a backup immediately beforehand, etc.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-11 : 11:08:21

The plan does an index data integrity check. If it finds an error it tries to correct the inconsistancey.
--Tara, myself, and several others have scripts on here you can just copy and schedule. ???

Can somebody advise me whats the best thing to do.
--You need to schedule downtime like crazyjoe said, make a backup, then repair the error.
--Run DBCC CHECKDB though first though at night and tell us what errors it's encountering.

Can I just ignore this issue, it hasn't caused a problem yet?
--No. It could be causing big problems. When it blows up in your face, you'll be help responsible for not repairing it earlier.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-11 : 12:42:26

You are probably running DBCC CHECKDB with the repair options which you shouldn't be doing. The database must be in single user mode for this to occur anyway. You only use the repair options when corruption occurs. Since this is through a maintenance plan, uncheck the option to fix minor problems (I think it's labeled similar to that at least).

Here's a script so that you don't need the maintenance plan wizard:



CREATE PROC isp_DBCC_CHECKDB
AS

SET NOCOUNT ON

DECLARE @dbid INT
DECLARE @DBName SYSNAME
DECLARE @SQL NVARCHAR(4000)

SET @dbid = 0

WHILE @dbid < (SELECT MAX(dbid) FROM master.dbo.sysdatabases)
BEGIN

	SELECT TOP 1 @dbid = dbid, @DBName = name
	FROM master.dbo.sysdatabases
	WHERE dbid > @dbid
	ORDER BY dbid

	SET @SQL = 'DBCC CHECKDB(' + @DBName + ')'

	EXEC sp_executesql @statement = @SQL

END

RETURN 0


GO

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources