How SQL Server handles our requests for modifications

This is not something new with latest Microsoft SQL Server but this is still being discussed and it is unknown or unclear to many of database developers. While I was discussing transaction log of SQL Server database with few, as a part of it, how SQL Server accepts our requests and modifies records was discussed. Thought it is useful to everyone, hence making a post on it;

What really happens when we send a request to SQL Server? It can be an update or a delete. Request might be related to one record or many records. Have a look on below image;

This starts with the request. Either using an application or connecting directly to SQL Server using something like SSMS, we send the request. Once SQL Server received the request, it checks data pages related to the records. If data pages required are not in the memory (or buffer cache), it loads relevant data pages from the disk to memory. Then, remember, it modifies records in pages that are in the memory, not pages in the disk. That is what 1st and 2nd steps in the image explains.

Third step is, updating the transaction log in the disk. Once the page (or pages) in the memory are modified, they become dirty pages. Then SQL Server writes redo and undo information to the log file. During this update, pages related are locked until the transaction log is completely updated.

Once the log is updated, the acknowledgement is sent to the application. Note that, even though the data files are not updated, we receive a message saying records are successfully updated. But we do not want worry, even something happen after we received the message, SQL Server can recover committed records, making sure durability which is one of the properties of the transaction, is satisfied with SQL Server.

Later, after one ore more transactions, a process called Checkpoint writes all dirty pages back to the disk, updating data files. This is how SQL Server handles our update requests.

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.

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();