March 16, 2010 at 12:08 am
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
March 16, 2010 at 1:55 am
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
March 16, 2010 at 9:08 am
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 i can write a query if index plan is running does not get diff backup bu i think it can be another solution
March 16, 2010 at 12:50 pm
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.
March 16, 2010 at 1:14 pm
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
March 16, 2010 at 11:31 pm
If I lost my Db I have only one hour i data lost and my recovery model is simple
March 16, 2010 at 11:43 pm
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)
March 16, 2010 at 11:50 pm
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[/url]
Learn Extended Events
March 16, 2010 at 11:52 pm
if i make it my ldf file size is very big
March 16, 2010 at 11:56 pm
ESAT ERKEC (3/16/2010)
if i 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[/url]
Learn Extended Events
March 16, 2010 at 11:58 pm
double post
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 17, 2010 at 12:02 am
Hi GilaMonster,
Can you please give some references more information on how differential backup size increases because of Index Rebuilding process?
I am not able to find any information on this.
I got one more thread
This also mentioning about index rebuilt and databas shrinking as one of the reason for large size of differential backups.
March 17, 2010 at 12:58 am
Oke I will try your comments
March 17, 2010 at 4:21 am
vidya_pande (3/17/2010)
Can you please give some references more information on how differential backup size increases because of Index Rebuilding process?I am not able to find any information on this.
I'm not Gail, but here goes.
Pick up a SQL Server reference book. Generally, it will have a section in the back called "Index". This index is grouped by subject matter. So it'll start with A, list topics starting with the letter 'A', some topics might have subtopics that are indented a little. For instance, in the book I just picked up, Aggregations is a topic with subtopics of Aggregation Design Wizard support (pp-pp), applying to data regions (pp), backing up (pp), defined (pp), etc.
Each "pp" is a page number on which I can find the topic. This is called a pointer in SQL Server terms. Essentially, the Index is ordered alphabetically, which makes it a clustered index, and it points to data on pages outside of itself, which makes the pages indexed with a non-clustered index.
Now say I have 500 new topics to insert into the book Index. I need to rearrange the text on these pages and insert blank pages to add these topics in. In doing this, I end up with a lot of extra page space between some of the letters. Say, 1/2 a blank page below the letter "C' which forces the "D" topics onto a brand new page. This is inelegant, hard to read, and just sloppy publishing. So I "rebuild" my indexes, squeezing the topics together to shove all the white space to the end of the book. But in order to do this, I have to copy all my Index pages on to brand new pages (cut-n-paste basically) because I can't just "shrink" a physical piece of paper without losing what's printed on the other side.
So I copy the text to a brand new set of pages, in the process eliminating all the white space. But now I have double the number of pages I had to begin with. The only way to get rid of this extra space is to excise or truncate it. Free it up for my "operating system" so to speak.
Now, in RL, SQL Server does actually "copy" the data it's reworking onto extra pages and extents (look these terms up in BOL) so it doesn't lose the data. Additionally, while it's working on rebuilding indexes, it's creating transactions that are, for the moment, completely uncommitted. It won't commit those transactions until it's finished rebuilding the index it's currently working on and even then, the transaction might remain uncommitted for several minutes / hours afterwards depending on your database load.
Because SQL Server needs all that space to work in, it will expand the database to meet that need. The next backup you take will include all that extra space. And the database data file WILL NOT TRUNCATE unless you have Auto-Shrink on (bad idea) or you manually truncate every so often. The Transaction Log will not Truncate unless you manually truncate or you do Transaction Log backups. (Note: Shrinking and Truncating are two slightly different things.)
And since a Differential backup takes every change since the last FULL backup (not the last Differential), it will continue to grow in size until you do another FULL backup. Whereas a Transaction Log backup might be huge the first time you do it after an Index rebuild, but it will truncate and shrink itself shortly thereafter, making the resulting Transaction Log backups smaller than the first one.
Does that help?
March 17, 2010 at 8:00 am
Hi Tarvin,
Thanks for the info.
Will get back if i come across any doubts on this.
Thanks again !!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply