Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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:
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.
