none
Reducing the log file size of my sql server

    Question

  • Hi ,

    I have a  production database which is in Simple recovery mode. I found that the log size often increasing and frequently getting the insufficient space in the log file.

    Please let me know how to reduce the log file size of a DB in a simple recovery mode.

     

    Regards,

    Mohammed

    Friday, July 08, 2011 4:53 AM

Answers

  • Hi Mohammed,

    Welcome to the MSFT forums. You need to add more details to get good quality responses to you questions.

    Please add information like your SQL Server @@version, what kind of transaction activity happens on the server, the size of the log file, file growth etc...

    In Simple recovery, the log file (VLFs) will truncate/cleared when you issue a CHECKPOINT. Also, when you run transactions, don't run them as one big monolithic transaction and instead try to break them up into smaller batches. Reason is if the transaction needs more space then log may try to grow big during the transaction if there is enough space available otherwise you may get the insufficient space available for the log file error.

    Also, SQL Server will try to keep some extra space in the log file to support the rollback of the transaction as well. You may want to keep that in mind as well.



    --Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/
    • Marked as answer by Stephanie Lv Saturday, July 16, 2011 3:45 AM
    Friday, July 08, 2011 5:15 AM
  • You reduce the file size using DBCC SHRINKFILE (details here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp). But is the size grows again to a larger size, then you just need that size and you better keep the file at that size all the time (or analyze what operations causes the file to grow to this size, like large batch operatioins, old open transactions, halted replication or mirroring etc).
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by Stephanie Lv Saturday, July 16, 2011 3:45 AM
    Friday, July 08, 2011 5:15 AM
  • Active part of log cannot get truncated in all the recovery modes. The following link will guide you to find what is causing the delay for the log truncation.

    http://msdn.microsoft.com/en-us/library/ms345414.aspx

    I Hope this helps!!

    • Marked as answer by Stephanie Lv Saturday, July 16, 2011 3:45 AM
    Friday, July 08, 2011 5:24 AM

All replies

  • Hi Mohammed,

    Welcome to the MSFT forums. You need to add more details to get good quality responses to you questions.

    Please add information like your SQL Server @@version, what kind of transaction activity happens on the server, the size of the log file, file growth etc...

    In Simple recovery, the log file (VLFs) will truncate/cleared when you issue a CHECKPOINT. Also, when you run transactions, don't run them as one big monolithic transaction and instead try to break them up into smaller batches. Reason is if the transaction needs more space then log may try to grow big during the transaction if there is enough space available otherwise you may get the insufficient space available for the log file error.

    Also, SQL Server will try to keep some extra space in the log file to support the rollback of the transaction as well. You may want to keep that in mind as well.



    --Sankar Reddy

    Blog: http://SankarReddy.com/
    Twitter: http://twitter.com/SankarReddy13/
    • Marked as answer by Stephanie Lv Saturday, July 16, 2011 3:45 AM
    Friday, July 08, 2011 5:15 AM
  • You reduce the file size using DBCC SHRINKFILE (details here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp). But is the size grows again to a larger size, then you just need that size and you better keep the file at that size all the time (or analyze what operations causes the file to grow to this size, like large batch operatioins, old open transactions, halted replication or mirroring etc).
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by Stephanie Lv Saturday, July 16, 2011 3:45 AM
    Friday, July 08, 2011 5:15 AM
  • Active part of log cannot get truncated in all the recovery modes. The following link will guide you to find what is causing the delay for the log truncation.

    http://msdn.microsoft.com/en-us/library/ms345414.aspx

    I Hope this helps!!

    • Marked as answer by Stephanie Lv Saturday, July 16, 2011 3:45 AM
    Friday, July 08, 2011 5:24 AM