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.
Add a comment
|
3 Answers
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.
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 sp_WhoIsActive http://whoisactive.com/, and see this blog post from Kendra Little on how to store the results http://www.littlekendra.com/2011/02/01/whoisactive/
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.
answered Apr 19, 2011 at 14:41
