/*
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)
*/
Saturday, January 15, 2011
SQL 2008 Core Studies - Log File Internals
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment