Playing with the log – Before the first full backup

1

October 29, 2014 by Kenneth Fisher

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

PsudoSimple1

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

PsudoSimple2

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

PsudoSimple3

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

PsudoSimple4

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

PsudoSimple5

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

PsudoSimple6

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.

One thought on “Playing with the log – Before the first full backup

  1. […] Once we have created the database we need to take an initial backup to keep it out of pseduo simple mode. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,146 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: