November 23, 2009 at 5:40 am
I am (basically a java developer) facing the prob that my live db is 7.5 GB and its diff backups per day are 1.5 GB. (in fact we take diff backups for 4 hr) When i ran randy's script (http://www.sqlskills.com/BLOGS/PAUL/post/New-script-How-much-of-the-database-has-changed-since-the-last-full-backup.aspx ) for the half day (i.e at lunch time which is half of a complete business day) I got following output:
total change percentage
125658 17169 13.66
I am sure this is an old application and very few ppl use this but how can this be possible to show 17169 changed extents which is causing: 17169 * 64k => 1098.816 MB (nearly 1GB differential) for such a less used application. To debug further I made a one row insert, one update and seen nearly 5 extents are changing. and after inserting 130 rows early 10 extents are changing. I am totally confused in understanding how db extents are changed and how can we minimize to get smaller diff backups? any links/help on this would be highly appreciated.
PS: no index rebuid/reorg happened to shuffle the data. All table's indexes are clustered (i.e primary key columns)
November 23, 2009 at 8:29 am
First, a diff contains all changes since the last full backup. So if you make 1 change, then run a diff, then make a 2nd change and run a diff, BOTH changes are in the second diff. Not just the second one.
Next, in changing extents, they aren't changed directly. Pages are changed. So if you make an update, one of the 8 pages (or more) in the extent is changed. An insert can cause a page split and that could cause two or more pages to change. Same for the update. You might also have triggers that cause changes, auditing, etc.
It's possible, but without more information on what code is being run, it's hard to know if something strange is happening. Also 4 hours for a 1GB diff is a long time. It makes me think you have I/O issues or hardware problems.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply