none
DPM 2010 - SQL Log files not truncating RRS feed

  • Question

  • Hi,

    I am using DPM 2010 that is protecting SQL 2008 and 2005 servers. DPM does not truncate the log files of my databases. This causes the log files to grow until all the disk space on my servers are used.

    All my databases are in 'Full Recovery' model. The DPM backup job runs syncronizations ever 4 hours and a express full backup at 8:00pm daily.

    I have read many other posts state that the 'Syncronization' option in the protection group indicates 'Incremental' backups, and this is what will cause SQL log files to truncate. Does not work on any of my databases.

    What can I do to get this to work?

     

    Monday, March 7, 2011 2:14 PM

Answers

  • DPM uses the sync to do a TSQL command that will pull the new bits for the transaction logs and then deals with the log truncation.  Since your databases are set to full, it should be happening.  To be sure, are you having DPM do a "sync before recovery point" and doing it every four hours?  If so, this will actually not truncate the logs.  You would need to set up specific syncronization intervals and then have it also do the recovery points (express full).


    Thanks, Chris Butcher - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Tuesday, March 8, 2011 3:42 PM
    Moderator
  • DPM frees up the log space so that SQL can reuse it and hence prevent further growth. But it doesn't truncate the file size by itself (if it has grown already). You'll need to use DBCC SHRINKFILE (or something equivalent) to reduce the file size.

    If you still find your logs growing significantly and DPM not able to prevent the growth, consider increasing your incremental frequency to less than 4 hours.

    Thanks

    Deepan

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, March 8, 2011 7:18 AM
    Moderator

