2

I have a database in Full Recovery mode. Full backup is executed every day, transaction log backup every hour.

However,

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

shows 0 log truncations for that DB.

At the same time,

DBCC SQLPERF(LOGSPACE);

shows different "Log Space Used" after each execution (it slowly grows during interval between log backups, and then drops; Log Size doesn't change), so it seems to me that log truncation works fine; otherwise, I'd run out of space long time ago.

I suspect counter is not updated for any reason, but I can't figure out why. Also, I may be well wrong, and the problem is more serious than it looks .

Any help is greatly appreciated.

  • I'm not exactly sure what this tracks. On a testing database I was able to get it to increase by shrinking the log, and also doing a checkpoint sometimes, but not by doing a log backup. – Jon Seigel Oct 16 '13 at 23:45
  • There is a difference between truncating the log file i.e. shrinking it and the truncation of the Virtual Log Files in SQL server. I suspect that the truncation in the performance counters refer to the physical file and not the virtual log files. The log file back up truncates the VLF's that are inactive. Probably two different things – Namphibian Oct 17 '13 at 4:56
  • @Namphibian: That's the confusing part. I was able to increment the counter by doing a CHECKPOINT by itself in some cases, without shrinking the log file. – Jon Seigel Oct 17 '13 at 13:34
  • @Jon Seigel : did you try checkpoint for database in Full Recovery Mode? I'm really confused because I see numbers in 'Log truncations' for system databases which are in Simple mode. Also, in my understanding the engine issues checkpoint with "Recovery Interval" frequency (assuming there are enough transactions). It appears to me that Namphibian may be right and Log truncations don't increase "Long truncations" counter which is even more confusing since docs clearly distinguish between truncation(logical operation) and shrinking (physical size)... – a1ex07 Oct 17 '13 at 14:37
  • Yes, it did increase by issuing a CHECKPOINT (or at least, within the span of time between checking the DMV), but only sometimes. It was more consistent in SIMPLE. The counter also tended to increase by 2 most of the time in SIMPLE. The behaviour is baffling to me. – Jon Seigel Oct 17 '13 at 16:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.