Tuesday, November 24, 2015

Changing from FULL to SIMPLE recovery model reduces the log file size?

A concern related to large log file or ldf file still appears in many forums, it is very common to hear/see a questions like my database log file has grown for GBs, why and how can I reduce it, can I just reduce it by changing the recovery model from FULL to SIMPLE.

First thing you need to understand is, how log file truncation works with FULL and SIMPLE recovery model. FULL Recovery model does not truncate the log file until a transaction log backup is performed. But SIMPLE recovery model automatically truncates the log making the log vacant for next transactions rather expanding or growing the file. There are few exceptions in FULL, read my article Log is getting automatically truncated with FULL Recovery Model  for more info.

Now if your database in FULL recovery model and you see a large log file, can you reduce it by just changing the recovery model. Recommendation is not change as you will lose all you transaction records in log file but if you do not want them you can change. However it does NOT reduce the size of the log file. Once changed, it will truncate the file but will not release the free space unless you perform the SHRINKFILE operation for the log file. So the conclusion is, changing the recovery model from FULL to SIMPLE does not reduce the size but truncates. Shrinking file is required for reducing the file size.

Here is a small test for verification.

This code creates a database and a table. The default recovery model is FULL for this database. It backs up the database once. It makes sure that the backup chain is established.

CREATE DATABASE TestDatabase;
GO

USE TestDatabase;
GO

CREATE TABLE dbo.TestTable
(
 Id int identity(1,1) primary key
 , Value1 char(4000) not null
 , Value2 char(4000) not null
);

-- take a backup
BACKUP DATABASE [TestDatabase] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\TestDatabase.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'TestDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Here is the current file size.


This code adds records growing both data and log files.

INSERT INTO dbo.TestTable
 (Value1, Value2) VALUES (REPLICATE('a', 4000), REPLICATE('a', 4000));
GO 1000000

As you see, log file has been grown and it is not getting either truncated or shrunk automatically.


Let's change the recovery model and see.

USE master;
GO
ALTER DATABASE TestDatabase SET RECOVERY SIMPLE WITH NO_WAIT;
GO

If you check the size again, you will see the same as it does not release the free space back to OS. Let's shrink the file and see.

USE TestDatabase;
GO
DBCC SHRINKFILE (TestDatabase_log, TRUNCATEONLY);

All as we wanted.


No comments:

Post a Comment