All replies

  • Anyone? Bump!
    Tuesday, March 8, 2011 4:42 AM
  • DPM frees up the log space so that SQL can reuse it and hence prevent further growth. But it doesn't truncate the file size by itself (if it has grown already). You'll need to use DBCC SHRINKFILE (or something equivalent) to reduce the file size.

    If you still find your logs growing significantly and DPM not able to prevent the growth, consider increasing your incremental frequency to less than 4 hours.

    Thanks

    Deepan

    This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, March 8, 2011 7:18 AM
    Moderator
  • DPM uses the sync to do a TSQL command that will pull the new bits for the transaction logs and then deals with the log truncation.  Since your databases are set to full, it should be happening.  To be sure, are you having DPM do a "sync before recovery point" and doing it every four hours?  If so, this will actually not truncate the logs.  You would need to set up specific syncronization intervals and then have it also do the recovery points (express full).


    Thanks, Chris Butcher - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Tuesday, March 8, 2011 3:42 PM
    Moderator
  • Hi, I'm, marking this post as answered. Please respond back if the issus still persist.

    Thanks

    Deepan

    This posting is provided "AS IS" with no warranties, and confers no rights.

     

    Sunday, March 13, 2011 10:45 AM
    Moderator
  • Well, we have the same problem across all of our SQL databases.  DPM is set to 15 minute syncs, with File Recovery Points set to twice per day, every day, AND Express Full Backup set to once per day. 

    In Studio Management Studio on the SQL server, says the databases are backing up, but the transactional  logs are saying never; and one of our logs is sitting at almost 60GB.

    We tried running the dbcc command to shrinkfile, as we found documented elsewhere, to no avail....

    So right now we are performing a manual backup of the transactional logs from within the Studio, however, this seems counter-intuitive to the way DPM should be working..

    Anybody have any hints on this ??

    Friday, March 18, 2011 8:03 PM
  • At this point, everythign is set the way it should be.  You say this is happening for all of your databases.  Do you have multiple instances and or servers that are all exhibiting the behavior?  It also sounds like you are protecting these in combination with some files in the same protection group.  That should have no affect on this, but is it possible to create a new instance for testing and put it in a different protection group?

    Outside of this, the scope of this really appears to be deep enough that you may want to open an incident with our support team if you can't get any further answers on here. 


    Thanks, Chris Butcher - MSFT This posting is provided "AS IS" with no warranties, and confers no rights
    Wednesday, March 23, 2011 2:24 PM
    Moderator
  • Hello Gentlemen,

    I'm adding to this closed thread because we are having the exact same issues with DPM 2010 not truncating any of our DB logs during synchronization or full backup.  OUr settings are set as such:

    Sync's ever 4 hours
    Recovery points Based on synchronication frequency (every 4 hours)
    Express Full backup once a day at 8PM

    All backups appear to be working fine, but SQL server reports that the transaction log files are full (no available free space) indicating that truncating is not occurring during any of the backups (Full or incremental).    We have had to manually run a backup in SQL in order to clear out the logs (truncate them). 

    Was there a definate cause determined from this thread as to what is causing DPM not to truncate the files?   SQL database setting is set to FULL, so that shouldn't be the problem.

    Anyone have any other suggestions?

    Thursday, August 25, 2011 3:05 PM
  • Hi All,

     

    We seem to have the same issue as described above with DPM2010. Where the log files are getting full. Has anyone found a fix for this?

    Thursday, September 22, 2011 11:04 PM
  • Hi All,

    We seem to have the same issue as described above with DPM2010. Where the log files are getting full. Has anyone found a fix for this?

    What has not been addressed here is the recovery model of the SQL Server databases. If Simple Recovery is used, then the log is truncated automatically by the internal Checkpoint thread. You cannot make transaction log backups of a Simple Recovery database.

    In Full Recovery model, only a log backup performs the truncation. If no log backups are being made of the database, it will grow until it consume all available disk space.

    Here is a good starting point for learning about SQL Server Recovery Models: http://msdn.microsoft.com/en-us/library/ms189275.aspx

    Monday, May 14, 2012 4:34 PM
  • Are your recovery points 0kb or do they show a size? Just wondering as we had a similar issue where our SQL logs were not truncating and when I checked the size of the recovery point was 0kb. I found a powershell online called Get-DPMRecoveryPointReport.ps1 and although the protection group was set the same as yours it was actually backing up 20 out of 100 dbs every 15 minutes creating 10k recovery points over 6 days.
    Monday, May 6, 2013 8:26 PM
  • No, it is not. 

    We have been roving the web for answers of this problem, and tried all of them (including the very obvious suggestions here - we know about the differences in truncation and shrinking, and we did not change the default settings in the protection group) - to no avail. 

    We have an interesting case where we have around 20 databases from one SQL Server (3 instances) in one protection group (translation: they all use the same DPM settings). Nearly all databases use the FULL RECOVERY model.

    For *two* of the databases, it works - during syncs (every 15 minutes), the Log file is backed up, a restore point is created (can be observed in the details pane of the DPM, also in the Restore UI of DPM). For all the other databases, it doesn't. Only during the Express Full Backup at night, a restore point is created. At the 15-minutes interval, *nothing* is happening, no Log backup, therefore no truncation, and one of the DBs has now grown the log file to 100 GB (that's how we noticed it).

    Just as a pointer to your own people - I really wonder what these Microsoft engineers/developers are doing sometimes :-( - when we modify the protection group (just to see all its settings), at the very end of the wizard, it says: 

    Recovery Points (config 1)  ... 15 Minutes
    Recovery Points (config 2)  ... 1 day

    or similar. So it sort of indicates (???) that two different configurations (???) are created in one protection group, even though there is nothing of this offered in the UI, no other message about it, etc... but that seems to happen behind the scenes: for some of the DBs, there are 15 minute recovery points (including transaction log backup and truncation), and for most of the DBs, it is one day.

    Just a hint for your people... and tell them to be more careful in the development of their user interfaces :-)

    PS: I should add that of course we tried to identify differences between these databases. We couldn't find any - they are all FULL RECOVERY model, many of them share the same SQL compatibility version, we really went through the property sheet...


    • Edited by PeterResele Thursday, September 1, 2016 2:58 PM
    Thursday, September 1, 2016 2:56 PM