SQL Server – How to Reduce LOG File After Switching to Simple Mode

disaster recoverysql server

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?

Best Answer

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:

Related Solutions

Sql-server – Shrinking the log file does not reduce size

Here is the answer to my own question.

Run the below query to get information about the log file's reuse wait:

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DBName'

I got the following output:

log_reuse_wait_desc
-------------------
REPLICATION

There were some replication-related objects remaining in the database, even after removing the replication.

To remove the replication from the database, sp_removedbreplication can be used. But it didn't work for us as replication was not active at the time and actually replication had been removed long before.

The solution was to import the database contents to another database using the import option of SQL Server.

How to Switch from Full to Simple Recovery in SQL Server

IMHO you MUST take transaction log backup before swithching to simple recovery it would help you in PIT recovery as well as its documented and recommended in BOL Article. It wont do any harm and you will have a failsafe.

Please also note its advisable to change recovery model when load on database is relatively less. Although you can change it in peak hour but changing recovery model does takes lock on database and if load is high you might face delay.

No transactions/changes would be lost, if you change from full recovery to simple. Changing recovery model would force a checkpoint which would commit transactions which can be committed. After changing recovery model to simple automatic checkpoint would truncate transaction logs(if no long running transaction is holding the logs)

Point 3 and 4 are totally not required, do you have specific reason to do it ?

Point 5 is necessary and you must at least take full backup daily of database in simple recovery. However backup of database should be according to RPO and RTO agreed. You can also take differential backup in simple recovery to reduce RTO.

Best Answer

Related Solutions

Sql-server – Shrinking the log file does not reduce size

How to Switch from Full to Simple Recovery in SQL Server

Related Question