?
Solved

MS SQL 7.0 Diff Backup

Posted on 2005-04-05
5
Medium Priority
?
193 Views
Last Modified: 2008-03-17
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



0
Comment
Question by:novice12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13709261
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.

0
 

Author Comment

by:novice12
ID: 13709585
Here is the job step:

BACKUP DATABSE [DatabseName] TO [BackupDeviceName] with INIT, NOUNLOAD, DIFFERENTIAL, NAME = N'JOBNAME', NOSKIP, NOFORMAT
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 2000 total points
ID: 13709653
As I mentioned though :

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.
0
 

Author Comment

by:novice12
ID: 13709708
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?.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 13725228
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.
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question