What does 'Log Truncations' Counter Value in DMV sys.dm_os_performace_counters Signify?

What does 'Log Truncations' Counter Value in DMV sys.dm_os_performace_counters Signify?



Background


As per MS, or if you refer to perfmon description for this counter 'Log Truncations',  it means 'Total number of log truncations for this database'. The information seems wrong or  to be more accurate, the information is incomplete. What actually this value depicts is something different which I will try to show in this post.

I got an interesting question from an OP (http://social.msdn.microsoft.com/Forums/sqlserver/en-US/7714c30a-35e8-4fef-999b-ed0aa9b1d114/log-truncation-counter-value-for-database-in-full-recovery-mode?forum=sqldatabaseengine) where he was using below query to find out amount of log truncation occurring for each database. 

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

But strangely this query was returning 0 in  cntr_value column for some databases, please see suggested reading section at the bottom of this article to get detailed information what cntr_value column means. The database in picture was configured for full and transaction log backup so according to him value must not be zero as transaction log marks VLF as truncated after log backup when it is not holding any active transaction (not in case of long running transaction). The fact that log truncation was occurring can be taken from point (which OP posted) that his log size was very much under control.

Solution to the question why 'Log truncations' cntr_value is showing 0 even if proper transaction log backup (and truncation ) is happening, which as per its design truncates logs, lies in fact that  log was not manually shrunk to remove free space and provide it to Disk. 0  indicated log backup happening but value changed to 1,2 3 or so on only when that log was shrunk so actually 'log truncations ' counter does not mean log truncation which occurs when log backup is taken, it signifies when log is shrunk and no of VLF that  are made free, this is as far as I think and will also try to show in this article.

Performing the test


So to actually find out what this zero value indicated  I did little test on my local machine.

Below is snapshot of AdventureWorks2008R2 DB which had log backup taken twice on 14th Oct 2013 .



As per screenshot you can see AdventureWorks2008 r2 database has 2 trn log backup on 14th Oct 2013.
Now when I run below query for in my AdventureWorks2008 r2  database


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


Below is result which I got




As you can see from above result even after 2 log backups CNTR_VALUE is zero. Which a general guess would be 2But transaction log backup is not actually what updates this value this cntr_value is updated when log is manually shrunk, not when transaction log backup happens, which truncate logs.

I ran below command to shrink logs


dbcc shrinkfile (AdventureWorks2008R2_Log ,200)


Now I ran below command again


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


This is what output was




Now you see after running DBCC SHRINKFILE command for log file cntr_value changed from 0 to 4.

From BOL (http://technet.microsoft.com/en-us/library/ms189085(v=sql.105).aspx)
When the checkpoint is performed, the inactive portion of the transaction log is marked as reusableThereafter, the inactive portion can be freed by log truncation ( this is correct definition if current article is concerned) Truncation frees the inactive virtual log files for reuse. Eventually, when a new record is written to a freed virtual log, that virtual log file becomes active again.

So CNTR_VALUE is not how many times log is truncated but with above example it seems this value corresponds to no of VLF(Virtual log files are the unit of space that can be reused) that were freed and given back to OS of course they were inactive not holding any active transaction. Shrinking the transaction log reduces its physical size by removing one or more inactive virtual log files and this is what the number in cntr_value reflects.

Summary

Why this peculiar behavior is occurring can be attributed to the fact that log truncation happens for VLF (virtual log files not physical)  when transaction log backup happens. When transaction log backup starts it looks for VLF with inactive transactions and marks them reusable. What actually frees these VLF's and gives space back to the disk is Log file shrinking and this is what updating the counter.

I am really puzzled by this strange behavior. I have always known that log file truncates the log, but it seems now I have to use more correct word, makes it reusable. I tried to derive inference on the behavior I see. I would love Microsoft engineers to comment on this whether this is strange behavior or it is correct, but we were using wrong word reusable instead of truncate.
 
NOTE: I did not perform this test for database in simple recovery mode. I would like to take it further, would like to see if checkpoint in simple recovery updates this counter or not.

Suggested Reading

For cntr_value: http://technet.microsoft.com/en-us/library/ms187743.aspx
For Log shrinking: http://technet.microsoft.com/en-us/library/ms178037(v=sql.105).aspx
How transaction log works: http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx
Log truncation: http://technet.microsoft.com/en-us/library/ms189085(v=sql.105).aspx
Trn log Physical architecture: http://technet.microsoft.com/en-us/library/ms179355(v=sql.105).aspx
Trn log Logical architecture: http://technet.microsoft.com/en-us/library/ms180892(v=sql.105).aspx

See Also

This Article Participated in Technet Guru Competition October 2013 and won Silver Medal.


Sort by: Published Date | Most Recent | Most Useful
Comments
Page 1 of 1 (3 items)