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;
- Full database backup has never been taken.
- 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();
