Sunday, March 11, 2012

Differential Backups Produce 55GB Files

We have a SQL 2K5 10GB database that, as part of the recovery plan, gets a differential backup every six hours. Log file backups occur every hour, and a full backup is done every 24 hours. Over the weekend, the differential backup produced a 55GB backup file which caused us a lot of issues besides disk space usage (log backups couldnt finish, mirroring broke, etc.). This is also the max growth size that the log file is set to. There are no errors in the ERRORLOG, or in the job history. It's as if the backup was successful, which I assume it was, but the file was sparse.

I should mention that our full backup is typically 10GB, log file backups are typically 100 to 500MB, and the diff backup is generally 1GB to 3GB.

Has anyone experienced this issue before?
What causes it?
How do we resolve it?

Thank you in advance for your help,
Greg
if the log backups occurs every 1 hr you dont need to schedule the differential backup.......moreover check if any process is running at the time of backup......generally if you schedule the backups frequently the log files shudnt grow as high in your case........|||

I'm not worries about the size of the log files. The logs are fine. This post regards the size of the differential backup file itself (55GB). We don't really need it, true, but its much easier to restore one diff backup over six logs in my humble opinion, but this is really beside the point.

|||Was there any unusual activity over the weekend? Someone doing a bunch of bulks loads or index rebuilds for instance?|||Are you appending your differential backups to the same file, or creating a new file for each differential backup?|||Yes. Over the weekend we usually do an index rebuild, and some heavy inserts. Our database is mirrored as well, so we're not able to actually do the heavy inserts as a true bulk insert.|||Each backup creates a new file. A secondary process on another machine copies the files over to another server.|||

most probably this is coz of the activities in the weekend. When u rebiult index it take extraspace to create the fresh index . the existing index are not overwritten beacuse if system needs the existing index to rollback if necessary. Differential backup take the backup of all the extents changed from last full backup. So what i would prefer is... take full backup after the weekend maintenance instead of Differential(if you have maintenance window).

Madhu

No comments:

Post a Comment