DPM 2012 backup of SQL2012 does not seem to reduce LDF size

OK, I am not sure why, but the LDF file sizes are literally 181GB while the DBF is 32GB. I have DPM2012 backuping up the server, the databases, etc., but this file seems huge in comparison to me. I am not sure what to do to reduce the file size and keep it from consuming storage and it appers it is affecting the speed of the system (I think).

Thanks for any help.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Andrej PirmanCommented:
SQL will truncate the logs files after each DPM Synchronization (incremental backup). However truncation is not the same as shrinking. Once a log file grows, you will need to shrink it manually.  If the time between synchronizations is set for something like 12 or 24 hours, then the log file has already grown and you will need to shrink it manually, then reduce the synchronization period to keep it to a resonable size.  If the synchronization selection is ‘Just before a recovery point’ then incremental backups won’t get scheduled. This option is a way of telling DPM, that the user is interested only in express full backups and not incremental backups.

The Shrink process needs to be done manually or through a SQL Maintenance job

You can also create a post-backup script to deal with the shrink process.

More information about Pre-Backup/Post-Backup script can be found in the link below



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ber8630Author Commented:
OK, so I don't mess things up. How I manually shrink then?
Randy Knight, MCMPrincipal ConsultantCommented:
DBCC SHRINKFILE.  Note that you should not be regularly shrinking files, just as a maintenance activity.  Your log file should be right sized and left alone.  The best way to think of the log file is almost like a disk drive in that it is a "container" for log records.  The % of the file that is used can change but the file size should not.  

You didn't say but is the database in SIMPLE or FULL recovery model?  If you are not doing transaction log backups (incremental in DPM terminology) then you should be in SIMPLE.  However this will eliminate your ability to do point in time restores.

If you post a bit more about your backup strategy and RPO/RTO goals we can help you design the right backup system.
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ber8630Author Commented:
Thanks for the being patient with my delayed response. Too many irons in the fire.

So, I guess DBCC SHRINKFILE is part of some overall script or syntax I am not aware of, nor am I a DBA. Thus, my ignorance is quite clear now.

I am not exactly sure which mode it is, nor do I remember DPM asking me when I added into the protection groups. I would have probably chosen to do Full recovery.

It is interesting to me (we just switch to DPM recently) that DPM appears to be perfectly happy backing up machine in the various groups, However, the Bare Metal and System states show to be "inconsistent" more often than not.

Basically I am trying to make sure the machine and the databases are backed up every 8 hours.
Randy Knight, MCMPrincipal ConsultantCommented:
The recovery model is set in the database, not by DPM.  Do you have a DBA?  If so you should be working with them on this to ensure everyone is on the same page.  If not, post  back here and we can help you.
ber8630Author Commented:
Sorry for the extremely slow response.

I do not have a DBA here.

Sorry on the Model question, I guess I misunderstood the question. I was able to get the log file to shrink down by switching to simple, conducting a shrink on the database, and then switched it back to Full. I also set the interval much lower to try and keep the file in hand.

I have not been able to get back around, but I know that the database backups still do not seem to be working the way I expected. So it may mean that I need to pose the question in an area for DPM as opposed to SQL, but I will let you advise me on that.

ber8630Author Commented:
Sorry didn't realize I left this item open and for so long.

Thanks for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.