November 20, 2014 at 5:38 am
rrn 62873 (11/20/2014)
Using Ola's solution, I have now set up a solution where the production database gets maintained (DBCC and index rebuild/defrag) every saturday, differential backups every four hours and transaction log backups every fifteen minutes. Backups are written to separate files (which is default in Ola's scripts).
I'm sure it was just left out, but when are you running full backups?
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 20, 2014 at 5:53 am
Indeed, it was accidentally omitted. Full backups are run on saturdays after dbcc and index maintenance.
Will DBCC CHECKDB ever fail a job step? If not, how does one normally get a nudge (using Ola's solution) if there is an issue?
November 20, 2014 at 5:57 am
rrn 62873 (11/20/2014)
Will DBCC CHECKDB ever fail a job step?
Yes.
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 20, 2014 at 6:26 am
Just one question, you are aware that Diff backups aren't incremental? Ie, your last differential backup will back up every page changed since your last *full* backup, not your last Diff.
If you are in a situation where your maintenance jobs/regular processing hit a lot of your data pages, by the end of the week you could find the size of each Diff is a significant proportion of a full backup in size, and this may take up rather a lot of space compared to a nightly full with regular log backups.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
November 20, 2014 at 7:01 am
Andrew, thanks for the insight. I know the differential is all changed data since last full backup. I just put this schedule in place and will monitor the size of the diff backups, if they become too big, I will change the full backup from weekly to daily.
The production DB is currently about 220GB, and the current schedule has taken full backups each day, with four hour incrementals. The incrementals go from 0.5GB (12AM), 1.5GB (4AM), 2.5GB (8AM), 4.5GB (12PM) to 5.2GB (4PM) in size (yesterday, 8PM was full backup time, so no differential scheduled then). It looks like a 1 GB increment (roughly) every four hours (6 GB/day) - maybe a little less, since some of the recent data will be modified several times over the first few days of it's life - production, QA testing, costing, shipping.
Compunding the differentials (42 in a week) will yield about 900 GB worth of backup data plus 220 GB for the full backup. The previous schedule would carry four full sets of backup of 220 GB for the DB and 15 GB for the differentials which is about the same storage, but would take less backup time overall, or so I am predicting. I might also be able to change the differential window to six hours, roughly cutting the storage space of differential logs to two thirds (from 42 compounded to 28 in a week). But this will depend on restore time.
Of course, I may be totally off on any of these counts and will be adjusting the schedule within a week of setting it up 🙂
Edit: These are uncompressed figures, but the logic should be unaffected.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply