question

abhishek120684 avatar image
abhishek120684 asked

Maintenance job failure: DI and Backup(Full)

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.
backupfailing
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image Tim commented ·
What version and edition of SQL Server?
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
How big is the database? Oh, and what's the hardware? or is it virtual?
0 Likes 0 ·

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
By database integrity, I'm assuming you mean a Database Consistency Check (DBCC) of the database? If that's taking 9 hours plus, you have a very large database, or, you have a lot of contention on resources. If the database is really large, you can break up the checks within DBCC to only do a physical check on a regular basis and then do the full check, as you already said, on weekends. To do the physical check, you'll need to modify the script so that it looks more like this: DBCC CHECKDB (mydatabase) WITH PHYSICAL_ONLY That checks all the page linkages in the database to ensure physical storage is correct. It doesn't check all the internal consistencies in the database storage, so you still need to do that. Another option is to do the physical checks online as suggested above and then do the logical checks against a restored copy of your database on another server. But, I'd also monitor the process to see if the DBCC is just being blocked by resource contention.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Whether or not your hardware is under warranty doesn't mean a drive might not fail or sunspots or something might affect a write operation. DBCC is all about paranoia and protection. I would do it as frequently as I could. If you really only are able to get it weekly, so be it, but I would try variations, such as validating storage nightly, in order to try to get as much protection as possible. If you only check weekly, then you need to make sure you have backups in place such that you can restore to a point in time during the week in the case of corruption. That means more than a full backup, log backups, and possibly differentials.
1 Like 1 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
And, actually, since you are dealing with large databases, full backup of anything that takes 9 hours to DBCC is likely to take as long or longer for a full backup. You may want to do a full once a week and then differentials on a daily basis. It'll make your backups faster.
1 Like 1 ·
abhishek120684 avatar image abhishek120684 commented ·
yes, I am talking about the DBCC check task from the maint plan. yes the dbcc is taking more that 9 hrs, coz the DBs are big enough to justify thing long duration that dbcc is taking to complete. The Physical check option sounds tempting. I will try that option. I have one question though- If I have good hardware which is in warranty- Will it make any performance issue if I do a DBCC checkdb consistency check once a week instead of doing it every night. yeah, the sql is 2005 on a physical box. Thanks Grant for the reply.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.