locked
LDF File Transaction Log size RRS feed

  • Question

  • Hello guys, I have an SQL server with around 1.7GB of DB size but  LDF file is 13GB in size.

    Is there a way to reduce the LDF file during full backup?

     

    I don't want to shrink the log file, other methods to manage the log file size would be good.

    Thanks you for your help.


    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    Wednesday, July 1, 2020 8:47 AM

Answers

  • Yes, just simple log backups, that releases the VLF = "Virtual Log Files" and the free space will be reused for the next coming transactions.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by cguan Saturday, July 4, 2020 3:18 AM
    Wednesday, July 1, 2020 9:18 AM

All replies

  • Is there a way to reduce the LDF file during full backup?

    A full backup is not enough, if the database is in full recovery mode, then you have to run frequently log backups as well, otherwise the log file size keeps growing.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 1, 2020 8:58 AM
  • Is there a way to reduce the LDF file during full backup?

    A full backup is not enough, if the database is in full recovery mode, then you have to run frequently log backups as well, otherwise the log file size keeps growing.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Hi Olaf, thanks for your reply. "Frequently run the log backup", means just do the backup without any other parameters like truncate or other possible parameters?  Cheers!

    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    Wednesday, July 1, 2020 9:16 AM
  • https://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

    https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thanks Uri, will look into that links. Cheers!

    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    Wednesday, July 1, 2020 9:17 AM
  • Yes, just simple log backups, that releases the VLF = "Virtual Log Files" and the free space will be reused for the next coming transactions.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by cguan Saturday, July 4, 2020 3:18 AM
    Wednesday, July 1, 2020 9:18 AM
  • If you care only full backup of your database, you can change your database recovery mode to SIMPLE to avoid this situation.
    Wednesday, July 1, 2020 2:22 PM
  • Hi cguan,
    If you want to reduce log file,please shrink it.

    you can refer to these articles:

    Manage the size of the transaction log file and  SQL Server Transaction Log Backup, Truncate and Shrink Operations .

     Best Regards,
     Echo

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 2, 2020 3:06 AM
  • Kindly check why log is not reuse.

    select log_reuse_wait_desc from databases
    Keep check if open transaction is there.


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Thursday, July 2, 2020 2:25 PM
  • Kindly check why log is not reuse.

    select log_reuse_wait_desc from databases
    Keep check if open transaction is there.


    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    Hi AV111, if let's say open transaction is not there? what would be the consequence? Thanks.

    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    Saturday, July 4, 2020 3:20 AM
  • If you care only full backup of your database, you can change your database recovery mode to SIMPLE to avoid this situation.
    Nope, i don't want Simple. Point recovery in time would be difficult.

    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    Saturday, July 4, 2020 3:21 AM
  • Hi cguan,
    If you want to reduce log file,please shrink it.

    you can refer to these articles:

    Manage the size of the transaction log file and  SQL Server Transaction Log Backup, Truncate and Shrink Operations .

     Best Regards,
     Echo

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Hi Echo, we used to do shrink but we don't want it since if there would be a disaster. Recovery will be tedious.


    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    Saturday, July 4, 2020 3:22 AM
  • If you care only full backup of your database, you can change your database recovery mode to SIMPLE to avoid this situation.

    Nope, i don't want Simple. Point recovery in time would be difficult.

    Every second counts..make use of it. Disclaimer: This posting is provided AS IS with no warranties or guarantees and confers no rights.

    If you want to point of time restore, you need to have a scheduled log backup in place and it will take care of your log file size in a manageable way. (no crazy growing log size any more). 

    By the way, have you tried to do a point of time restore at least once?

    Check your restore strategy to see whether it works. It is important.

    Saturday, July 4, 2020 4:58 AM