(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.
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! :-)