none
DPM2010 - log file not truncating RRS feed

  • Question

  • Hello,

    I currently have the issue with one of our SQL server that the log files are not truncating.

    After 1 week the filesize (ldf) will be 10GB and I connect free any space in the ldf file.

    The backups run without issue and the backup level is set to "Full".

    Backup on the DPM2010 is configured as followed:

    • Synchronization: 15 Minutes
    • FullBackup: 20:00

    If I look at the log_reuse_wait_desc, I get "LOG_BACKUP".

    Meaning it should be waiting for the next full backup.

    Does someone have any idea where the problem could be?

    Regards

    Paul

    Wednesday, September 3, 2014 1:44 PM

All replies

  • Hi,

    If i understand you correct, you would like to have the LDF File resized or shrunk?

    Thats not be done by DPM. DPM will backup the LogFile but not resize it.


    Seidl Michael | http://www.techguy.at | twitter.com/techguyat | facebook.com/techguyat

    Thursday, September 4, 2014 6:02 AM
  • Hello,

    thanks for the reply.

    I do not want to shrink the ldf file I only use it as an example, to show that no logs are truncating.

    We already had the problem that the log did run full in the past and I do not want that happening again.

    Regards,

    Thursday, September 4, 2014 8:53 AM
  • Hi,

    The following may assist with you issue. As stated already DPM does not perform the process of truncating logs this is performed via SQL. SQL logs will be truncated in the following scenario

    -Under the simple recovery model, after a checkpoint.

    -Under the full recovery model or bulk-logged recovery model, after a log
    backup, if a checkpoint has occurred since the previous backup.

    The way log truncation works is SQL marks transactions internally to the log file so the records can be reused. SQL does not resize the log file after a backup. So a potential remedy may be for you to take more frequent incremental backups so SQL can mark the transactions committed more frequently, and thus prevent log growth. The size that the log file gets is determined by: 

    A) The amount of activity of the SQL DB.

    B) The amount of time before it gets backed up.

    So as an example, let’s say a SQL log starts off at 1GB, and over a typical work day, the log grows to 20GB, and your 1st backup was at the end of the day, the log would remain 20GB forever.  If you took the 1st backup after an hour and backups every hour thereafter, then the log file would probably not grow larger than about 2.5GB  (20gb/8hr work day = 2.5GB per hour)

    Additional information:

    Transaction Log Truncation-SQL 2008 R2

    http://technet.microsoft.com/en-us/library/ms189085(v=SQL.105).aspx

    ******************

    Please remember to click “Mark as Answer” on
    the post that helps you, and to click “Unmark as Answer” if a marked post does
    not actually answer your question. This can be beneficial to other community
    members reading the thread. Regards, Dwayne Jackson II. [MSFT] This posting is
    provided "AS IS" with no warranties, and confers no rights."
    <o:p></o:p>





    Thursday, September 4, 2014 7:51 PM
  • Hello,

    thanks for the reply.I am aware that the file size will not shrink in case of a backup, but I can check IF it would shrink using the shrink file option and there I see that there is no space to free up. I checked this directly after a full backup using DPM.

    So after a backup the file size and the logs in use remain at 20GB and cannot be released.Ic hope I could clear up my problem and hope someone can provide a solution.

    Regards
    Paul

    Friday, September 5, 2014 6:13 AM
  • Hi,

    dont think here of a SCDPM Problem.

    After your DPM Job has run, please open your SQL Managament Studio, do a right click on the protected DB and see properties. Here you can check you last bavluo time, should bee the same for DB and Log.

    is this correct?


    Seidl Michael | http://www.techguy.at | twitter.com/techguyat | facebook.com/techguyat

    Tuesday, September 9, 2014 4:56 AM
  • Hello,

    I just checked the properties and the log backup and DB backup time are not the same:

    What could be the problem?

    Regards

    Paul

    Tuesday, September 9, 2014 5:51 AM
  • Log will not be backed up

    Please check following:

    - DB Mode is Full

    - Run Consistency Check on DPM for this DB

    - Check for any Errors


    Seidl Michael | http://www.techguy.at | twitter.com/techguyat | facebook.com/techguyat

    Tuesday, September 9, 2014 6:03 AM
  • Hello,

    thanks for the reply:

    DB Recovery Mode is: Full

    Consistency Check completes successfully

    There are no errors in DPM or the SQL log:

    Any idea how to solve this?

    Tuesday, September 9, 2014 12:32 PM
  • Hi,

    For the database in question has the recovery model ever been changed since DPM protection was initial configured?

    **************

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Dwayne Jackson II. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights."

    Thursday, September 11, 2014 1:56 PM
  • Hello,

    it could be possible that the recovery model has been changed.

    I just not took the problematic DB and wanted to add it to a new protection group and noticed that I do not have the option to select a synchronisation time:

    Any idea why I cannot select a synchronisation time?

    Regards

    Paul

    Friday, September 12, 2014 5:25 AM
  • Hi,

    Thanks for the additional information.

    So my understanding is that the problem DB from SQL shows we are in Full recovery model. However DPM per your last screen shoot we cannot run incremental backups against the problem DB. If SQL Database is set to simple, DPM won’t run incremental backups against it. 

     

    DPM detects the recovery model that the database is configured to use. If the database recovery model has changed after the fact DPM protection will continue as it was initial configured. As you suspect that the recovery model may have changed at some point. To fix I would suggest stopping the protection for the problem DB retaining the data. Then re-protecting the data. This will allow DPM to detect the current model of the problem DB (which is full). Right now DPM thinks the DB is in simple. The below may be useful as well.

     

    http://technet.microsoft.com/en-us/library/ff399658.aspx

    **************

    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread. Regards, Dwayne Jackson II. [MSFT] This posting is provided "AS IS" with no warranties, and confers no rights."

     

    Friday, September 12, 2014 10:48 AM
  • Hello,

    thanks for the reply, but I already did what you suggested:

    1. Took DB out of the protection group
    2. Checked the recovery model (which already is Full, but just in case I switched to Simple and back to Full)
    3. Tried to add the DB to a new protection group and there I do not ahve the option of setting the sync-time

    Any idea how I can reset the possibly cached data the DPM could have

    Friday, September 12, 2014 10:56 AM
  • Hi,

    i am not sure, bute maybe this will help.

    Create the Following Reg Key "CacheInquiryResults" under HKLM\SOFTWARE\Microsoft Data Protection Manager\Configuration

    here you can see some pics: http://www.techguy.at/auswahl-der-zu-sichernden-datasources-beschleunigen/

    Restart your DPM Console, now you should see a "clear chache" on the Select Memebers of Protectiongroup Page.

    try it.


    Seidl Michael | http://www.techguy.at | twitter.com/techguyat | facebook.com/techguyat

    Monday, September 15, 2014 5:08 PM
  • Hello,

    thanks for the link, I cleared the cache, but the problem still persists.

    Any idea, what else I could try?

    Regards

    Tuesday, September 16, 2014 8:39 AM
  • Plase run a full recoverypoint and post the DPMCUrr.log

    Seidl Michael | http://www.techguy.at | twitter.com/techguyat | facebook.com/techguyat

    Thursday, October 2, 2014 9:43 AM