We have 4 node replica, 2 nodes in synchronous mode, and 2 nodes in asynchronous mode. Log backups are happening every 15 minutes. The application just went live yesterday and one thing does not look right is that the data file used is only 800 mb where as the log file used is showing as 2.2 gb.

My point is aren't the log backups supposed to truncate the log file and make it clear for reuse? I have allocated 70gb to the log file and DBCC SQLPERF(logspace) shows as 3.5% used and it is only increasing in spite of taking log back ups every 15 Minutes.

There is no open transaction, the log_reuse_wait_desc shows as 'NOTHING' but the DBCC LOGINFO showing couple of VLFs with a status of 2. Even if I take log backup still stays at 2. Is this a normal behavior or after some time I will have to shrink the log files?

Using Ola Hallengren's script. My concern is if the space used is not cleared then at some point my log file will get full. I want to avoid shrinking at all costs.

I have 4 active VLFs and the size is 4gb . Does that mean that it will only clear once it reaches 4gb! As of now the data file used is 900mb whereas the log file used is around 2.5 gb in spite of taking log backup every 15 minutes. I understand that the log might grow due to application load but why it is not releasing the space after backup for reuse is worrying me as if this continues then very soon my log file will take up the whole space. The log file size is around 10 gb and the space used is 2.5 gb and that is increasing everyday in spite of taking regular log back ups.

The error logs are fine. There are no errors.

There are total of 43 VLFs and 4 showing active with a size of 4.4 Gb, Now will the active VLF will only be cleared or reuse when it becomes full but since the Log space used is around 2.5 gb will have to wait to see if that's the case.

  • So finally when the active VLSs got full it truncated the space and now it has grabbed some other vlf hence , and i guess this is how it works. It only truncates when it gets full – SQL_NoExpert Mar 25 at 10:19

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.