Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are doing a full backup once a week and a daily diff between that. The total db is 80Gb, the diffs start out under 1Gb, then gradually grow to approximately 3-5Gb which is ok, but then all of a sudden jump to 40Gb almost every week. Why would this happen? How do I investigate? I'm reasonably sure that the actual db usage is uniform w/o spikes.

share|improve this question
add comment

3 Answers 3

up vote 6 down vote accepted

Perhaps you've got a maintenance job - such as reindexing - that occurs once a week - that would cause a massive jump in diff backup file size. Or perhaps it's a once-a-week application job that imports or changes data?

If you're also taking regular transaction log backups (say once an hour), if you look at the file size of those backup files it may give you a clue as well as to when the change activity is.

share|improve this answer
add comment

The size of your differential backup is dependent upon the amount of data that has changed since the last full backup.

You should be able to identify which day there is a spike in the size of the backup. Start by seeing if there is a trend, perhaps it is the same day every week, for example. Then look to see if you have any maintenance jobs that are running on those days.

It could be the case that perhaps you do not have jobs running at that time, but there could be a piece of application code that decides to perform some maintenance without your knowledge.

If you are able to isolate the particular day that you see the spike you may want to use Adam Machanic's free tool WhoIsActive http://sqlblog.com/blogs/adam_machanic/archive/2010/10/21/who-is-active-v10-00-dmv-monitoring-made-easy.aspx, and see this blog post from Kendra Little on how to store the results http://www.littlekendra.com/2011/02/01/whoisactive/

share|improve this answer
add comment

Differential backups save the extents that were changed since the last full backup. Any update/insert/delete statements to your DB will have extents that change. Most of the time, if the DB size is the same, then the extents changed will be due to update statements run on the DB. Another event to look out for are maintenance tasks on the DB like index rebuilds.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.