none
Log Truncation counter value for database in Full Recovery mode

    Question

  • I have a database in Full Recovery mode (full backup is running every day, transaction log every hour).  

    SELECT* FROM sys.dm_os_performance_counters where counter_name = 'Log Truncations'


    returns 0 log truncation for this database. At the same time transaction log seems to be truncated : I check logspace periodically with 

    DBCC SQLPERF(LOGSPACE);

    Value of "Log Space Used" increases slowly during interval between transaction log backups, and then drops right after transaction log backup.  In addition, the server has been running without restarts for couple months, so it would have run out of space if truncation had never happened. 

    I checked value of 'Log Truncations' for databases in Simple Recovery and it changes over the time. 

    Could you please clarify if 'Log Truncations' counter supposed to show how many times log file was shrunk, not truncated ?  

    Thursday, October 17, 2013 10:25 PM

Answers

  • Hello ,

    Seems I found solution to your question why Log truncation Cntr_value is showing 0 even if proper transaction log backup is happeing which as per its design truncates logs.

    Now this value does not chages after transaction log hapens but changes when you shrink the logs after taking transaction log backup I have created a wiki article with detailed description please refer to it below.It was not possible to explain with limitation which we get here in our post

    http://social.technet.microsoft.com/wiki/contents/articles/20353.what-does-log-truncations-counter-value-in-dmv-sys-dm-os-performace-counter-signifies.aspx

    Please revert if this article provides information what you were looking for

    PS: Please ignore my previous comment

    HTH


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Friday, October 18, 2013 12:03 PM
    • Marked as answer by a1ex07 Friday, October 18, 2013 2:10 PM
    Friday, October 18, 2013 10:40 AM

All replies

  • Hello ,

    I assume this Log_truncation does not points to log truncation which occurs when backup log command is fired this counter is updated when someone runs

    backup log DB_name with truncate_only

    or

    Backup log db_name to disk='Null'

    This counter is showing null value because no log truncate command as above has been fired.I am not 100% sure but this is what I have observed.

    Let me do some quick test and I will be back

    Thanks for your cooperation


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, October 18, 2013 5:19 AM
  • Hello ,

    Seems I found solution to your question why Log truncation Cntr_value is showing 0 even if proper transaction log backup is happeing which as per its design truncates logs.

    Now this value does not chages after transaction log hapens but changes when you shrink the logs after taking transaction log backup I have created a wiki article with detailed description please refer to it below.It was not possible to explain with limitation which we get here in our post

    http://social.technet.microsoft.com/wiki/contents/articles/20353.what-does-log-truncations-counter-value-in-dmv-sys-dm-os-performace-counter-signifies.aspx

    Please revert if this article provides information what you were looking for

    PS: Please ignore my previous comment

    HTH


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Friday, October 18, 2013 12:03 PM
    • Marked as answer by a1ex07 Friday, October 18, 2013 2:10 PM
    Friday, October 18, 2013 10:40 AM
  • Thank you for your time.  Your explanations make sense; however, the fact the problem exists at all is very confusing. There are bunch of articles about transaction logs in MSDN which clearly separate concepts of log truncation and log file shrinking. So I expected (and I believe not  just me) the value of "Log Truncations" counter represents number of log truncations ,  not log shrinking ...
    Friday, October 18, 2013 2:09 PM
  • Thank you for your time.  Your explanations make sense; however, the fact the problem exists at all is very confusing. There are bunch of articles about transaction logs in MSDN which clearly separate concepts of log truncation and log file shrinking. So I expected (and I believe not  just me) the value of "Log Truncations" counter represents number of log truncations ,  not log shrinking ...

    Hello,

    I am not sure this is a problem but really you dont have to worry about this as this counter is not going to make any affect on your prod.I can unserstand your stituation but sometimes things might be different.

    Does below article gives some insight into what happens after you shrink log

    http://technet.microsoft.com/en-us/library/ms178037(v=sql.105).aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, October 18, 2013 3:13 PM
  • You are right, this counter can be safely ignored.  The real problem I had with that is a scheduled job that checks server healthiness (number of log truncations was among other things) and alerted if number is less than expected , so I ended up with mailbox full of useless warnings)

    Hello,

    I am not sure this is a problem but really you dont have to worry about this as this counter is not going to make any affect on your prod.I can unserstand your stituation but sometimes things might be different.

    Does below article gives some insight into what happens after you shrink log

    http://technet.microsoft.com/en-us/library/ms178037(v=sql.105).aspx


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Saturday, October 19, 2013 3:07 PM