0

I have a database with full recovery model and without TLog backup.

The log file have grown to 160GB and I found out that it needs TLog to be periodically backed up or the recovery model switched to simple. Now that I have switched to simple recovery model, the .ldf file is still of the same size.

How can I reduce it?

0

Switching directly to simple recovery mode is something that should not be followed in the very first go depending upon how critical you're data is you are willing to keep.Shrinking the log file on first go without analyzing what caused the log file grow, will do no good as it will result in similar growth again:

Therefore, you might want to first investigate what is causing the log file to grow that big using below query to get an idea of what might be preventing your transaction log from being truncated.

SELECT name, log_reuse_wait_desc

FROM sys.databases

Now that I have switched to simple recovery model, the .ldf file is still of the same size.

Probable reason being a long running transaction. Again you have to check that using DBCC opentran() to find any long running transactions which might delay the duration of log truncation. Also, simple recovery model will do checkpoint depending upon the frequency determined by frequency interval or if log gets full by 70% and above.

Depending upon you'r RTO/RPO you might want to go and do a log backup first and if nothing works, shrinking the log file would be the last best resort for you to get out of the issue, but need to make sure recovery model has been changed back to FULL.

You might want to go and read below as great reads and would be helpful as well:

  1. Why Does the Transaction Log Keep Growing or Run Out of Space?

  2. How do checkpoints work and what gets logged

  3. 8 Steps to better Transaction Log throughput

0

You need to perform a shrink operation on the log file.

You can follow the instructions there.

PS: Don't forget to switch back to FULL Mode.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.