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.
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)