Database Integrity and backup job failing.

Question

Post reply

Database Integrity and backup job failing.

abhishekkulkarni1284

SSC Rookie

Points: 32
More actions
November 29, 2012 at 5:33 am

#266809

Hi, We have a Database Integrity Job and Backup Job that are scheduled in a gap of 6 hrs, with the DI job kicking off first. However conidering the size of the DB- the DI job goes on running for about 9 to 10 hrs and ultimately causing both the jobs to fail. Any recomendations to make these job successful. I will be pressing to make the DI job run only on weekend, but still any suggestions will be helpful.

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

Well, the first thing that anyone trying to help needs to know is what's causing the jobs to fail. What's the error?

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

sanket kokane Hall of Fame Points: 3445 More actions · Answer 2

abhishekkulkarni1284 (11/29/2012)

Hi, We have a Database Integrity Job and Backup Job that are scheduled in a gap of 6 hrs, with the DI job kicking off first. However conidering the size of the DB- the DI job goes on running for about 9 to 10 hrs and ultimately causing both the jobs to fail. Any recomendations to make these job successful. I will be pressing to make the DI job run only on weekend, but still any suggestions will be helpful.

can you post the error please .

I think dbcc checkdb will not block the backup process.

OR you can schedule the Database Integrity job after backup job (add as a next jobstep )

but if you find it eating resource , then it will be better to it on weekend

-----------------------------------------------------------------------------
संकेत कोकणे

abhishekkulkarni1284 SSC Rookie Points: 32 More actions · Answer 3

Sorry for not posting the error. Below it is:

"Failed:(100) An exception occurred while executing a Transact-SQL statement or batch."

This is the error for both the jobs. And I have tested the backup job running successfully if the DBCC check job is not in execution. Sanket, your option of running the D.I check job after the backup job sounds good, I will try, but have one concern that it might bleed into business hours affecting the performance of applications. Not sure though, how much it will affect, i can try though.

Thanks guys!

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

That's not a useful error. Log the job output to a file and check what's in the file.

You can run integrity checks after taking a backup, but personally I prefer to know that my DB's corrupt before backing it up, not after.

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

sanket kokane Hall of Fame Points: 3445 More actions · Answer 5

abhishekkulkarni1284 (11/29/2012)

but have one concern that it might bleed into business hours affecting the performance of applications. Not sure though, how much it will affect, i can try though.

Thanks guys!

Then it will be better to schedule Database Integrity job on Weekends when there will be less active users.

Also agree with Gail, if you DB is corrupt,backups taken after it usually not useful.

-----------------------------------------------------------------------------
संकेत कोकणे

sanket kokane Hall of Fame Points: 3445 More actions · Answer 6

sanket kokane

Hall of Fame

Points: 3445

November 30, 2012 at 12:41 am

#1564183

But please do post the exact error of your jobs .

use the method Gail provided in last post.

-----------------------------------------------------------------------------
संकेत कोकणे

Perry Whittle SSC Guru Points: 233792 More actions · Answer 7

abhishekkulkarni1284 (11/29/2012)

Hi, We have a Database Integrity Job and Backup Job that are scheduled in a gap of 6 hrs, with the DI job kicking off first. However conidering the size of the DB- the DI job goes on running for about 9 to 10 hrs and ultimately causing both the jobs to fail. Any recomendations to make these job successful. I will be pressing to make the DI job run only on weekend, but still any suggestions will be helpful.

You can perform a less intensive check that could run through the week and a full consistency check at the weekeknds or whenever you have period of low activity. The code for the less intensive integrity check is

DBCC CHECKDB (yourdb) WITH PHYSICAL_ONLY, ALL_ERRORMSGS

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