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)
3 thoughts on “T-SQL Tuesday #67 – monitoring log activity with Extended Events”
Is this a bug that i cannot locate transaction_log under the GUI for XEvents even in SQL Server 2014? I wonder how many more events are there…..
No – it’s by design. Use New Session and then click Channel and tick the Debug box. Then all events will show up.
Great article! Very helpful. However, you might include the warning associate with the transaction_log event “Occurs when a record is added to the SQL Server transaction log. This is a very high volume event that will affect the performance of the server. Therefore, you should use appropriate filtering to reduce the number of events, and only use this event for targeted troubleshooting during a short time period.”