none
How do we know when to increase transaction log size?

    Question

  • Hi All,

    Question: How do we know when to increase transaction log size as opposed to directing SQL developers to break up inserts, deletes, and updates into smaller batches?

    Background: This is We created a database (simple recovery model) for a specific reporting project and started creating sprocs and tables.  Some of the developers write their T-SQL with little understanding of how it can affect the transaction log.  (Shocking, I know!)  The DBA wants some of these enormous transactions broken up into batches so we quit growing the transaction log to the point that it runs out of drive space.  I know it depends, but what does it depend on when deciding whether or not to grow the transaction log?

    Admittedly, as new ETL projects are added to this database, it will get busier and multiple processes are bound to be run in parallel.

    Thanks,
    Eric B.

    Thursday, September 13, 2018 12:14 AM

All replies

  • You don't increase the T-log, you just configure it efficiently and preallocate it in a way such that it rarely relies on auto-growth. 

    Deciding whether or not to let the log grow affects how a T-log is managed/maintained i.e. if you don't let it grow and if there are transactions needing to write to the log and the log cannot grow any further, the transactions would break. On the other hand, a poorly written long-running query can increase the T-log and since the active portion cannot be truncated, the T-log keeps growing to the point it runs beyond you would like.

    Hence, you need to evaluate the workload and configure the T-log efficiently. A rule of thumb is to keep the size of the log in such a way that creates less number of VLFs. A recommended VLF size is less than 1 GB with the number of VLFs < 1000.  So as an example, if you are creating a new database and would like to start your log file size with 64 GB initial size, try to increase the log file size in chunks instead of one time creation i.e. create the database with 8 GB as initial size of the log file and then alter its size seven times to add 8 GB more each time. This will make the size of each VLF to 512 MB and there will be 128 VLFs. The auto-growth algorithm has slightly changed starting SQL 2014. 

    Hope that helps.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    Thursday, September 13, 2018 1:03 AM
  • Please read this article

    8 Steps to better Transaction Log throughput

    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

    Thursday, September 13, 2018 5:35 AM
    Answerer
  • Hi Eric B,

    According to your description, my understanding is that you want to know when to enlarge transaction log file size of a database which is in Simple recovery model. If anything is misunderstood, please tell me.

    You could use sys.dm_db_log_space_usage to monitor log space use like the following screenshot. This DMV returns information about the amount of log space currently used, and indicates when the transaction log needs truncation.


    But because your database is in simple recovery mode, once the transaction is complete and the data has been written to the data file, the space that was used in the transaction log file is now re-usable by new transactions. So the transaction log will not grow forever as in the "Full" recovery model.

    In the simple recover model the log is automatically truncated when certain triggers are hit, such as a checkpoint, 70% of log full, etc. It is possible that you have created a very large long running transaction that causes the log to grow forever. So I recommend you to troubleshoot the full transaction log. For more information, please refer to the document from Troubleshoot a Full Transaction Log.

    Best Regards
    Puzzle
    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, September 13, 2018 7:13 AM