Differential backups way too large

I don't know why this is happening...I have a database with the following
backup schedule..
Friday 2:30am Full backup
Daily except friday 2:30am Differential backup
Daily between 6:15am and 11:45pm half hourly tlog backups
So I'm looking at last weeks full backup it is 2GB as of 2:30 am...
The first tlog backup at 6:15am is 1.4 GB ?
The diff starting the next day was already 1.9GB yet the next full backup
will only be 2GB?
If a differential is only supposed to show the changes since last full
backup then how can it be the size of the full database by the next day?
There is absolutely no way all that data has changed.
Is there some magical setting I'm missing that resets the state of something
on a full backup?
any/all help is most graciously appreciated....I'm really tired of moving
unnecessary data around our wires....
Tim,
Do you also rebuild indexes, do a DBCC INDEXDEFRAG, et cetera? These things
also cause pages to change and logs to grow.
RLF
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23E92rvLdIHA.3400@.TK2MSFTNGP03.phx.gbl...
>I don't know why this is happening...I have a database with the following
>backup schedule..
> Friday 2:30am Full backup
> Daily except friday 2:30am Differential backup
> Daily between 6:15am and 11:45pm half hourly tlog backups
>
> So I'm looking at last weeks full backup it is 2GB as of 2:30 am...
> The first tlog backup at 6:15am is 1.4 GB ?
> The diff starting the next day was already 1.9GB yet the next full backup
> will only be 2GB?
> If a differential is only supposed to show the changes since last full
> backup then how can it be the size of the full database by the next day?
> There is absolutely no way all that data has changed.
> Is there some magical setting I'm missing that resets the state of
> something on a full backup?
> any/all help is most graciously appreciated....I'm really tired of moving
> unnecessary data around our wires....
>
|||Yes I do and thought about that immediately after posting this... BUT ... I
can see that making my first tlog large, but not every days DIFF file.
Oh crap...just realized. If I'm rebuilding clustered indexes, then I'm
indirectly updating EVERYTHING correct?
Guess I need to be sure that happens immediately before my full/diff
backups.
Thanks for such a quick response.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23C3S6xLdIHA.4588@.TK2MSFTNGP06.phx.gbl...
> Tim,
> Do you also rebuild indexes, do a DBCC INDEXDEFRAG, et cetera? These
> things also cause pages to change and logs to grow.
> RLF
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23E92rvLdIHA.3400@.TK2MSFTNGP03.phx.gbl...
>
|||Hi Tim,
What you saw is not uncommon.
Think of diffential backup the same as tran log backup in that they all
contain db changes. They only differ in that the diffential backup is
accumulative, whereas the tran log backup is incremental. If database
activities do fancy things, such as insert 10 milion records in a temp
table, update them, then insert into a second temp table, then delete from
the frist table, then ..All these transactions contribute to the size of
tran log or diff backups. although as a net result, that may not affect the
size of dbs too much.
I suggest you run a profile trace to see what exactly run during the log
growth. You may be surprised to see the apps doing dumb things...
rick
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23E92rvLdIHA.3400@.TK2MSFTNGP03.phx.gbl...
>I don't know why this is happening...I have a database with the following
>backup schedule..
> Friday 2:30am Full backup
> Daily except friday 2:30am Differential backup
> Daily between 6:15am and 11:45pm half hourly tlog backups
>
> So I'm looking at last weeks full backup it is 2GB as of 2:30 am...
> The first tlog backup at 6:15am is 1.4 GB ?
> The diff starting the next day was already 1.9GB yet the next full backup
> will only be 2GB?
> If a differential is only supposed to show the changes since last full
> backup then how can it be the size of the full database by the next day?
> There is absolutely no way all that data has changed.
> Is there some magical setting I'm missing that resets the state of
> something on a full backup?
> any/all help is most graciously appreciated....I'm really tired of moving
> unnecessary data around our wires....
>
|||Hmmm that is NOT what BOL says about differentials. TLOGS grow based on
activity level...diffs show the actual database pages that have been altered
only regardless of activity level.
"Rick D." <y.ding@.neu.edu> wrote in message
news:1D24A79F-AD81-4B7D-A279-80ED18C72E9E@.microsoft.com...
> Hi Tim,
> What you saw is not uncommon.
> Think of diffential backup the same as tran log backup in that they all
> contain db changes. They only differ in that the diffential backup is
> accumulative, whereas the tran log backup is incremental. If database
> activities do fancy things, such as insert 10 milion records in a temp
> table, update them, then insert into a second temp table, then delete from
> the frist table, then ..All these transactions contribute to the size of
> tran log or diff backups. although as a net result, that may not affect
> the size of dbs too much.
> I suggest you run a profile trace to see what exactly run during the log
> growth. You may be surprised to see the apps doing dumb things...
>
> rick
> "Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
> news:%23E92rvLdIHA.3400@.TK2MSFTNGP03.phx.gbl...
>
|||Yes, you got it. - RLF
"Tim Greenwood" <tim_greenwood AT yahoo DOT com> wrote in message
news:%23odFZ8LdIHA.4744@.TK2MSFTNGP06.phx.gbl...
> Yes I do and thought about that immediately after posting this... BUT ...
> I can see that making my first tlog large, but not every days DIFF file.
> Oh crap...just realized. If I'm rebuilding clustered indexes, then I'm
> indirectly updating EVERYTHING correct?
> Guess I need to be sure that happens immediately before my full/diff
> backups.
> Thanks for such a quick response.
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23C3S6xLdIHA.4588@.TK2MSFTNGP06.phx.gbl...
>