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
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2777 Visits: 1068
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 (466K reputation)SSC Guru (466K reputation)SSC Guru (466K reputation)SSC Guru (466K reputation)SSC Guru (466K reputation)SSC Guru (466K reputation)SSC Guru (466K reputation)SSC Guru (466K reputation)

Group: General Forum Members
Points: 466767 Visits: 47339
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
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2777 Visits: 1068
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
SSC Guru
SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)SSC Guru (77K reputation)

Group: General Forum Members
Points: 77822 Visits: 9450
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 (466K reputation)SSC Guru (466K reputation)SSC Guru (466K reputation)SSC Guru (466K reputation)SSC Guru (466K reputation)SSC Guru (466K reputation)SSC Guru (466K reputation)SSC Guru (466K reputation)

Group: General Forum Members
Points: 466767 Visits: 47339
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
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2777 Visits: 1068
If I lost my Db I have only one hour ı data lost and my recovery model is simple
vidya_pande
vidya_pande
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2811 Visits: 243
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 Guru
SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)

Group: General Forum Members
Points: 123906 Visits: 18627
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
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2777 Visits: 1068
if ı make it my ldf file size is very big
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)

Group: General Forum Members
Points: 123906 Visits: 18627
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