Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Differential Backup Size Expand / Collapse
Author
Message
Posted Tuesday, March 16, 2010 12:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:19 AM
Points: 231, Visits: 736

I have maintance plan. When I rebuild Index my Diffrential backup size is enormous (very big) what can I do.

P.S. = I take full backup after rebuild index finished but When the rebuild index is going the diffrential backup is always grow and grow
Post #883566
Posted Tuesday, March 16, 2010 1:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:21 AM
Points: 41,507, Visits: 34,422
If you're rebuilding all your indexes, you're modifying just about the entire database, hence any differential run after that, before the next full backup will be large, because they contain changes since the last full backup.

How long does index rebuild take and how often are your diff backups that you say backups during the rebuild are large?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #883590
Posted Tuesday, March 16, 2010 9:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:19 AM
Points: 231, Visits: 736
My rebuild index taken about 5 hours and my diffrantial backup runs about per one hour about that time
diff backup starting to grow up i think maybe ı can write a query if index plan is running does not get diff backup bu i think it can be another solution
Post #883875
Posted Tuesday, March 16, 2010 12:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 10, 2014 8:19 AM
Points: 7,002, Visits: 6,181
Ouch!

Might I recommend only using Differential on a daily basis (not an hourly) and backing up the Transaction Log on an hourly basis instead?

I've never seen Differentials as an "hourly" type of backup. Others may have different opinions, but Differentials, IMHO, should be used so you don't have to have a daily FULL backup if your database is large.

If I'm misunderstanding your backup plan, I apologize.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #884114
Posted Tuesday, March 16, 2010 1:14 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:21 AM
Points: 41,507, Visits: 34,422
Why are you taking differential backups every hour?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #884135
Posted Tuesday, March 16, 2010 11:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:19 AM
Points: 231, Visits: 736
If I lost my Db I have only one hour ı data lost and my recovery model is simple
Post #884411
Posted Tuesday, March 16, 2010 11:43 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, January 22, 2014 6:44 PM
Points: 891, Visits: 234
If your database is critical. It is recommanded to have database in full/bulked log recovery model. And plan for log backups. here you recover up to last point of failure as well.

Taking up Differential backups so frequently may be costly operation as it takes differential data from last full backup. (Not from last differential backup)




Post #884416
Posted Tuesday, March 16, 2010 11:50 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:28 PM
Points: 20,453, Visits: 14,063
Change to Full recovery mode and run transaction log backups. With transaction log backups you can recover to the point just before failure. That is much more acceptable than losing 1 hour of data.

Using differential backups in this fashion is inefficient and costly.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #884418
Posted Tuesday, March 16, 2010 11:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, January 21, 2014 8:19 AM
Points: 231, Visits: 736
if ı make it my ldf file size is very big
Post #884419
Posted Tuesday, March 16, 2010 11:56 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:28 PM
Points: 20,453, Visits: 14,063
ESAT ERKEC (3/16/2010)
if ı make it my ldf file size is very big



That's why you take transaction log backups. If you don't backup the transaction log then it will grow. The ldf is your transaction log and thus contains the information you will need to perform a point in time recovery and thus minimize data loss.





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #884420
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse