Mining transaction log backups

It’s commonly known that the sys.fn_dblog function can be used to query the contents of the transaction log. Frequently run data changes can be identified using this function. If you are taking transaction log backups then the record of the committed changes will be written to the transaction log backup and the log itself will be cleared. If you are running scheduled transaction log backups it’s possible to mine these backups using the sys.fn_dump_dblog function. An example of a statistic that may be useful is the number of page splits that are occurring for a given clustered index.

The following script creates a test table, inserts some test data in clustered primary key order and finally inserts another row into the middle of the existing range:


CREATE TABLE dbo.testsplits (
id int NOT NULL CONSTRAINT pk_testsplits PRIMARY KEY,
val char(500) NOT NULL
)

BACKUP DATABASE TestSplits TO DISK = 'c:\sqlback\TestSplits.bak' WITH INIT

TRUNCATE TABLE dbo.testsplits

CHECKPOINT

BACKUP LOG TestSplits TO DISK = 'c:\sqlback\TestSplits.trn' WITH INIT

INSERT INTO dbo.testsplits VALUES (1,'testing')
INSERT INTO dbo.testsplits VALUES (2,'testing')
INSERT INTO dbo.testsplits VALUES (3,'testing')
INSERT INTO dbo.testsplits VALUES (4,'testing')
INSERT INTO dbo.testsplits VALUES (5,'testing')
INSERT INTO dbo.testsplits VALUES (6,'testing')
INSERT INTO dbo.testsplits VALUES (7,'testing')
INSERT INTO dbo.testsplits VALUES (8,'testing')
INSERT INTO dbo.testsplits VALUES (9,'testing')
INSERT INTO dbo.testsplits VALUES (10,'testing')
-- INSERT INTO dbo.testsplits VALUES (11,'testing')
INSERT INTO dbo.testsplits VALUES (12,'testing')
INSERT INTO dbo.testsplits VALUES (13,'testing')
INSERT INTO dbo.testsplits VALUES (14,'testing')
INSERT INTO dbo.testsplits VALUES (15,'testing')
INSERT INTO dbo.testsplits VALUES (16,'testing')

INSERT INTO dbo.testsplits VALUES (11,'testing')

CHECKPOINT

You can use the sys.fn_dblog function to query the log for inserts and page splits for the testspits table:


SELECT
[Current LSN],[Operation],[Context],[Description], [AllocUnitId],[AllocUnitName],
[Page ID],[Slot ID], [New Split Page],[Rows Deleted],[Bytes Freed]
FROM sys.fn_dblog(null,null)
WHERE [Operation] IN ('LOP_INSERT_ROWS','LOP_DELETE_SPLIT')
AND [Context] = 'LCX_CLUSTERED'
AND [AllocUnitName] = 'dbo.testsplits.pk_testsplits'
ORDER BY [Current LSN]

testsplits1

Backup the transaction log and you can now query it for the same information using the sys.fn_dump_dblog function:


BACKUP LOG TestSplits TO DISK = 'c:\sqlback\TestSplits.trn' WITH INIT

DECLARE @allocunitid bigint

SELECT @allocunitid = allocation_unit_id
FROM sys.allocation_units au
INNER JOIN sys.partitions p
ON au.container_id =
(
CASE WHEN au.type IN (1,3) THEN p.hobt_id
WHEN au.type = 2 THEN p.partition_id
END
)
WHERE p.object_id = OBJECT_ID('testsplits')
AND p.index_id = 1

SELECT
[Current LSN],[Operation],[Context],[Description], [AllocUnitId],[AllocUnitName],
[Page ID],[Slot ID], [New Split Page],[Rows Deleted],[Bytes Freed]
FROM sys.fn_dump_dblog(default,default,default,default,
'c:\sqlback\TestSplits.trn',default,default,default,default,default,default,default,
default,default,default,default,default,default,default,default,
default,default,default,default,default,default,default,default,
default,default,default,default,default,default,default,default,
default,default,default,default,default,default,default,default,
default,default,default,default,default,default,default,default,
default,default,default,default,default,default,default,default,
default,default,default,default,default,default,default,default
)
WHERE [Operation] IN ('LOP_INSERT_ROWS','LOP_DELETE_SPLIT')
AND [Context] = 'LCX_CLUSTERED'
AND [AllocUnitId] = @allocunitid
ORDER BY [Current LSN]

testsplits2

You can see that the allocation unit name is not persisted in the transaction log backup so we have to use the allocation unit id to search for the specific clustered index.

You don’t have to run this command on your production server. You can copy the transaction log backups to another SQL Server and query them there.

This is just one example of mining transaction log backups for useful information.

Advertisements
  1. Leave a comment

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

%d bloggers like this: