none
Truncate SQL logs ater DPM backup RRS feed

  • Question

  • Hi all,

    We have SQL 2014 AlwaysOn availability group in which we have multiple databases. The SQL AG is healthy, at least this is what I can see from the SQL management studio.

    We have as well DPM 2012 R2 box with latest updates that is protecting workloads including SQL databases in the alwayson AG.

    The issue we have is that even the protection group is configured to synchronize every 15 minutes (incremental backup), the SQL logs are not getting truncated at logs are heavily increasing.

    What can we do to solve this issue?

    Note: the sql recovery model configured on databases is Full.

    Friday, February 19, 2016 11:08 AM

Answers

  • It seems DPM do truncate SQL logs via incremental backup but cannot shrink the log file size and release unused space. To shrink the logs file and release unused space we have to do it manually via SQL management studio or using a script that I can schedule in the task scheduler.

    If yo agree guys on this I will close this thread.

    • Marked as answer by AhmadJY Wednesday, March 2, 2016 5:59 AM
    Sunday, February 21, 2016 12:07 PM
  • Hi Sir,

    First.:
    Have you changed the Recovery model from Simple to Full without removing and then adding the DB's to the DPM?
    DPM is not that happy about changing the Recovery model on SQL without re-adding the DB's.

    Second:
    Please make sure that the there is more space available on the drive that the backup requires.
    If the SQL Transaction Log is 30 GB then it's necessary you have a minimum of 30 GB of free space on the same drive before the DPM can truncate the logs.

    Please, let us know how it turns out.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by AhmadJY Wednesday, March 2, 2016 5:59 AM
    Wednesday, February 24, 2016 4:25 AM

All replies

  • Hi all, any help or advise would be much appreciated.
    Sunday, February 21, 2016 7:16 AM
  • There is a know issue with Sharepoing DBs , Not sure are you backing up SharePointDbs ?

     If you try to back up a SharePoint site that uses SQL Always On as a content database, SQL logs are not truncated as expected. this was fixed by Update Rollup 8

    https://support.microsoft.com/en-us/kb/3086084

    Sunday, February 21, 2016 7:37 AM
  • There is a know issue with Sharepoing DBs , Not sure are you backing up SharePointDbs ?

     If you try to back up a SharePoint site that uses SQL Always On as a content database, SQL logs are not truncated as expected. this was fixed by Update Rollup 8

    https://support.microsoft.com/en-us/kb/3086084

    Then thing is that we have databases for SharePoint and other applications; not only SharePoint and all suffering from same issue.

    Moreover, UR8 is installed already on DPM server.

    Sunday, February 21, 2016 7:49 AM
  • It seems DPM do truncate SQL logs via incremental backup but cannot shrink the log file size and release unused space. To shrink the logs file and release unused space we have to do it manually via SQL management studio or using a script that I can schedule in the task scheduler.

    If yo agree guys on this I will close this thread.

    • Marked as answer by AhmadJY Wednesday, March 2, 2016 5:59 AM
    Sunday, February 21, 2016 12:07 PM
  • it will not shrink the initial log file size   , Once you setup the backup (log) then further increasing of the log file will be be truncated.Initial log file size has to truncated manually.

    Sunday, February 21, 2016 12:44 PM
  • Hi Sir,

    First.:
    Have you changed the Recovery model from Simple to Full without removing and then adding the DB's to the DPM?
    DPM is not that happy about changing the Recovery model on SQL without re-adding the DB's.

    Second:
    Please make sure that the there is more space available on the drive that the backup requires.
    If the SQL Transaction Log is 30 GB then it's necessary you have a minimum of 30 GB of free space on the same drive before the DPM can truncate the logs.

    Please, let us know how it turns out.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by AhmadJY Wednesday, March 2, 2016 5:59 AM
    Wednesday, February 24, 2016 4:25 AM