T-SQL Tuesday #67 – monitoring log activity with Extended Events

On the second Tuesday of each month, many people in the SQL Server community join together to all blog on the same topic – a cool idea from Adam Machanic many years ago.

This month’s topic is Extended Events, hosted by Jes Borland (b | t) – you can see her original post here.

Yesterday in class I was discussing monitoring transaction log activity with a student, to show that when a transaction commits, there is a write to the transaction log file. This is easy to do with Extended Events.

I’m going to use the file_write_completed event to track writes occurring and the transaction_log event to watch log records being generated so we can see transactions committing.

First of all I’ll setup my simple scenario:

USE [master];
GO

IF DATABASEPROPERTYEX (N'Test', N'Version') > 0
BEGIN
	ALTER DATABASE [Test] SET SINGLE_USER
		WITH ROLLBACK IMMEDIATE;
	DROP DATABASE [Test];
END
GO

CREATE DATABASE [Test] ON PRIMARY (
    NAME = N'Test_data',
    FILENAME = N'D:\SQLskills\Test_data.mdf')
LOG ON (
    NAME = N'Test_log',
    FILENAME = N'C:\SQLskills\Test_log.ldf',
    SIZE = 1MB,
    FILEGROWTH = 0MB);
GO

USE [test];
GO

CREATE TABLE TestTable (
	c1 INT IDENTITY,
	c2 CHAR (1000) DEFAULT 'a');
GO

INSERT INTO [TestTable] DEFAULT VALUES;
GO

And here’s the simple Extended Event session:

-- Drop the session if it exists. 
IF EXISTS (
	SELECT * FROM sys.server_event_sessions
		WHERE [name] = N'MonitorLog')
    DROP EVENT SESSION [MonitorLog] ON SERVER
GO

-- Create the event session
CREATE EVENT SESSION [MonitorLog] ON SERVER
	ADD EVENT [sqlserver].[file_write_completed],
	ADD EVENT [sqlserver].[transaction_log]
	ADD TARGET [package0].[ring_buffer]
		WITH (MAX_MEMORY = 50MB, max_dispatch_latency = 1 seconds)
GO

-- Start the session
ALTER EVENT SESSION [MonitorLog] ON SERVER
STATE = START;
GO

I ran both of those scripts, then executed the INSERT a few times.

Now if I go to Object Explorer, I can use the Live Data Viewer (in SQL Server 2012 onwards). Find the session, right-click on it and select Watch Live Data.

Choose the following columns to view (right click on any column name and select Choose Columns…): name, timestamp, database_id, file_id, size, operation.

And then run another INSERT (you might have to run two to make the first set of data show in the Viewer) and you’ll see data like below.

And then you can play around and watch stuff happening.

Enjoy! (and check out the other posts from this T-SQL Tuesday)