November 10, 2009 at 6:13 am
hi all,
my live SQLSERVER database (though very few people are using it now) of size 7.5 GB (has got nearly 7 years data) and my DBA scheduled each hour 1 differential backup and daily 1 full backup at 7pm (after business hrs). "No other backups are scheduled as per my DBA's decision" (though its a bad backup schdule) 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 the last differential database size is nearly 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 this is completely related to DBA activity and as I am a developer, its really tough for me to debgu this. After following few sqlserver forums I changed database recovery model to "simple" from "full" (as we are not taking any transaction log backups). Any idea why my differential bkps are huge??? any changes I need to make to reduce diff bkp size??
November 10, 2009 at 6:48 am
You've already said you think it's a bad backup plan, so I won't pass any comments on what you're doing.
Switching from full to simple won't make any difference to your differential backup sizes, it just affects the transaction log (and your new lack of ability to recover to a point in time!!!).
Do you have any jobs scheduled to run on a regular basis... particularly re-index jobs as they are likely to generate a lot of changes.
Does the size of the differential backup suddenly grow at a particular time of the day? If so, then have a look to see what is run during the hour immediately prior to the growth.
November 10, 2009 at 6:51 am
Index rebuilds?
Why are you doing differential backups hourly? How often are log backups running?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 10, 2009 at 7:06 am
particularly re-index jobs / Index rebuilds?
Even I have seen similar answer in other forums as well. may i know where can I see these settings to enable/disable? because even I guess something related like this (as many other gurus like you are suggesting same)
November 10, 2009 at 7:24 am
You sure you want to disable your index rebuild jobs?
They'll be sQL agent jobs or maiontenance plans. Check with the DBA before you take any action.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 10, 2009 at 9:20 am
GilaMonster (11/10/2009)
Index rebuilds?Why are you doing differential backups hourly? How often are log backups running?
we are not doing any transaction log backups. My DBA has setup diff backups for every one hour which I dont have rights to change. So you mean to say, because of Index rebuilds its taking so much space and having huge diff backups..
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
by any chance "Append existing" causing any issue? (in both full, diff backups)
November 10, 2009 at 9:48 am
"...2. Shrink Database Task (All databases, Limit: 50 MB, Free Space: 10%)..."
Why ? Probably causes more problems than it solves.
November 10, 2009 at 9:54 am
There is no reason to run a shrink, and that is likely the cause of the large diff backups.
A better daily maintenance would be: 1. Update statistics, 2. Integrity check, 3. Full backup.
Also, change DB recovery mode to Full and do transaction log backups every 15 minutes. Eliminate the diff backups.
November 10, 2009 at 1:39 pm
Michael Valentine Jones (11/10/2009)
A better daily maintenance would be: 1. Update statistics, 2. Integrity check, 3. Full backup.
And an occasional index rebuild, doing all the indexes or targeted to the ones that need rebuilding.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 10, 2009 at 3:10 pm
Get that shrink database step out of there! If space is an issue where the storage for the database files are there are better ways to plan/manage for it.
Also, I totally that reorg/rebuild steps of the indexes should happen at regular intervals, preferably during periods of slow or no activity.
Joie Andrew
"Since 1982"
November 11, 2009 at 4:21 am
thanks alot people for your time and help. I really respct your time but I would like to know should I rebuild or reorganize indexes before taking full backup or should I preform them after taking full backup. Because I downloaded a sample database of size 250 M and reorganized indexes on table whose avg_fragmentation_in_percent is more than 40 and took a full backup. then its size was nerly 250 M and with in 5 min (WITH NO DB OPERATIONS, BELIEVE I DID NT CHANGE EVEN A SINGLE ROW UDATE/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 ..
November 11, 2009 at 6:34 am
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
November 11, 2009 at 7:23 am
a diff backup will backup every page or extent that has been modified. even if it was a bit value that was changed, it will backup the entire page or extent. don't remember which it was.
i have a few databases where i run daily diff backups and the backup size is close to the db size. index rebuilds are part of it. another reason is indexing. if you have a poor choice of a clustered index than changes can affect more pages and it will result in larger backups
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply