Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ski
Starting Member
3 Posts |
 Posted - 2009-11-10 : 08:11:21
|
| my live SQLSERVER database (though very few people are using it now) of size 7.5 GB (has got nearly 7 years data) and my DBA scheduled each hour 1 differential backup and daily 1 full backup at 7pm (after business hrs). "No other backups are scheduled as per my DBA's decision" (though its a bad backup schdule) As we know, hardly this system will have 100-200 inserts/updates/deletes per day (not more than that) but if I see size of differentials backed up the last differential database size is nearly 1GB per day. Is there a way I can reduce as my boss asking why is this so huge in size when very few operations carried out daily?? As this is completely related to DBA activity and as I am a developer, its really tough for me to debgu this. After following few sqlserver forums I changed database recovery model to "simple" from "full" (as we are not taking any transaction log backups). Any idea why my differential bkps are huge??? any changes I need to make to reduce diff bkp size?? |
|
|
support.sql
Starting Member
15 Posts |
Posted - 2009-11-29 : 11:26:53
|
| HiA differential backup backs up only modified extents since the last complete backup. An extent is a group of 8 data pages each consisting of 8 KB (64 KB in total). By definition, differential backups are cumulative. The most recent differential backup contains all changes from all previous differential backups performed since the most recent complete database backup. Differential backups may be considered as an alternative for databases that are large and are modified infrequentlyThe size of a differential backup depends on the amount of data that has changed since the base. Generally, the older a base is, the larger a new differential backup will be. A specific differential backup captures the state of changed extents at that time that backup is created. If you create a series of differential backups, a frequently updated extent is likely to contain different data in each differential. As the differential backups increase in size, restoring a differential backup can significantly increase the time that is required to restore a database. Therefore, we recommend that you take a new full backup at set intervals to establish a new differential base for the data. For example, you might take a weekly full backup of the whole database (that is, a full database backup) followed by a regular series of differential database backups during the week.Maybe there other operation into the database or your database extents are big.Review this link to get more information.http://msdn.microsoft.com/en-us/library/ms345448.aspxSNMTry Free Microsoft SQL Server Data Collector & Performance Monitor.http://www.analyticsperformance.com@nalyticsperformance - Microsoft SQL Server & Windows Server Free Data Collector |
 |
|
|
|
|
|
why my differential backup files are HUGE?