f649066a   
   From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   On Fri, 28 Aug 2009 11:45:35 -0700 (PDT), hayko98 wrote:   
      
   >i have prd database (10Gb which has 3 monts of data.SQL 2005).Every day   
   >(at 1am) i do full back up and at 12:30am diff back up.Diff backup   
   >size is 5gb.Any opinions why so big?   
      
   Hi Hayko,   
      
   A differential backup contains a copy of each database page that is   
   changed since the last full backup. So if about 1% of your data is   
   changed and the changes all hapen to be clustered on the same pages, the   
   diff backup size will be about 1% of the database size. However, if the   
   same 1% of changed data happens to be spread all around the database,   
   all on different pages, you could as a worst case even run into a diff   
   backup that has to dump ALL the pages!   
      
   Apart from the changed pages, a diff backup also contains a copy of all   
   log records, starting (at the top of my head) at the start of the oldest   
   transaction that was running when the backup started up until the last   
   log entry just before the backup finished. So if the backup takes a long   
   time to run, and there is a lot of activity going on in the database,   
   you might get a diff backup with a long list of log records at the end.   
      
   --   
   Hugo Kornelis, SQL Server MVP   
   My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)