A SQL Server DBA myth a day: (14/30) clearing the log zeroes out log records

(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join our Insider list, plus my online Myths and Misconceptions training course on Pluralsight.)

Today is the big day – 8am PST I’m presenting my DBA Mythbusters session at SQL Connections for the first time. I’ll hopefully do it as a longer Spotlight Session at SQL PASS this Fall too.

So a little quickie today as I’m doing 4 sessions in a row.

Myth #14: clearing the log zeroes out log records.

FALSE

The transaction log is *always* zero initialized when first created, manually grown, or auto-grown. Do not confuse this with the process of clearing the log during regular operations. That simply means that one or more VLFs (Virtual Log Files) are marked as inactive and able to be overwritten. When log clearing occurs, nothing is cleared or overwritten. ‘Clearing the log’ is a very confusing misnomer. It means the exact same as ‘truncating the log’, which is another unfortunate misnomer, because the size of the log doesn’t change at all.

You can read more about zero initialization of the log in my blog post Search Engine Q&A #24: Why can’t the transaction log use instant initialization? and about how log clearing works in my TechNet Magazine article from February 2009: Understanding Logging and Recovery in SQL Server.

You can prove this to yourself using trace flag 3004. Turning it on will let you see when SQL Server is doing file zeroing operations (as I described in A SQL Server DBA myth a day: (3/30) instant file initialization can be controlled from within SQL Server). Turn it on and watch for messages coming during the day – you shouldn’t see anything unless the log grows.

Here’s a script to show you what I mean:

DBCC TRACEON (3004, 3605);
GO

-- Create database and put in SIMPLE recovery model so the log will clear on checkpoint
CREATE DATABASE [LogClearTest] ON PRIMARY (
    NAME = N'LogClearTest_data', FILENAME = N'D:\SQLskills\LogClearTest_data.mdf')
LOG ON (
    NAME = N'LogClearTest_log', FILENAME = N'D:\SQLskills\LogClearTest_log.ldf',
SIZE = 20MB);
GO

-- Error log mark 1
ALTER DATABASE [LogClearTest] SET RECOVERY SIMPLE;
GO

USE [LogClearTest];
GO

-- Create table and fill with 10MB - so 10MB in the log
CREATE TABLE [t1] ([c1] INT IDENTITY, [c2] CHAR (8000) DEFAULT 'a');
GO
INSERT INTO [t1] DEFAULT VALUES;
GO 1280

-- Clear the log
CHECKPOINT;
GO

-- Error log mark 2
ALTER DATABASE [LogClearTest] SET RECOVERY SIMPLE;
GO

And in the error log I see:

2010-04-13 13:20:27.55 spid53      DBCC TRACEON 3004, server process ID (SPID) 53. This is an informational message only; no user action is required.
2010-04-13 13:20:27.55 spid53      DBCC TRACEON 3605, server process ID (SPID) 53. This is an informational message only; no user action is required.
2010-04-13 13:20:27.63 spid53      Zeroing D:\SQLskills\LogClearTest_log.ldf from page 0 to 2560 (0x0 to 0x1400000)
2010-04-13 13:20:28.01 spid53      Zeroing completed on D:\SQLskills\LogClearTest_log.ldf
2010-04-13 13:20:28.11 spid53      Starting up database 'LogClearTest'.
2010-04-13 13:20:28.12 spid53      FixupLogTail() zeroing D:\SQLskills\LogClearTest_log.ldf from 0x5000 to 0x6000.
2010-04-13 13:20:28.12 spid53      Zeroing D:\SQLskills\LogClearTest_log.ldf from page 3 to 63 (0x6000 to 0x7e000)
2010-04-13 13:20:28.14 spid53      Zeroing completed on D:\SQLskills\LogClearTest_log.ldf
2010-04-13 13:20:28.16 spid53      Setting database option RECOVERY to SIMPLE for database LogClearTest.
2010-04-13 13:20:29.49 spid53      Setting database option RECOVERY to SIMPLE for database LogClearTest.

The two ALTER DATABASE commands serve as markers in the error log. There’s clearly no zeroing occurring from the CHECKPOINT between the two ALTER DATABASE commands. To further prove to yourself, you can add in calls to DBCC SQLPERF (LOGSPACE) before and after the CHECKPOINT, to show that the log is clearing when the checkpoint occurs (watch the value in the Log Space Used (%) column decrease).

Now it’s session time here in Vegas! (And there’s no-one to blame but yourself if you’re the Conference Chair and you’ve got an 8am session! :-)

5 thoughts on “A SQL Server DBA myth a day: (14/30) clearing the log zeroes out log records

  1. Hi Paul,
    It is a relatively old article but I have been thinking lately about the recovery and I know from your Pluralsight course that recovery stops when sql hits a log block full of zeroes or a vlf with different parity.
    If we go back to the case with log block full of zeroes, my question is how is this possible if sql is using the vlf for the second time for example?
    Let’s say we have 4 vlfs and sql has gone to thr last one, managed to do a truncation and now we are back again using the first two vlfs. Sql crashed and we need to make a recovery and we were at the middle of the second vlf so how will sql determine where to stop the recovery? It should be the first case with log block of zeroes but this should mean that sql has performed zeroing on this log block in the meantime as we are using this vlf for the second time and it was occupied by a meaningful info.
    Hope I explained my question well
    Thanks in advance for a response

    1. I have the same question. I don’t see a response for this. The article says that there is no zeroing after the checkpoint (Simple recovery Point) . Then how does SQL determine , where to stop the recovery.

      1. It uses the parity bits for each VLF to determine when an old portion of a VLF has been hit. I haven’t blogged about this but I explain it in my Pluralsight course on logging.

  2. I read a couple articles by Paul & Kimberly about instant file initialization (IFI). I agree in the benefit based on Kimberley’s data comparing with & without IFI.
    I found where to add the “perform maint tasks” option in lusrmgr.msc. But my domain doesn’t have a SQL instance security group, I only have a user, “SvcMSSQL$.” Should I create a security group where SvcMSSQL$ is a member? Do this in local or domain context? My habit is to use domain security groups. However, the group wasn’t created during SQL install. Now would be a good time to correct it.

    1. It doesn’t matter whether it’s the SQL instance user or a group the member is part of. I can’t answer what the best thing to do is, as that’s determined by your company’s IT policies.

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.