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.

view plainprint?

1. -- 1.  
2. -- Creating a database with SIMPLE recovery model  
3. USE master;  
4. GO  
5.   
6. IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Database_SimpleRecovery')  
7.  DROP DATABASE Database_SimpleRecovery  
8. GO  
9. CREATE DATABASE [Database_SimpleRecovery]  
10.  CONTAINMENT = NONE  
11.  ON  PRIMARY   
12. ( NAME = N'Database_SimpleRecovery',   
13.  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_SimpleRecovery.mdf'   
14.  , SIZE = 3072KB , FILEGROWTH = 1024KB )  
15.  LOG ON   
16. ( NAME = N'Database_SimpleRecovery_log',   
17.  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_SimpleRecovery_log.ldf'   
18.  , SIZE = 1024KB , FILEGROWTH = 10%)  
19. GO  
20. ALTER DATABASE [Database_SimpleRecovery] SET RECOVERY SIMPLE   
21. GO  
22.   
23.   
24. -- 2.  
25. -- Creating a database with FULL recovery model  
26. USE master;  
27. GO  
28.   
29. IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Database_FullRecovery')  
30.  DROP DATABASE Database_FullRecovery  
31. GO  
32. CREATE DATABASE [Database_FullRecovery]  
33.  CONTAINMENT = NONE  
34.  ON  PRIMARY   
35. ( NAME = N'Database_FullRecovery',   
36.  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_FullRecovery.mdf'   
37.  , SIZE = 3072KB , FILEGROWTH = 1024KB )  
38.  LOG ON   
39. ( NAME = N'Database_FullRecovery_log',   
40.  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_FullRecovery_log.ldf'   
41.  , SIZE = 1024KB , FILEGROWTH = 10%)  
42. GO  
43. ALTER DATABASE Database_FullRecovery SET RECOVERY FULL   
44. GO  

-- 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.

view plainprint?

1. -- Adding some transactions to Database_SimpleRecovery   
2. USE Database_SimpleRecovery;  
3. GO  
4.   
5. SELECT *  
6. INTO dbo.TestTable  
7. FROM AdventureWorksDW2014.dbo.FactInternetSales2;  
8.   
9. -- Checking for freespace in the log  
10. SELECT DB_NAME() AS DatabaseName,   
11.  name AS FileName,   
12.  size/128.0 AS CurrentSizeMB,    
13.  size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB   
14. FROM sys.database_files;   
15.   
16.   
17. -- Adding some transactions to Database_FullRecovery   
18. USE Database_FullRecovery;  
19. GO  
20.   
21. SELECT *  
22. INTO dbo.TestTable  
23. FROM AdventureWorksDW2014.dbo.FactInternetSales2;  
24.   
25. -- Checking for freespace in the log  
26. SELECT DB_NAME() AS DatabaseName,   
27.  name AS FileName,   
28.  size/128.0 AS CurrentSizeMB,    
29.  size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB   
30. FROM sys.database_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.

view plainprint?

1. -- Connecting with Database_FullRecovery  
2. USE Database_FullRecovery;  
3. GO  
4.   
5. -- Dropping table  
6. DROP TABLE dbo.TestTable;  
7. GO  
8.   
9. -- Taking full backup  
10. BACKUP DATABASE Database_FullRecovery  
11. TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\Database_FullRecovery.bak';  
12. GO  
13.   
14. -- Loading data again  
15. SELECT *  
16. INTO dbo.TestTable  
17. FROM AdventureWorksDW2014.dbo.FactInternetSales2;  
18.   
19. -- Checking for freespace in the log  
20. SELECT DB_NAME() AS DatabaseName,   
21.  name AS FileName,   
22.  size/128.0 AS CurrentSizeMB,    
23.  size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB   
24. FROM sys.database_files;   

-- 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.

view plainprint?

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

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