| Author |
Topic |
|
gangadhara.ms
Aged Yak Warrior
549 Posts |
 Posted - 2010-07-09 : 00:33:11
|
| Dear All,Today while monitoring one of the my production server backup for one of the database the Differential back up size is more than the full backup size,is this possible ?Any solution for this ? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-09 : 03:24:53
|
| Log-shot: DIFF has backed up split index pages which will be reorganised before the FULL occurs? |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-09 : 03:25:34
|
... or data which will be deleted before the FULL occursFWIW I always think our DIFF backusp are "unreasonably" large - relative to full backups |
|
|
|
Yeoh Ray Mond
Starting Member
49 Posts |
Posted - 2010-07-09 : 09:50:48
|
| If you are comparing backup file sizes, perhaps there are multiple backup sets contained in the differential backup file?Ray MondSQLBackupRestore.com - your quick guide to SQL Server backup and recovery issuesSQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-07-09 : 11:34:50
|
| You must analyse what amount of modifications have occured since your last backup. is there a large amount of data being modified?Are there large maintenace jobs?Basically , a differential backup will include all extents that have been modified since the last backupJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-09 : 17:39:58
|
| And a Full Back will include ALL used extents, won't it Jack? Thus full backup should be larger than DIFF? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-07-10 : 05:07:24
|
| Kristen, the diff backup should reflect the amount of data modified since the last backup, i.e extents that have been modified. For example, index rebuilds , bulk inserts. If there have been many modifications than the diff backup can be larger than the initial backup. Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
Yeoh Ray Mond
Starting Member
49 Posts |
Posted - 2010-07-10 : 06:30:51
|
| I was also a little confused about your first response that stated 'a differential backup will include all extents that have been modified since the last backup' as a possible cause that resulted in the differential backup being larger than a full backup. I guess you didn't mean 'modified' in the literal sense, but rather to include all modifications and additions of extents to a database.So for e.g. if database A was x in size, and there were enough data insertions that resulted in the database growing to > (x * 2) in size, then it will certainly result in a differential backup file that's larger than the previous full backup.Ray MondSQLBackupRestore.com - your quick guide to SQL Server backup and recovery issuesSQL Image Viewer - retrieve, view, convert and export images and binary data from SQL Server, Oracle, DB2, PostgreSQL, SQLite, and Firebird databases.SQL Data Sets - share and distribute SQL Server, Oracle and PostgreSQL data sets securely and easily |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2010-07-10 : 12:22:55
|
| You can simulate the process by : creating a blank database, add some data, take a Full backup with init. Then , do some data inserts, index rebuild etc, at that point , if you have done sufficient modifications - the Differential will be bigger than the initial full backupJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-11 : 03:37:33
|
| Ah, I see thanks Jack. So a DIFF will have to record modified extents, including extents that have become empty (in order that they are "removed" when Full + Diff is restored), whereas the Full only has to record the Used extents. Obvious really now you've mentioned it, thanks.Also accounts for why my DIFFs are such a large percentage of Full backup size when only a small percentage of data has changed; we run Index Reorg on anything fragmented every night ... |
|
|
|
|
Differential back up Size is more than Full backUp