October 27, 2008 at 3:28 am
Hi Everyone,
I have setup the DB Integrity Check for every SAT in DB Maintenance Plan. When I check the job, it is failed.
I also checked in SQL Server Logs, but I got a following list in Logs-
DBCC CHECKDB(Employee,repair_fast) executed by ...0 errors
DBCC CHECKDB(model,repair_fast) executed by ...0 errors
DBCC CHECKDB(pubs,repair_fast) executed by ...0 errors
DBCC CHECKDB(HeadOffice,repair_fast) executed by ...0 errors
DBCC CHECKDB(Sales,repair_fast) executed by ...0 errors
It looks fine in SQL Server Logs and I don't understand why it is failed. Interesting thing is, I couldn't find the DBCC CHECKDB for master,msdb,tempdb. Job status is failed becasue of those 3 database?
In maintenance plan I choosed all databases option.
Thanks.
October 27, 2008 at 4:07 am
You should never run repair on a regular basis. It should only be run when there is corruption, and there's no other way to fix it. It's a last resort, not the first thing tried.
Go into the maint plan and look for a checkbox called something like 'repair minor errors'. Uncheck it.
You're getting the error because repair requires that the DB be switched to single user mode and it can't do that for some of the databases.
I would suggest you run a checkDB (without any of the repair options) on all of your databases manually, make sure that the job wasn't failing because it tried to repair and couldn't.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 27, 2008 at 4:28 am
Hi Gila,
Okay, I have untick the box for auto repair option. So do you think one of the Master,MSDB,TempDB got a error? It was failed becasue of that?
Shall I check manually DBCC CHECKDB for those 3 databases?
Thanks.
October 27, 2008 at 4:41 am
Leo (10/27/2008)
Hi Gila,Okay, I have untick the box for auto repair option. So do you think one of the Master,MSDB,TempDB got a error? It was failed becasue of that?
Maybe
Shall I check manually DBCC CHECKDB for those 3 databases?
Master and msdb only. Temp doesn't need checking, it's recreated on server start.
Run checkDB with the no_infomsgs option and none of the repair options.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply