November 29, 2012 at 5:33 am
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.
November 29, 2012 at 5:53 am
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
November 29, 2012 at 5:54 am
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
-----------------------------------------------------------------------------
संकेत कोकणे
November 29, 2012 at 7:07 am
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!
November 29, 2012 at 7:15 am
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
November 30, 2012 at 12:40 am
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.
-----------------------------------------------------------------------------
संकेत कोकणे
November 30, 2012 at 12:41 am
But please do post the exact error of your jobs .
use the method Gail provided in last post.
-----------------------------------------------------------------------------
संकेत कोकणे
November 30, 2012 at 5:58 am
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"
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy