Saturday, January 15, 2011

SQL 2008 Core Studies - Log File Internals


/*
04 - Logfile Internal.sql, how a transaction log
is circular in nature and how it can skip
active VLFs
Remark: Learned from sqlskills.com
*/

USE master
GO

IF DATABASEPROPERTY ('MCM2008', 'Version') >0
DROP DATABASE MCM2008;

CREATE DATABASE MCM2008 ON PRIMARY (
NAME = 'MCM2008DATA',
FILENAME = N'C:\SQLskills\MCM2008_data.mdf')
LOG ON (
NAME = 'MCM2008_log',
FILENAME = N'C:\SQLskills\MCM2008_log.ldf',
SIZE = 5MB,
FILEGROWTH = 1MB);
GO

USE MCM2008;
GO

SET NOCOUNT ON;
GO

-- Make sure the database is in SIMPLE
-- recovery model
ALTER DATABASE MCM2008 SET RECOVERY SIMPLE;
GO

-- What does the log look like?
DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 19 2 64 0
2 1245184 1253376 0 0 0 0
2 1245184 2498560 0 0 0 0
2 1499136 3743744 0 0 0 0
*/

-- Create a table that will grow very
-- quickly and genrate lots of transaction
-- log
CREATE TABLE BigRows (
c1 INT IDENTITY,
c2 CHAR (8000) DEFAULT 'a');
GO

-- Insert some rows to fill the first
-- two VLFs and start the third
INSERT INTO BigRows DEFAULT VALUES;
GO 300

-- What does the log look like now
DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 19 2 64 0
2 1245184 1253376 20 2 64 0
2 1245184 2498560 21 2 64 0
2 1499136 3743744 0 0 0 0
*/


-- Now start an explicit transaction which
-- will hold VLF 3 and onwards active
BEGIN TRAN
INSERT INTO BigRows DEFAULT VALUES;
GO

-- Now checkpoint to clear the first two
-- VLFs and look at the log again
CHECKPOINT;
GO

DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 19 0 64 0
2 1245184 1253376 20 0 64 0
2 1245184 2498560 21 2 64 0
2 1499136 3743744 0 0 0 0

(4 row(s) affected)
*/

-- Now add some more rows that will fill
-- up VLFs 3 and 4 and then wrap around
INSERT INTO BigRows DEFAULT VALUES;
GO 300

DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 23 2 128 0
2 1245184 1253376 20 0 64 0
2 1245184 2498560 21 2 64 0
2 1499136 3743744 22 2 64 0

(4 row(s) affected)
*/

-- Now add some more rows - the log is
-- forced to grow. What do the VLF
-- sequence number look like?
INSERT INTO BigRows DEFAULT VALUES;
GO 300

DBCC LOGINFO;
GO
/*
4 new VLFs created because of auto-grow,
4 new VLFs created because of space reseving for rollback.
*/

/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 23 2 128 0
2 1245184 1253376 24 2 128 0
2 1245184 2498560 21 2 64 0
2 1499136 3743744 22 2 64 0
2 253952 5242880 25 2 64 24000000061600024
2 253952 5496832 26 2 64 24000000061600024
2 253952 5750784 27 2 64 24000000061600024
2 286720 6004736 0 0 0 24000000061600024
2 253952 6291456 0 0 0 24000000212700024
2 253952 6545408 0 0 0 24000000212700024
2 253952 6799360 0 0 0 24000000212700024
2 286720 7053312 0 0 0 24000000212700024

(12 row(s) affected)
*/

-- Will checkpoint clear it now?
CHECKPOINT;
GO

DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 23 2 128 0
2 1245184 1253376 24 2 128 0
2 1245184 2498560 21 2 64 0
2 1499136 3743744 22 2 64 0
2 253952 5242880 25 2 64 24000000061600024
2 253952 5496832 26 2 64 24000000061600024
2 253952 5750784 27 2 64 24000000061600024
2 286720 6004736 0 0 0 24000000061600024
2 253952 6291456 0 0 0 24000000212700024
2 253952 6545408 0 0 0 24000000212700024
2 253952 6799360 0 0 0 24000000212700024
2 286720 7053312 0 0 0 24000000212700024

(12 row(s) affected)
*/

-- Find the open transaction
DBCC OPENTRAN
GO
/*
Transaction information for database 'MCM2008'.

Oldest active transaction:
SPID (server process ID): 55
UID (user ID) : -1
Name : user_transaction
LSN : (21:942:2)
Start time : Jan 15 2011 12:23:17:820PM
SID : 0x01050000000000051500000088910179c111e2566c1dbe1ef4010000
*/

-- How about now?
COMMIT TRAN;
GO

CHECKPOINT;
GO

DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 23 0 128 0
2 1245184 1253376 24 0 128 0
2 1245184 2498560 21 0 64 0
2 1499136 3743744 22 0 64 0
2 253952 5242880 25 0 64 24000000061600024
2 253952 5496832 26 0 64 24000000061600024
2 253952 5750784 27 2 64 24000000061600024
2 286720 6004736 0 0 0 24000000061600024
2 253952 6291456 0 0 0 24000000212700024
2 253952 6545408 0 0 0 24000000212700024
2 253952 6799360 0 0 0 24000000212700024
2 286720 7053312 0 0 0 24000000212700024

(12 row(s) affected)
*/

-- Insert more rows to wrap the transaction
-- log, pay attention to "Parity" column.

INSERT INTO BigRows DEFAULT VALUES;
GO 400

DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 23 0 128 0
2 1245184 1253376 24 0 128 0
2 1245184 2498560 33 2 128 0
2 1499136 3743744 34 2 128 0
2 253952 5242880 25 0 64 24000000061600024
2 253952 5496832 26 0 64 24000000061600024
2 253952 5750784 27 2 64 24000000061600024
2 286720 6004736 28 2 64 24000000061600024
2 253952 6291456 29 2 64 24000000212700024
2 253952 6545408 30 2 64 24000000212700024
2 253952 6799360 31 2 64 24000000212700024
2 286720 7053312 32 2 64 24000000212700024

(12 row(s) affected)
*/

INSERT INTO BigRows DEFAULT VALUES;
GO 400

DBCC LOGINFO;
GO
/*
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- -------------------- -------------------- ----------- ----------- ------ ---------------------------------------
2 1245184 8192 35 2 64 0
2 1245184 1253376 36 2 64 0
2 1245184 2498560 33 0 128 0
2 1499136 3743744 34 0 128 0
2 253952 5242880 37 2 128 24000000061600024
2 253952 5496832 38 2 128 24000000061600024
2 253952 5750784 39 2 128 24000000061600024
2 286720 6004736 40 2 128 24000000061600024
2 253952 6291456 29 0 64 24000000212700024
2 253952 6545408 30 0 64 24000000212700024
2 253952 6799360 31 0 64 24000000212700024
2 286720 7053312 32 0 64 24000000212700024

(12 row(s) affected)
*/