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.

I changed the Recovery Model before BULK operation, is it a good practice?

I have already written a post on bulk operations, indicating the benefits we get by changing the recovery model before performing bulk operations. I got a couple offline questions after my SQL Server Stored Procedure session at SS SLUG and one question was this. I was talking about how plan cache getting flushed away and I mentioned that changing recovery model causes to get the plan cache flushed. Now the question is, should we change the recovery model for our bulk operation?

First of all, let's see whether changing the Recovery Model causes to get the plan cached removed. Here is the code I wrote for testing.

I am executing two SELECT statements, one against AdventureWorks2014 and another with AdventureWorksDW2014. The last statement checks plan cache.

-- EXECUTE STATEMENT SEPARATELY

-- Cleaning buffer
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
  
USE AdventureWorks2014;
GO

-- Query Sales.SalesOrderDetail table
SELECT * FROM Sales.SalesOrderDetail
WHERE SalesOrderDetailID = 1;
  

USE AdventureWorksDW2014;
GO

-- Query dbo.DimProduct
SELECT * FROM dbo.DimProduct
WHERE ProductKey = 1;

-- Checking chached plans
SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE 
 TEXT LIKE '%SELECT * FROM Sales.SalesOrderDetail%'
 OR TEXT LIKE '%SELECT * FROM dbo.DimProduct%';

As we expected, we see two cached plans in plan cache.

This code changes the Recovery Model of AdventureWorks2014. Second statement shows plans in the cache after the change.

-- Change the Recovery Model
ALTER DATABASE AdventureWorks2014 SET RECOVERY Bulk_Logged;
GO

-- And see plans
SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE 
 TEXT LIKE '%SELECT * FROM Sales.SalesOrderDetail%'
 OR TEXT LIKE '%SELECT * FROM dbo.DimProduct%';

As you see, plan related to AdventureWorks2014 has been removed. This proves that changing the Recovery Model flushes plans in the cache.

Now the question is, how advisable to do this in production environment for bulk operations. My recommendation is, if it is a large bulk operation and database is heavily accessed by users, bulk operation should be done during off-peak hours. We still lose cached plans but this will at least minimize the damage. However, if the database is continuously being used by users, then it is advisable to perform bulk operations without changing the Recovery Model.

Log is getting automatically truncated with FULL Recovery Model

General understanding regarding the log file (ldf) is, it is getting automatically truncated when the Recovery Model is set to SIMPLE and it needs transaction log backup to be taken for truncating when the Recovery Model is set to FULL. Once the transaction log is truncated only, the unused space can be released back to the operating system.

Can there be a situation where the log is getting automatically truncated even with FULL Recovery Model? Yes, it is possible. Here is the code that shows it;

This code snippet creates two databases called Database_SIMPLERecovery and Database_FULLRecovery. Recovery model of first is set as SIMPLE and second set to FULL. Note that initial log file size is 1MB.

-- 1.
-- Creating a database with SIMPLE recovery model
USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Database_SimpleRecovery')
 DROP DATABASE Database_SimpleRecovery
GO
CREATE DATABASE [Database_SimpleRecovery]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Database_SimpleRecovery', 
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_SimpleRecovery.mdf' 
 , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Database_SimpleRecovery_log', 
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_SimpleRecovery_log.ldf' 
 , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Database_SimpleRecovery] SET RECOVERY SIMPLE 
GO


-- 2.
-- Creating a database with FULL recovery model
USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Database_FullRecovery')
 DROP DATABASE Database_FullRecovery
GO
CREATE DATABASE [Database_FullRecovery]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Database_FullRecovery', 
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_FullRecovery.mdf' 
 , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Database_FullRecovery_log', 
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_FullRecovery_log.ldf' 
 , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE Database_FullRecovery SET RECOVERY FULL 
GO

Let's add some records to these two databases and check the space used by log files.

-- Adding some transactions to Database_SimpleRecovery 
USE Database_SimpleRecovery;
GO

SELECT *
INTO dbo.TestTable
FROM AdventureWorksDW2014.dbo.FactInternetSales2;

-- Checking for freespace in the log
SELECT DB_NAME() AS DatabaseName, 
 name AS FileName, 
 size/128.0 AS CurrentSizeMB,  
 size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 


-- Adding some transactions to Database_FullRecovery 
USE Database_FullRecovery;
GO

SELECT *
INTO dbo.TestTable
FROM AdventureWorksDW2014.dbo.FactInternetSales2;

-- Checking for freespace in the log
SELECT DB_NAME() AS DatabaseName, 
 name AS FileName, 
 size/128.0 AS CurrentSizeMB,  
 size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 

Note the free-space in both log files. Although the files sizes have gone up, no active transactions, they have been truncated, more free-space, hence free-space can be released (You might not see the same free-space immediately if CHECKPOINT process has not been run. If so, wait for few seconds and query for space again). However, when the database is in FULL recovery model, transactions should not be truncated until a transaction log backup is performed. What is the issue with second database?

There can be two more reasons for getting transaction log automatically truncated;

1. Full database backup has never been taken.
2. Full or differential backup has never been taken after switching from SIMPLE to FULL recovery model.

Since we have not taken a backup, our second database log is getting automatically truncated. Let's take a full backup and then do the same and see.

-- Connecting with Database_FullRecovery
USE Database_FullRecovery;
GO

-- Dropping table
DROP TABLE dbo.TestTable;
GO

-- Taking full backup
BACKUP DATABASE Database_FullRecovery
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\Database_FullRecovery.bak';
GO

-- Loading data again
SELECT *
INTO dbo.TestTable
FROM AdventureWorksDW2014.dbo.FactInternetSales2;

-- Checking for freespace in the log
SELECT DB_NAME() AS DatabaseName, 
 name AS FileName, 
 size/128.0 AS CurrentSizeMB,  
 size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 

As you see, not much free-space in the file now, means log has not been truncated. All you have remember is, in a scenario like this, log gets automatically truncated, which is not acceptable with FULL recovery model and should be avoided. If you need to see whether your database log file is getting automatically truncated with FULL recovery model, use sys.database_recovery_status to see last_log_backup_lsn column. If the column is NULL, then it indicates that the log is getting automatically truncated.

SELECT * FROM sys.database_recovery_status
WHERE database_id = DB_ID();