SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Differential Backup Size


Differential Backup Size

Author
Message
ESAT ERKEC
ESAT ERKEC
Mr or Mrs. 500
Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)

Group: General Forum Members
Points: 574 Visits: 940
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87185 Visits: 45270
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, MVP, M.Sc (Comp Sci)
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


ESAT ERKEC
ESAT ERKEC
Mr or Mrs. 500
Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)

Group: General Forum Members
Points: 574 Visits: 940
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
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14560 Visits: 8990
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 AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87185 Visits: 45270
Why are you taking differential backups every hour?

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

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


ESAT ERKEC
ESAT ERKEC
Mr or Mrs. 500
Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)

Group: General Forum Members
Points: 574 Visits: 940
If I lost my Db I have only one hour ı data lost and my recovery model is simple
vidya_pande
vidya_pande
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1099 Visits: 242
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)



SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32278 Visits: 18552
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

ESAT ERKEC
ESAT ERKEC
Mr or Mrs. 500
Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)Mr or Mrs. 500 (574 reputation)

Group: General Forum Members
Points: 574 Visits: 940
if ı make it my ldf file size is very big
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32278 Visits: 18552
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search