November 21, 2011 at 5:55 am
Hi I have had an issue with a differential database backup ballooning to 50GB following the addition of a new datafile to the database. The log backups taken in the same time are a fraction of that - 10GB. Has anyone experienced similar? SQL2005 ENT.
Thanks in advance.
November 21, 2011 at 6:05 am
What is the overall size of your database? How often do you do full and log backups?
John
November 21, 2011 at 11:14 am
A differential is the sum total of all changes since the last full backup. When did you last take a full backup?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 22, 2011 at 4:09 am
Plan for Log backups, and daily differential will help a lot
November 22, 2011 at 4:21 am
The log backup logged the alter database statement and also modifications to the system’s metadata tables that reflect the database’s modification. It didn't have to copy/backup the files' pages. The differential backup has to backup every page in the database that was modified since the last full backup. This means that it has to backup all the pages in the new files that you’ve added to the databases, because each page in those files doesn’t exist in the full backup that was done before the database’s modification. After you’ll do a full backup again, the differential backup will backup only pages that were modified in the new files and not all the pages in the new files.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 23, 2011 at 8:16 am
Adi Cohn-120898 (11/22/2011)
The log backup logged the alter database statement and also modifications to the system’s metadata tables that reflect the database’s modification. It didn't have to copy/backup the files' pages. The differential backup has to backup every page in the database that was modified since the last full backup. This means that it has to backup all the pages in the new files that you’ve added to the databases, because each page in those files doesn’t exist in the full backup that was done before the database’s modification. After you’ll do a full backup again, the differential backup will backup only pages that were modified in the new files and not all the pages in the new files.Adi
That is not a fully-correct statement. Differential backups will backup all EXTENTS that have been changed since the last full backup. So you can change 1 byte on one page and wind up backing up 8 8K data pages (extent). Thus lots of random small changes can lead to very large diff backups very quickly. In this case though it was probably the movement (assuming there was some) of data onto the new file group that may have caused the large diff?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply