John Sansom (Blog|Twitter) asked on the MSDN Forums about the meaning/description for the numeric values returned by the EventSubClass column of the TransactionLog SQL Trace Event. John pointed out that this information is not available for this Event like it is for the other events in the Books Online Topic (TransactionLog Event Class), or in the sys.trace_subclass_values DMV. John wanted to know if there was a way to determine this information. I did some looking and found an old SQL Server 2000 BOL topic, Transactions Data Columns gave more information about what the EventSubClass means and defined the column as:
Type of transaction log event, such as BEGINXACT(null).
After seeing this, I knew it would be possible to map this out using the new sqlserver.transaction_log Event in Extended Events, which I blogged about last month in my blog post, An XEvent a Day (22 of 31) – The Future – fn_dblog() No More? Tracking Transaction Log Activity in Denali. To start with I created a database with database_id = 5 on my VM that I would run a number of test workloads against. Then I created a filtered Extended Events Session for the sqlserver.transaction_log Event in Extended Events in a SQL Server Denali CTP1 VM on my laptop for the sqlserver.transaction_log Event with a Predicate on database_id = 5 for the Event.
CREATE EVENT SESSION tranlog
ADD EVENT sqlserver.transaction_log
( ACTION (sqlserver.event_sequence)
WHERE database_id = 5)
ADD target package0.ring_buffer
WITH (MAX_DISPATCH_LATENCY = 5SECONDS, EVENT_RETENTION_MODE=NO_EVENT_LOSS)
ALTER EVENT SESSION tranlog
I then created a SQL Trace in Profiler that also captured the TransactionLog Event with all of the available columns into a Trace Table named TranLogTrace in the master database that also had a filter on DatabaseID = 5. Then I ran a couple of workload samples against the demo database (Note: These tests do not necessarily represent a complete workload.)
CREATE TABLE Test (rowid INT IDENTITY PRIMARY KEY)
INSERT INTO Test DEFAULT VALUES;
INSERT INTO Test DEFAULT VALUES;
Based on this admittedly, overly simplistic test, the following mappings to of the EventSubClass of the SQL Trace TransactionLog using the following the parsing of the Extended Event Session Data as a comparison against the SQL Trace TransactionLog Event Data can be made:
This is no way a complete mapping of the to entire subset of EventSubClass value’s of the SQL Trace Event, but it shows that there is a mechanism available through Extended Events that can map this kind of information out.
While I was playing with this, I did some testing along the lines of the original question of the MSDN Forums post, and there is nothing in the TransactionLog Event that you could key on to know the log truncated. No Transaction Log record is generated that specifically says the log truncated, it opens a transaction, writes to the boot_page, and closes the transaction all in different log records. Keep in mind that the boot page gets written to by a number of other operations in SQL Server, so keying on it is not the answer. For example, if the log is failing to truncate because there is an open transaction and it has to grow, the growth would trigger a write to the boot page. Attempting to key in on TransactionLog Events in SQL Trace that write to the Boot Page alone would be short sighted, and incorrect in this type of analysis alone.