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

Huge DIFF Backup, Normal T-Log Backups Expand / Collapse
Author
Message
Posted Saturday, April 6, 2013 9:12 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:10 PM
Points: 2,826, Visits: 8,463
I have a 55 Gig database, and the differential backup for yesterday was 32 Gig !! It was about 5 gig the day before

So, I assumed I could look at my transaction log backups and find a huge T-log backup to identify what time of day something big happened. But my t-logs, every 15 minutes, show 1 backup > 1 gig, and all the others quite small, and similar to other days this week.

What can make a huge Diff backup, without generating huge t-log backup(s) ?



Post #1439586
Posted Saturday, April 6, 2013 12:09 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:15 PM
Points: 5,976, Visits: 12,886
differential backups contain all the extents that have been touched by updates, therefore a single row update would lead to 64k worth of backup. Educated guess some process or processes occurred that touched a lot of extents leading to a large differential backup but not necessarily a large log backup.

sounds like its time to reset the differential base.


---------------------------------------------------------------------

Post #1439593
Posted Saturday, April 6, 2013 1:05 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:10 PM
Points: 2,826, Visits: 8,463
The next Full backup is tomorrow morning, so that is good. Puzzled by the huge Diff though. I have trace files from that time period. Not sure the best columns to look at .... I/O ?, but maybe I'll take a look.


Post #1439600
Posted Sunday, April 7, 2013 8:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
homebrew01 (4/6/2013)
I have a 55 Gig database, and the differential backup for yesterday was 32 Gig !! It was about 5 gig the day before

So, I assumed I could look at my transaction log backups and find a huge T-log backup to identify what time of day something big happened. But my t-logs, every 15 minutes, show 1 backup > 1 gig, and all the others quite small, and similar to other days this week.

What can make a huge Diff backup, without generating huge t-log backup(s) ?


T-Log backups that occur every 15 minutes happen 96 times a day. If you divide 32 by 96, the average T-Log size is only 0.3333.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1439646
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse