why my differential backups are huge in size?
Question
Monday, November 9, 2009 3:20 PM
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.
All replies (27)
Monday, November 9, 2009 3:52 PM | 1 vote
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 9, 2009 4:07 PM
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 9, 2009 4:36 PM
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.
Monday, November 9, 2009 4:38 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
Tuesday, November 10, 2009 1:17 PM
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:22 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:40 PM
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 3:08 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:48 PM
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)
- Backup Database Task (All databases, Type: Full, Append existing, destination: Disk) and this has an arrow pointing to
- Shrink Database Task (All databases, Limit: 50 MB, Free Space: 10%) and this has an arrow pointing to
- Check Database Integrity Task (All databases, Include Indexes)
For Diff backups: (once per every hour)
- 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 4:04 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.aspTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
Tuesday, November 10, 2009 4:11 PM
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:41 PM
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 5:06 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 11:38 PM
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..
Wednesday, November 11, 2009 7:55 AM
>> 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 9:14 AM
surely tibork. I follow exactly what you mentioned and update results here.
Wednesday, November 11, 2009 11:24 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:
- ran reorganizing of indexes (eg: ALTER INDEX ALL ON <dbname>.dbo.<table_name> REORGANIZE)
- Took a full backup (gave me db of size 250 M)
- did nothing for 5 mins. Went for coffee.
- 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:37 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:44 AM
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 12:48 PM
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:49 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)**
deleted sam1 database created in version 1.
I created a sample db called sam1 from a full backup (of last night live size: 275, 796 KB)
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)
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> reorganizeNow 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 )
Took a full bkp called v2_sam1_full2 and its size is : 246, 100 KB.
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..
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 2:06 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, March 17, 2010 6:07 AM
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:24 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 2:04 PM
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
Tuesday, October 26, 2010 4:31 PM
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 ?
Monday, November 7, 2016 4:17 AM
How does taking T-Log backup help this situation?