none
why my differential backups are huge in size?

    Question

  • hi all,
    my live (production) database (very few people use it) of size 5 GB (has got 7 years data) and we scheduled each night 1 differential backup and weekly 1 full backup. "No other backups are scheduled (as per my DBA's decision)" As we know, hardly this system will have 100-200 inserts/updates/deletes per day (not more than that) but if I see size of differentials backed up database size is 1GB per day. Is there a way I can reduce as my boss asking why is this so huge in size when very few operations carried out daily?? As i am a developer I have very little knowledge on dba concepts like backups/restores. Please advise asap on this.
    Monday, November 09, 2009 3:20 PM

All replies

  • My guess is that you are doing index rebuilds or reorganize.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Monday, November 09, 2009 3:52 PM
    Moderator
  • What is your recovery model for your database? If its FULL and you're not doing t-log backups then that could be cause. I'd suggest running in SIMPLE recovery mode.
    every day is a school day
    Monday, November 09, 2009 4:07 PM
    Moderator
  • my database recovery model is FULL . And we are not taking any transaction log backups. We do full backup once in each night at 23:59pm and differential backups for each 1 hour. Is that an issue ?..Excuse my ignorance as I am a developer with very less DBA knowledge.
    • Edited by fbit_user Tuesday, November 10, 2009 1:40 PM
    Monday, November 09, 2009 4:36 PM
  • If you are not taking transaction log backups, there is no reason to run in FULL recovery mode and this could cause space issues as the log will only truncate on your full backup, once per week. I would suggest changing to SIMPLE mode.
    every day is a school day
    • Proposed as answer by Bikash Dash Tuesday, November 10, 2009 6:40 AM
    Monday, November 09, 2009 4:38 PM
    Moderator
  • sorry people. I made it as simple recover model still its size is not reducing..Still am I missing anything ?
    Tuesday, November 10, 2009 1:17 PM
  • The number of log records in the ldf file should affect the size of diff backups, as you now have seen. Did you check my prior post (I didn't see any reply to that) regarding index rebuild/reorg or similar operations. It is clear that something/somebody is modifying a large amount of data, the proof is in the size of the diff backup. You need to find what/who is doing this and make it all manageable.


    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, November 10, 2009 1:22 PM
    Moderator
  • Tibork,
    "regarding index rebuild/reorg or similar operations" is this something I need to enable/disable on the db?? could you please let me know where can I see these settings? (please ignore my innocence on this as I am java dev, completely new to sql server and dba activities too)
    Tuesday, November 10, 2009 1:40 PM
  • Most probably a scheduled job, possibly using the "SQL Server agent" job scheduler (but can really be anything). Check Agent Jobs in Management Studio, and also Maintenance Plans (which in turn uses Agent jobs).
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, November 10, 2009 3:08 PM
    Moderator
  • hi Tibork,
    Seems I am on the ball now. As per my database "maintenance plans" my DBA has setup following things:

    For full backups: (once in a day)
    ------------------------------------
    1. Backup Database Task (All databases, Type: Full, Append existing, destination: Disk) and this has an arrow pointing to
    2. Shrink Database Task (All databases, Limit: 50 MB, Free Space: 10%) and this has an arrow pointing to
    3. Check Database Integrity Task (All databases, Include Indexes)

    For Diff backups: (once per every hour)
    ----------------------------------------------
    1. Backup Database Task (All databases, Type: Differential , Append existing, destination: Disk)

    these two plans are added to one maintenance plan called "DailyFullDiffHourly" maintenance plan. Are there any mistakes he committed (before he left us)

    by any chance "Append existing" causing any issue? (in both full, diff backups)
    Tuesday, November 10, 2009 3:48 PM
  • Hmm, I thought I replied. Anyhow, it is ´the shrink you don't want to do. Many other resons listed here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, November 10, 2009 4:04 PM
    Moderator
  • Ah, i think you're right - the Append Existing will mean that the differential backups will all be added to one file so that is why its getting larger.

    This is in itself perfectly acceptable - essentially, you will be using the same space either way (eg 5 backup files of 10MB = 50MB or 1 file of 50MB containing 5 backups).

    However, this may cause a problem if you don't clear down older backup sets- perhaps consider going to a single file for each backup, differential backup and then having a cleanup job to delete files older than say, 1 week.
    every day is a school day
    Tuesday, November 10, 2009 4:11 PM
    Moderator
  • no,
    I am getting different files for different backups with sizes increasing like morning 9am (when business hours starts) it is 100kb and by 6pm (where business hours ends) it is nearly 1GB :( :( :( which is worrying me..
    Tuesday, November 10, 2009 4:41 PM
  • Perhaps even the database has autoshrink database option turned on (horror)...
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Tuesday, November 10, 2009 5:06 PM
    Moderator
  • No tibork. autoshrink option is set to "False". I have removed shrinking database option and introduced rebuilding/reorganizing indexes and scheduled backups for tomorrow. Need to see if any progress happens..
    Tuesday, November 10, 2009 11:38 PM
  • >> No tibork. autoshrink option is set to "False".

    Then you need to go and hunt down who are doing those modifications. The size of a diff backup will reflect how much data has been modified since last db backup. It as simple as that.

    >> I have removed shrinking database option and introduced rebuilding/reorganizing indexes and scheduled backups for tomorrow.

    Removing shrink will cause lot less modifications. Adding rebuild or reorg (I hope you didn't add both) will add many many modifications again. I suggest you try both with no shrink and no reorg/rebuild, and then add reorg or rebuild to see the difference.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, November 11, 2009 7:55 AM
    Moderator
  • surely tibork. I follow exactly what you mentioned and update results here.
    Wednesday, November 11, 2009 9:14 AM
  • thanks alot tibork for your time and help. I really respect your time but I am feeling bad to inform you this news. Because I downloaded a sample database of size 250 M and reorganized indexes on tables whose avg_fragmentation_in_percent is more than 40 and took a full backup. then its size was nearly 250 M and with in 5 min (WITH NO DB OPERATIONS, BELIEVE I DID NT CHANGE EVEN A SINGLE ROW UPDATE/INSERT/DELETE) I took a diff backup then it is of size nearly 1.5 MB.. Still I am wondering this as a big in size.

    steps I followed:
    -----------------
    1. ran reorganizing of indexes (eg: ALTER INDEX ALL ON <dbname>.dbo.<table_name> REORGANIZE)
    2. Took a full backup (gave me db of size 250 M)
    3. did nothing for 5 mins. Went for coffee.
    4. took a diff backup which gave me 1364 KB (nearly 1 MB). still seems to be an issue ..

    Let me try doing full backup and then organize indexes and then diff backup. But as per your material there are many tables/indexes whose avg_fragmentation_in_percent is more than 40 so I thought its better to choose rebuild this time rather reorganize.
    Wednesday, November 11, 2009 11:24 AM
  • Can you satisfy my curiorsity and run the following command to see what the contents of your backup file is:

    restore headeronly
    from disk = 'YourBackupFilePath\backupfilename.bak'
    

    every day is a school day
    Wednesday, November 11, 2009 11:37 AM
    Moderator
  • I see nothing strange in what you describe. You did a full backup, 250MB. Then reorg all tables with frag > 40% (probably no tables). Then a diff backup which were close to 0 in size. All seems very expected to me.

    Or are yo saying that you consider 1.5MB to be "too big"? There is always some meta-data in backups, so I would ignore the diff backup size unless it is over some 10-20MB and you feel no modifications were done since last db backup.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, November 11, 2009 11:44 AM
    Moderator
  • yes tibork. with no changes 1.5 MB is acceptable but if I use same mechanism in another database whose size is 7.5 GB and at the end of the day its last diffup is growing ti 1.5 GB. that's what my concern is.
    Wednesday, November 11, 2009 12:48 PM
  • Version 1:(experimenting with rebuilding indexes)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    1. I created a sample db called sam1 from a full backup (of last night live size: 275, 796 KB)

    2. After restoring immediately I took a full backup for it names as sam1_full_1 (and its size is: 275, 796KB which is same as base)

    3. As many table's avg_fragmentation_in_percent is more than 40, I ran
        ALTER INDEX ALL ON sam1.dbo.<table_name> rebuild

    4. (though i should nt have done this) Now took a diff backup called sam1_diff_1 its size is 239, 956 KB (ppl did u see this??? diff bkp is almost same as base however  I did nt do any thing, I even did nt start my java server to insert some data)
       this may be as expected as there was so many changes in db compated to base due to rebuilding indexes.

    5. Took a full bkp called sam1_full2 and its size is : nearly 250 MB

    6. now i started my java application server and tried to insert some rows to the database, like it does 10 inserts, 10 deletes, 10 updates..

    7. now I took again a diff backup and its sam1_diff_22 now its size is : 239.956 KB (I am dead now, as its just  235 MB ) its HUGE.
    and result of ur command is:


    restore headeronly from disk = 'C:\inetpub\ftproot\backups\sam1_diff_22'

    BackupName                          BackupDescription BackupType ExpirationDate  Compressed Position DeviceType               
    ----------------------------------- ----------------- ---------- --------------- ---------- -------- ----------               
    sam1-Differential Database Backup   NULL              5          NULL            0          1        2                        
                                                                                                                                  
    UserName  ServerName   DatabaseName   DatabaseVersion DatabaseCreationDate    BackupSize  FirstLSN                            
    --------- ------------ -------------- --------------- ----------------------- -----------------------------------             
    sa        FB2K8VM004   sam1           611             2009-11-11 12:28:12.000 245708288   10135000000737200001                
                                                                                                                                  
     LastLSN                CheckpointLSN         DatabaseBackupLSN     BackupStartDate         BackupFinishDate                  
     ------------------------------------------------------------------ ----------------------- -----------------------           
     10135000000737400001   10135000000737200001  9975000000099200077   2009-11-11 12:34:27.000 2009-11-11 12:35:13.000           
                                                                                                                                  
    SortOrder CodePage UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId SoftwareVersionMajor            
    --------- -------- --------------- ---------------------- ------------------ ---------------- --------------------            
    52        0        1033            196609                 80                 4608             9                               
                                                                                                                                  
    SoftwareVersionMinor SoftwareVersionBuild MachineName   Flags       BindingID                                                 
    -------------------- -------------------- ------------- ----------- ------------------------------------                      
    0                    3042                 FB2K8VM004    2560        D977BCB5-DB21-4429-976C-07A3407332B0                      
                                                                                                                                  
    RecoveryForkID                       Collation                      FamilyGUID                           HasBulkLoggedData    
    ------------------------------------ ------------------------------------------------------------------- -----------------    
    89861EE0-7C07-46EF-A6CC-4B99A91EFC9C SQL_Latin1_General_CP1_CI_AS   9CE02220-C406-4757-9879-6486D0ACB9CF 0                    
                                                                                                                                  
    IsSnapshot IsReadOnly IsSingleUser HasBackupChecksums IsDamaged BeginsLogChain HasIncompleteMetaData IsForceOffline IsCopyOnly
    ---------- ---------- ------------ ------------------ --------- -------------- --------------------- -------------- ----------
    0          0          0            0                  0         0              0                     0              0         
                                                                                                                                  
    FirstRecoveryForkID                  ForkPointLSN  RecoveryModel   DifferentialBaseLSN   DifferentialBaseGUID                 
    ------------------------------------ ------------- ------------------------------------- ------------------------------------ 
    89861EE0-7C07-46EF-A6CC-4B99A91EFC9C NULL          FULL            9975000000099200077   7250BDBF-0115-4ED9-BDD3-85AE095DC251 
                                                                                                                                  
    BackupTypeDescription   BackupSetGUID                                                                                         
    ----------------------- ------------------------------------                                                                  
    Database Differential   BDA8C682-5AE3-4382-80CF-B34CCCEE7A94                                                                  



    Version 2: (reorganisze index)

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    0. deleted sam1 database created in version 1.

    1. I created a sample db called sam1 from a full backup (of last night live size: 275, 796 KB)

    2. After restoring immediately I took a full backup for it names as v2_sam1_full_1 (and its size is: 275, 796KB which is same as base)

    3. As many table's avg_fragmentation_in_percent is more than 40, I ran intentionally reorganize rather rebuild.
        ALTER INDEX ALL ON sam1.dbo.<table_name> reorganize

    4. Now took a diff backup called v2_sam1_diff_1 its size is 212, 308 KB (in case of rebuild, the first diff bkp was 239, 956 KB )

    6. Took a full bkp called v2_sam1_full2 and its size is : 246, 100 KB.

    7. now i started my java application server and tried to insert some rows to the database, like it does 10 inserts, 10 deletes, 10 updates..

    8. now I took again a diff backup and its v2_sam1_diff_2 now its size is : 2388 KB (I am ok now, as its just 2.3 MB ) however its huge..

    BackupName                           BackupDescription  BackupType ExpirationDate Compressed Position DeviceType UserName                                                                       
    ------------------------------------ ----------------------------- ------------------------- -------- ---------- ----------                                                                     
    sam1-Differential Database Backup    NULL               5          NULL           0          1        2          sa                                                                              So at the end I did see no much change in diff backups even after running rebuild or reorganisze. but when i reoganize indexe atlest I did nt see horrible results.
                                                                                                                                                                                                     Am I still missing anything..
    ServerName    DatabaseName  DatabaseVersion DatabaseCreationDate    BackupSize  FirstLSN               LastLSN                                                                                  
    ------------- ------------- --------------- ----------------------- ----------- ---------------------- ---------------------                                                                    
    FB2K8VM004    sam1          611             2009-11-11 11:59:47.000 218445312   10160000002024000107   10160000002028500001                                                                     
                                                                                                                                                                                                    
                                                                                                                                                                                                    
    CheckpointLSN          DatabaseBackupLSN    BackupStartDate         BackupFinishDate        SortOrder CodePage UnicodeLocaleId                                                                  
    ------------------------------------------------------------------- ----------------------- --------- -------- ---------------                                                                  
    10160000002024000107   9975000000096400042  2009-11-11 12:12:03.000 2009-11-11 12:12:48.000 52        0        1033                                                                             
                                                                                                                                                                                                    
    UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName                                                           
    ---------------------- ------------------ ---------------- -------------------- -------------------- -------------------- -------------------------------------------                           
    196609                 80                 4608             9                    0                    3042                 FB2K8VM004                                                            
                                                                                                                                                                                                    
                                                                                                                                                                                                    
     Flags       BindingID                            RecoveryForkID                       Collation                                                                                                
     ----------- ------------------------------------ ------------------------------------ -------------------------------                                                                          
     2560        7E3B43BE-3A03-4585-A798-EFF1001DBD5A 65CB753F-9CAD-43F9-8DA8-1C32B546584A SQL_Latin1_General_CP1_CI_AS                                                                             
                                                                                                                                                                                                    
     FamilyGUID                           HasBulkLoggedData IsSnapshot IsReadOnly IsSingleUser HasBackupChecksums IsDamaged BeginsLogChain HasIncompleteMetaData IsForceOffline IsCopyOnly          
     ------------------------------------ ----------------- ---------- ---------- ------------ ------------------ --------- -------------- --------------------- -------------- ----------          
     9CE02220-C406-4757-9879-6486D0ACB9CF 0                 0          0          0            0                  0         0              0                     0              0                   
                                                                                                                                                                                                    
     FirstRecoveryForkID                  ForkPointLSN   RecoveryModel  DifferentialBaseLSN      DifferentialBaseGUID                 BackupTypeDescription      BackupSetGUID                      
     ------------------------------------ ------------------------------------------------------ ------------------------------------ ------------------------------------------------------------- 
     65CB753F-9CAD-43F9-8DA8-1C32B546584A NULL           FULL           9975000000096400042      0F5FEBD6-F1C5-4967-88E6-55E659E66433 Database Differential    643879E4-32DA-40AC-9021-17ACB6709E5F 

    Wednesday, November 11, 2009 12:49 PM
  • The simple fact is trhat the size of a diff backup reflect how muchg data you modified since last db backup. If the size of the diff backup is 1.5 GB, then you modified 1.5 GB worth of data (sort of, since a diff backup pick up an extent if it has been modified). If you feel that this 1.5 GB is "too much", then you need to figure outwhat modifications has been done and see if you can do anything about it. You can't change how diff backups work.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, November 11, 2009 2:06 PM
    Moderator
  • Hi Tibork,

    can you please give some references on how index rebuilding/ DB shrink process increases the size of differential backup?
    Wednesday, March 17, 2010 6:07 AM
  • Just combine two facts and deduce:

    Differential backup contain all extents which has been modified since last full backup:
    http://msdn.microsoft.com/en-us/library/ms345448.aspx


    Index rebuild will create a new index and then remove the old one. Lots of data (extents) modified.

    Shrink will move pages from end-of-file towards beginning of file. Lots of data (extents) modified.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    Wednesday, March 17, 2010 6:24 AM
    Moderator
  • I see a huge problem here with no log backups.  The inactive portion of your log is not being truncated and that may have an effect on your backups.

    I would set recovery to SIMPLE mode.  Shrink the log down to bare minimum.  Do a single expand to get it back to "normal", and leave it alone.  A good rule of thumb is when a differential backup reaches 40% of the size of the full backup, it is time to take a new full backup.

    Also, have you actually tried restoring under this scenario.  Full and Differential database backups are not like full and incremental file system backups.  A SQL diff backup is designed to replace restoring a long sequence of transaction logs, not to save backup space.

    A typical sysadmin optimizes for space.  A good DBA optimizes for performance.  The two are competing goals. 
    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP
    Wednesday, March 17, 2010 2:04 PM
    Moderator
  • I see a huge problem here with no log backups.  The inactive portion of your log is not being truncated and that may have an effect on your backups.

    I would set recovery to SIMPLE mode.  Shrink the log down to bare minimum.  Do a single expand to get it back to "normal", and leave it alone.  A good rule of thumb is when a differential backup reaches 40% of the size of the full backup, it is time to take a new full backup.

    Also, have you actually tried restoring under this scenario.  Full and Differential database backups are not like full and incremental file system backups.  A SQL diff backup is designed to replace restoring a long sequence of transaction logs, not to save backup space.

    A typical sysadmin optimizes for space.  A good DBA optimizes for performance.  The two are competing goals. 
    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP

    Is it then possible, a diff file ( not appended ) would ever be greater in size than the differential base ?
    Tuesday, October 26, 2010 4:31 PM