DBCC CheckDB fails, then succeeds

Question

Post reply

DBCC CheckDB fails, then succeeds

jralston88

SSCommitted

Points: 1931
More actions
July 23, 2012 at 7:50 am

#276568

I configured DBCC CheckDB for several databases via SSMS. For several databases (3) they initially failed, only to find a few days later they worked (I also copied the database .bak's to another server, then restored, where they also worked). Can anyone tell me why they would fail, then succeed. BTW - this is SQL Server 2008 SP1 Enterprise Edition on a VM.
Any comments / URLs would be appreciated - thank you.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Without seeing messages, no way to say.

http://technet.microsoft.com/en-us/magazine/2009.04.sqlqa.aspx

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jralston88 SSCommitted Points: 1931 More actions · Answer 2

Thank you for the URL. Here's the history of the failed job. I've xxx out some of the content:

++++++

Date7/17/2012 11:30:00 PM

LogJob History (xxx_DBCC_CheckDB.Subplan_1)

Step ID1

ServerxxxCLUSTER

Job Namexxx_DBCC_CheckDB.Subplan_1

Step NameSubplan_1

Duration00:00:05

Sql Severity0

Sql Message ID0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted0

Message

Executed as user: xxx\SQLJobSvc. Microsoft (R) SQL Server Execute Package Utility

Started: 11:30:00 PM Progress: 2012-07-17 23:30:02.54 Source: {9F443F0B-D37B-4840-BC53-0A2AAAE47968}

Executing query "DECLARE @GUID UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress

DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:30:00 PM Finished: 11:30:03 PM Elapsed: 3.042 seconds.

The package execution failed. The step failed.

++++++

That's it...

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

No way to tell from that, there's no useful info in there.

If checkDB failed, there will be details in the SQL error log

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jralston88 SSCommitted Points: 1931 More actions · Answer 4

Gail,

I tracked down the error log only to find that it's almost one GB in size. I'm not inclined to open it. I'm going to do my due diligence and find out why this is so large and reconfigure. This is my top priority. As far as the CheckDB failures, they did resolve themselves so I'm going to have to monitor.

The URL you provided did help and frankly kicked open some doors to other concepts I need to know.

Thank you for your help.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

To keep the SQL Server Error Log file from getting too large (1 GB??), I schedule a job to run every week on Sunday at 00:00:00 to start a new version of the log.

execute [sys].[sp_cycle_errorlog]