none
SQL DB backup is successful but logs size are same and increasing RRS feed

  • Question

  • I am using MS SQL DB and we have many DBs there like vcenter and other DB. I have configured full backup for DB which is running successfully without any issue.

    1. But even backup is successful, SQL DB logs are not purged and same size?

    I want to shrink/reduce the SQL DB log file (ldf) size. I have changed recovery model from simple to FULL but no luck


    Arif

    Monday, June 22, 2020 6:50 AM

Answers

  • Hi Aayoosh,

    That is confusion, some body say that logs will be purged in full backup and somebody says in Incremental(synchronization) . I am using Simple recovery model so this allows only Full express backup.

    Are you sure if I take incremental backup then SQL logs file size will be shrunk?


    Arif

    Hello Arif,

    SQL log will not be purged if you take express full backup from DPM. You need to set a sync frequency in DPM PG in order to take a log backup. once log backup is successful, sql will make those T-logs for truncation.

    Since you are using simple recovery model, log will not purge and yes you wont get an option to set a sync time in the Protection group. Follow below steps:

    Stop protection for the SQLDBs from Protection group.

    Change the recovery model to Full.

    Enumerate the SQL server by clicking REFRESH and Re-protect the DBs and give sync time as per your requirement and you should be good.

    NOTE: SQL logs will be truncated after a sync job for sure however the LDF file will not be shrunk. The log inside the LDF file will be cleared but need to shrink the LDF file manually. 

    Friday, June 26, 2020 8:37 AM

All replies

  • Hi,

    If you have changed the recovery model from Simple to Full while the protected databases were already in a protection group, you need to remove the databases from the existing protection group and re-add them to fully get the recovery model changes applied.

    Best regards,
    Leon


    Blog: https://thesystemcenterblog.com LinkedIn:

    Monday, June 22, 2020 7:42 AM
  • Hi Azahri,

    Have you enabled a sync job in the protection group? Or you are only taking Expressfull backup for the SQL DBs ?

    Considering all the configuration are fine but you still need to make sure you have at least one SYNC job configured in PG so that SQL Logs are purged. SQL will not purge the T-Logs if you are only taking a full backup from DPM.

    During Sync job, DPM takes the backup of T-logs only and then those logs are marked for truncation from after successful backup. And in Expressfull backup (Full backup) it will only take the differences/delta of MDF and LDF files from previous successful backup and no LOGS will be purged/Truncated in this job. So make sure SYNC job is configured in the PG for purging SQL T-logs.


    Monday, June 22, 2020 8:18 PM
  • If you have changed recovery model from SIMPLE to FULL. As Leon stated above, you need to STOP protection of impacted databases on DPM protection group and re-add them so DPM can detect that you have changed recovery model. 

    On DPM -

    1- Right click on Datbase datasource, right click > Stop protection (Make sure you keep 'Delete replica on disk' Unchecked)

    2 - Modify protection group by right click > On very first page of Wizard, select database server and click on Refresh under the bottom of page, once refresh completed, you can re-add those datasources again. 

    3 - Make sure you have set Synchronization frequency set to some minutes or hours (depends on log growth rate you can increase and decrease this) and this is not to be set 'Before recovery point'

    Thanks

    Vijay 



    • Edited by V Jay Rana Friday, June 26, 2020 5:07 AM
    Friday, June 26, 2020 5:04 AM
  • Yes, I had modified the protection groups and still no luck. sql log file size remain same and continuously growing 

    Arif

    Friday, June 26, 2020 7:30 AM
  • Hi Aayoosh,

    That is confusion, some body say that logs will be purged in full backup and somebody says in Incremental(synchronization) . I am using Simple recovery model so this allows only Full express backup.

    Are you sure if I take incremental backup then SQL logs file size will be shrunk?


    Arif

    Friday, June 26, 2020 7:33 AM
  • For log backup and purge, yes - always use recovery mode as FULL. Also, to make DPM aware that you have changed recovery model - Use refresh option while re-adding them back into DPM. 

    Once incremental backup job completes on DPM, you can go to Database server and check last log backup and log size there. 

    • Edited by V Jay Rana Friday, June 26, 2020 8:04 AM
    Friday, June 26, 2020 7:54 AM
  • Hi Aayoosh,

    That is confusion, some body say that logs will be purged in full backup and somebody says in Incremental(synchronization) . I am using Simple recovery model so this allows only Full express backup.

    Are you sure if I take incremental backup then SQL logs file size will be shrunk?


    Arif

    Hello Arif,

    SQL log will not be purged if you take express full backup from DPM. You need to set a sync frequency in DPM PG in order to take a log backup. once log backup is successful, sql will make those T-logs for truncation.

    Since you are using simple recovery model, log will not purge and yes you wont get an option to set a sync time in the Protection group. Follow below steps:

    Stop protection for the SQLDBs from Protection group.

    Change the recovery model to Full.

    Enumerate the SQL server by clicking REFRESH and Re-protect the DBs and give sync time as per your requirement and you should be good.

    NOTE: SQL logs will be truncated after a sync job for sure however the LDF file will not be shrunk. The log inside the LDF file will be cleared but need to shrink the LDF file manually. 

    Friday, June 26, 2020 8:37 AM
  • Hi Aayoosh,

    That is confusion, some body say that logs will be purged in full backup and somebody says in Incremental(synchronization) . I am using Simple recovery model so this allows only Full express backup.

    Are you sure if I take incremental backup then SQL logs file size will be shrunk?


    Arif

    As I mentioned earlier, you still need to have the Full recovery model, no log truncating will be done otherwise, follow the steps as I said in my initial post. And make sure the synchronization runs before the Express Full backup.

    Blog: https://thesystemcenterblog.com LinkedIn:

    Friday, June 26, 2020 9:14 AM