novice12
asked on
MS SQL 7.0 Diff Backup
I have setup a SQL job to do a full backup of the dB every week. When I check the backup file size it's 3.6 GB. I also have a differential backup running every day (It writes to a backup device). I found that the diff backup file size is 3.5 GB!.
I was expecting the diff file size to be much less than the full backup file. The dB does not grow that much every week!. It's supposed to grow only ~ 8 MB every week, I know that as a fact.
How can the diff backup file size be so close from the full backup file size? Something is not right!
Thanks
I was expecting the diff file size to be much less than the full backup file. The dB does not grow that much every week!. It's supposed to grow only ~ 8 MB every week, I know that as a fact.
How can the diff backup file size be so close from the full backup file size? Something is not right!
Thanks
The differential backup size depends on the volume of data that has changed since the last full backup. I would have as a guess that the vast majority of your data is bein modified during the week.
ASKER
Here is the job step:
BACKUP DATABSE [DatabseName] TO [BackupDeviceName] with INIT, NOUNLOAD, DIFFERENTIAL, NAME = N'JOBNAME', NOSKIP, NOFORMAT
BACKUP DATABSE [DatabseName] TO [BackupDeviceName] with INIT, NOUNLOAD, DIFFERENTIAL, NAME = N'JOBNAME', NOSKIP, NOFORMAT
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the reply.
In the same Maintenance Plan that does the full backup, I found that there are other scheduled jobs. It does dB optimization and integrity check every day.
I guess this explains why the diff backup is so large. The optimization job is obviously altering almost the entire dB.
Another question I have: Is it a common practice to do optimization and integrity check that often?.
In the same Maintenance Plan that does the full backup, I found that there are other scheduled jobs. It does dB optimization and integrity check every day.
I guess this explains why the diff backup is so large. The optimization job is obviously altering almost the entire dB.
Another question I have: Is it a common practice to do optimization and integrity check that often?.
Its difficult to say how frequently you should do optimisation. It all really depends on just how "dynamic" your data is. If it is pretty static (ie: mainly read only) then daily is deffinately too much. on the otherhand a database where the distrubution of data is changing rapidly needs frequent optimisation.
I think if i were you i would start off with say ding it once a week. if you find the database slows considerably (which i doubt it will) then increase the frequency, if there is no performance loss decrease the frequency. Eventually over time you will strike a nice balance.
I think if i were you i would start off with say ding it once a week. if you find the database slows considerably (which i doubt it will) then increase the frequency, if there is no performance loss decrease the frequency. Eventually over time you will strike a nice balance.