none
Backup Log files they appear they are not backing up or shrinking SCCM2016 SQL 2012 RRS feed

  • Question

  • I have several databases on an SQL 2012 server that I am backing up with SCCM 2016.  The log files say they have never been backed up ever (from sql manager). and they are growing out of control. 

    How do you backup SQL databases (including the logs) forceing a truncation and shrinking of SQL database transaction logs?

    Tuesday, October 24, 2017 3:53 PM

Answers

  • Hi Papacucku,

    When you have DB in full recovery mode SQL Server do not free up the log file after finish transaction, there is two way to solve this.
    You can start taking transaction log backups if you need them, depends on your company RPO, or you can use simple recovery model for that DB. 
    With simple recovery model you do not transaction log backups, but in case corruption, if you need to restore your DB you will only have full or differential backup (if you created one), no logs after. 

    Step by Step Log Shipping Configuration (SSMS, read only secondary)
    http://www.sqlservercentral.com/blogs/ganapathis-mssqllover/2017/03/02/step-by-step-logshipping-configuration/

    If you want to have DB in restoring state on secondary server step are similar.  Then just do restore with no recovery. And within log shipping configuration select no recovery mode instead standby mode. 
    • Edited by baraczof Wednesday, October 25, 2017 8:32 AM
    • Marked as answer by Papacucku Thursday, November 2, 2017 12:43 PM
    Wednesday, October 25, 2017 8:30 AM

All replies

  • Hi Papacucku,

    When you have DB in full recovery mode SQL Server do not free up the log file after finish transaction, there is two way to solve this.
    You can start taking transaction log backups if you need them, depends on your company RPO, or you can use simple recovery model for that DB. 
    With simple recovery model you do not transaction log backups, but in case corruption, if you need to restore your DB you will only have full or differential backup (if you created one), no logs after. 

    Step by Step Log Shipping Configuration (SSMS, read only secondary)
    http://www.sqlservercentral.com/blogs/ganapathis-mssqllover/2017/03/02/step-by-step-logshipping-configuration/

    If you want to have DB in restoring state on secondary server step are similar.  Then just do restore with no recovery. And within log shipping configuration select no recovery mode instead standby mode. 
    • Edited by baraczof Wednesday, October 25, 2017 8:32 AM
    • Marked as answer by Papacucku Thursday, November 2, 2017 12:43 PM
    Wednesday, October 25, 2017 8:30 AM
  • Thanks.

    using DPM with these settings should be fine in simple recovery mode correct?

    retaining for 15 days,

    synchronizing every 12 hours,

    and doing recovery points every night except Friday at 9 PM,

    and doing express full backup every Friday night

    This should allow a restore from DPM that is at most 12 hours out of date correct?

    Thursday, November 2, 2017 12:50 PM
  • Hi,

    This is an old article but just as an info. If you change the DB recovery model after configuration is finished, DPM will continue with using initial configuration.

    Source

    "When the recovery model of a protected database is changed from full or bulk-logged to simple, express full backups will continue to succeed, but incremental backups will fail."

    https://technet.microsoft.com/en-us/library/bb808756.aspx?f=255&MSPPError=-2147217396

    To change the recovery model of a protected database to the simple recovery model

    • Stop protection of the database, selecting the retain replica option.
    • Change the recovery model on the SQL Server database.
    • Add the database to a protection group.

    • Edited by baraczof Thursday, November 2, 2017 8:06 PM
    Thursday, November 2, 2017 8:05 PM