0

We are currently using Data protection manager 2012 SP1 to back up our medical records database in SQL Server 2008 R2.

The database server is running Windows Server 2008 R2 as a virtual machine with the VM host running Windows Server 2012.

The database files exist on a cluster shared volume connected to the VM. On the backend of the CSV, it connects using iSCSi to our Lefthand/HP SANs where the data is ultimately stored.

We recently installed DPM 2012 after having the server being backed up on DPM 2008.

The issue we are having is that each night the express full backups seem to continue to grow. The database, including log file, is around 400GB. 305GB of that is for the MDF specifically.

We have a nightly maintenance job running based on the hallengren sql scripts. This job currently only reorganizes or rebuilds the indexes that require it based on the default conditions of fragmentation in the script.

Auto-Update Statistics is also set to true for this database.

So, the problem is that our express full backups, that run once a night from DPM, seem to use an increasing amount of space. Sometimes this space is reduced, but several times it has been almost as large as the MDF file itself. So, something seems off here and I'm hoping you all can give me some ideas.

I will say that the amount of space, for our 30 day retention period, has stabilized after the past month and it does not seem to be asking for any more. We also grab recovery points every 15 minutes.

Thanks for any help you can offer.

Improve this question
6
  • Reorganizations and rebuilds of indexes can increase the space a backup uses because those actions would move pages around on disk, and every changed page needs to be backed up. So it is probably your nightly maintenance job that is causing the problem. Have you tried moving to running that job every other day or weekly, or tuning it for higher thresholds for reorganization/rebuild?
    – Aaron Friel
    Jul 23, 2013 at 19:24
  • When we disabled the maintenance for a couple of nights we did notice the space usage on the backups being smaller. However, it does not touch every index in the database by any means.
    – user1441145
    Jul 23, 2013 at 19:46
  • Say for example we change the maintenance to run every other day or once a week. Wouldn't that produce the same amount of data changes ultimately while sacrificing any potential maintenance benefits (such as database speed, reduced CPU/IO usage). ? This would also create one large chunk for our off site DPM storage that has to go over our WAN, instead of incremental backup amounts every night.
    – user1441145
    Jul 23, 2013 at 19:50
  • Ah, express full backups should be quite large by default, they are full backups of your database, although SQL may "compress" them by leaving out unused pages. I thought you were talking about the network traffic required for an express full backup, which should be substantially less than the size of the database. Have you measured how much data is actually being transferred during express full backups?
    – Aaron Friel
    Jul 23, 2013 at 20:43
  • 1
    However, I do appreciate your input. You have steered me in the direction of changing the thresholds that the maintenance uses to reorganize or rebuild an index. Initially I had it set to the Books Online recommended values of 5% fragmentation for reorganizes and 20% for rebuilds. I changed those to 20% and 45%. My hope is to strike a balance between performance and the backup disk usage.
    – user1441145
    Jul 24, 2013 at 14:13

0

Reset to default

You must log in to answer this question.

Browse other questions tagged .