The log file tends to fascinate me. In fact one of my favorite posts is where I looked into the effect of VLF size on shrinking the log. So the other day I was asking about VLFs and got sent to this great video of a recorded session from PASS SQLRally Nordic 2013 by Mikael Wedham (b/t) on the subject. It’s a great video and I highly recommend you watch it. About 5 minutes in I started thinking “Wow that’s cool! I need to share that.” Now while I’m sure this little factoid is not completely unknown I certainly don’t think it’s widely known either.

Before the first full backup is taken on a database the transaction log will act like the recovery mode is SIMPLE even if the actual setting is FULL. (Someone told me the other day this is called “Pseudo-Simple” mode.) Of course this makes a lot of sense when you think about it. Without a FULL backup to restore you can’t restore any LOG backups. Heck until the first FULL backup is taken you can’t even take a LOG backup. Remember that SQL will still be logging everything at this point as it’s still needed to ensure ACID compliance. (Same as in SIMPLE recovery mode.)

And here’s the inevitable demo:

-- Create a brand new (very small) database
USE [master]
GO
/****** Object:  Database [LogTests]    Script Date: 10/18/2014 9:39:50 AM ******/CREATE DATABASE [LogTests]
 ON  PRIMARY 
(NAME = N'LogTests', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CS\MSSQL\DATA\LogTests.mdf' , 
SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
(NAME = N'LogTests_log', 
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014CS\MSSQL\DATA\LogTests_log.ldf' , 
SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB )
GO

-- Check the log size and the amount of free log space
USE LogTests
GO
SELECT * FROM sys.sysfiles where groupid = 0;
GO
DBCC SQLPERF(LOGSPACE);
GO

Note that the log size is 128 pages and 39.9% used.

Next we create a table & start loading some data.

CREATE TABLE TakeUpSpace (Space1 char(8000));
GO
INSERT INTO TakeUpSpace VALUES (REPLICATE('A',8000));
GO 5000

And while that is running, on a separate connection let’s check the free space periodically. Then at the end double check that the log size has not changed.

USE LogTests
GO
DBCC SQLPERF(LOGSPACE);
GO
WAITFOR DELAY '00:00:06'
GO
DBCC SQLPERF(LOGSPACE);
GO
WAITFOR DELAY '00:00:06'
GO
DBCC SQLPERF(LOGSPACE);
GO
WAITFOR DELAY '00:00:06'
GO
DBCC SQLPERF(LOGSPACE);
GO
WAITFOR DELAY '00:00:06'
GO
DBCC SQLPERF(LOGSPACE);
GO
WAITFOR DELAY '00:00:06'
GO
DBCC SQLPERF(LOGSPACE);
SELECT * FROM sys.sysfiles where groupid = 0;
GO

At the bottom you can see that throughout the process the log file stayed the same size. Yet the the amount of used space in the log went up and down even though we were constantly adding rows to the table (each insert being logged).

Next let’s take a quick FULL backup.

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

Then we do a before check of the used space and size of the log.

DBCC SQLPERF(LOGSPACE);
SELECT * FROM sys.sysfiles where groupid = 0;
GO

Run our inserts again.

INSERT INTO TakeUpSpace VALUES (REPLICATE('A',8000));
GO 5000

And check the used space and size of the log after the insert.

DBCC SQLPERF(LOGSPACE);
SELECT * FROM sys.sysfiles where groupid = 0;
GO

Things are a bit different this time. We went from 47.2% used and 128 pages to 99.5% full and 5888 pages. Obviously a big difference.

Now one last test. Let’s break the backup chain by changing the recovery mode to SIMPLE and back to FULL again and rerun our tests.

ALTER DATABASE [LogTests] SET RECOVERY SIMPLE;
GO
ALTER DATABASE [LogTests] SET RECOVERY FULL;
GO

I’m going to run the simplified test again although you are certainly welcome to do the larger test yourself.

Initial check of the used space and size of the log.

DBCC SQLPERF(LOGSPACE);
SELECT * FROM sys.sysfiles where groupid = 0;
GO

Because the log file is much larger this time the percent used is much smaller.

This time we are going to run three times as many inserts to make it obvious that the log file would have grow in a normal FULL recovery situation.

INSERT INTO TakeUpSpace VALUES (REPLICATE('A',8000));
GO 15000

And one last check of the used space and size of the log after the insert.

DBCC SQLPERF(LOGSPACE);
SELECT * FROM sys.sysfiles where groupid = 0;
GO

As you can see the amount of used space has barely increased (9 to 13%) and the file size has not increased at all.

This is going to have to be something I stay aware of as I add new databases and inevitably forget to run an initial FULL backup.

Filed under: Backups, Log File, Microsoft SQL Server, SQLServerPedia Syndication, VLF Tagged: backups, microsoft sql server, transactions, VLF