{"id":542,"date":"2010-12-22T10:38:00","date_gmt":"2010-12-22T10:38:00","guid":{"rendered":"\/blogs\/jonathan\/post\/An-XEvent-a-Day-(22-of-31)-e28093-The-Future-e28093-fn_dblog()-No-More-Tracking-Transaction-Log-Activity-in-Denali.aspx"},"modified":"2017-04-13T12:18:30","modified_gmt":"2017-04-13T16:18:30","slug":"an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/","title":{"rendered":"An XEvent a Day (22 of 31) \u2013 The Future \u2013 fn_dblog() No More? Tracking Transaction Log Activity in Denali"},"content":{"rendered":"

I bet that made you look didn\u2019t it?  Worry not, fn_dblog() still exists in SQL Server Denali, and I plan on using it to validate the information being returned by a new Event in SQL Server Denali CTP1, sqlerver.transaction_log, which brings with it the ability to correlate specific transaction log entries to the operations that actually caused them to occur.<\/p>\n

There is no greater source of information about the transaction log in SQL Server than Paul Randal\u2019s blog category Transaction Log<\/a>.  It is also listed as the referenced pre-reading material for the Microsoft SQL Server MCM<\/a> program Logging, Recovery, Log Maintenance topic.  In a number of his blog posts, Paul shows how to look at the transaction log by using an undocumented system function fn_dblog().  Note that I said it is undocumented, meaning its use is not supported by Microsoft, its functionality is subject to change at any point in time without notification, and its use is at your own risk.  Is it safe to use?  That\u2019s a topic that is up for debate, but at the end of the day if you were to have a problem associated with its use you are on your own because its undocumented.<\/p>\n

Why does any of this matter?  It matters because there is a lot of information that we can learn about the internal operations of SQL Server from the log operations that occur as the result of changes in our database.  Some examples of this would be:<\/p>\n

A SQL Server DBA myth a day: (19\/30) TRUNCATE TABLE is non-logged<\/a>
Benchmarking: 1-TB table population (part 2: optimizing log block IO size and how log IO works)<\/a>
Lock logging and fast recovery<\/a>
How do checkpoints work and what gets logged<\/a>
Finding out who dropped a table using the transaction log<\/a><\/p>\n

Admittedly this isn\u2019t necessarily information that you would start a conversation with at a party, unless of course you are surrounded by other ubergeeks<\/strike> SQL Server internal junkies, and its not really the kind of information that I use day to day in my work as a Database Administrator.  Prior to the introduction of Extended Events, some information about how SQL Server operated was only available inside of the transaction log records, and I am sure that there are still some items that you can only see inside of the log records.  Microsoft obviously recognized a demand to look the log operations generated by SQL Server in a supported fashion and added this functionality to Extended Events.<\/p>\n

The sqlserver.transaction_log Event returns 10 data elements in its payload, which can be found in the sys.dm_xe_object_columns DMV.<\/p>\n

\n
SELECT \r\n    <\/span>oc.<\/span>name, \r\n    <\/span>oc.<\/span>type_name<\/span>, \r\n    <\/span>oc.<\/span>description\r\nFROM <\/span>sys<\/span>.<\/span>dm_xe_packages <\/span>AS <\/span>p\r\nINNER JOIN <\/span>sys<\/span>.<\/span>dm_xe_objects <\/span>AS <\/span>o\r\n    ON <\/span>p.<\/span>guid <\/span>= <\/span>o.<\/span>package_guid\r\nINNER JOIN <\/span>sys<\/span>.<\/span>dm_xe_object_columns <\/span>AS <\/span>oc\r\n    ON <\/span>oc.<\/span>object_name <\/span>= <\/span>o.<\/span>name\r\n        AND <\/span>oc.<\/span>object_package_guid = <\/span>o.<\/span>package_guid\r\nWHERE <\/span>o.<\/span>name = <\/span>'transaction_log'\r\n  <\/span>AND <\/span>oc.<\/span>column_type = <\/span>'data'<\/span><\/pre>\n<\/blockquote>\n
\n
\"image\"<\/a> <\/p>\n<\/blockquote>\n
The operation and context elements have corresponding Maps in sys.dm_xe_map_values that provide the different Log Operations and Contexts that are available through Extended Events.  There are currently 70 Log Operations and 29 Contexts for those operations available in SQL Server Denali CTP1.  <\/p>\n
\n
SELECT \r\n    <\/span>name,\r\n    <\/span>map_key,\r\n    <\/span>map_value\r\nFROM <\/span>sys<\/span>.<\/span>dm_xe_map_values\r\n<\/span>WHERE <\/span>name in (<\/span>'log_op'<\/span>, <\/span>'log_context'<\/span>)\r\n<\/span>ORDER BY <\/span>name, <\/span>map_key<\/pre>\n
  <\/a><\/a><\/p><\/blockquote>\n
To show how this Event can be used, we\u2019ll first create a database named TransactionLogDemo, and then switch our connection to that database.  We\u2019ll then create an table that will be used to generate some Transaction Log events.  We\u2019ll create our Event Session to capture the sqlserver.sql_statement_starting, sqlserver.sql_statement_completed, and sqlserver.transaction_log Events and we\u2019ll add a Predicate to each Event to only fire for the TransactionLogDemo database.  To add the Predicate dynamically, we\u2019ll use Dynamic SQL to create our Event Session since inline parameters cannot be used in the CREATE EVENT SESSION DDL.  <\/p>\n
\n
CREATE DATABASE <\/span>TransactionLogDemo\r\nGO\r\nUSE <\/span>TransactionLogDemo\r\nGO\r\nCREATE TABLE <\/span>CreateLogRecords\r\n(<\/span>RowID int identity primary key<\/span>,\r\n <\/span>RowData nvarchar<\/span>(<\/span>120))\r\n<\/span>GO\r\nDECLARE <\/span>@sqlcmd nvarchar<\/span>(<\/span>2000) = <\/span>'\r\nCREATE EVENT SESSION TransactionLogDemo\r\nON SERVER\r\nADD EVENT sqlserver.page_reference_tracker,\r\nADD EVENT sqlserver.sql_statement_starting\r\n( ACTION(sqlserver.sql_text)\r\n  WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')\r\n),\r\nADD EVENT sqlserver.sql_statement_completed\r\n( ACTION(sqlserver.sql_text)\r\n  WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>')\r\n),\r\nADD EVENT sqlserver.transaction_log\r\n( WHERE (sqlserver.database_id = '<\/span>+ <\/span>cast<\/span>(<\/span>DB_ID<\/span>() <\/span>as varchar<\/span>(<\/span>3))+<\/span>'))\r\nADD TARGET package0.asynchronous_file_target(\r\n     SET filename=''C:\\SQLBlog\\TransactionLogDemoDenali.xel'',\r\n         metadatafile=''C:\\SQLBlog\\TransactionLogDemoDenali.xem'')\r\nWITH (MAX_MEMORY = 4MB, EVENT_RETENTION_MODE = NO_EVENT_LOSS, TRACK_CAUSALITY = ON )'\r\n<\/span>EXEC <\/span>(<\/span>@sqlcmd)\r\n<\/span>GO\r\nCHECKPOINT\r\nGO\r\n<\/span>-- Start the Event Session\r\n<\/span>ALTER EVENT SESSION <\/span>TransactionLogDemo\r\nON SERVER\r\nSTATE<\/span>=<\/span>START\r\nGO<\/span><\/pre>\n
  <\/a><\/p><\/blockquote>\n
Once the Event Session is created, we\u2019ll call CHECKPOINT on the database so that the log can truncate (you did create the database in SIMPLE recovery right?) and clear allowing our later call to fn_dblog() to only return the log records specific to the operations that occur after the CHECKPOINT.  We\u2019ll start our Event Session, and then insert 20 rows into the CreateLogRecords table, and then immediately delete all of the rows from the table, and stop our Event Session to end the collection of Events.<\/p>\n
\n
INSERT INTO <\/span>CreateLogRecords (<\/span>RowData)\r\n<\/span>SELECT <\/span>REPLICATE<\/span>(<\/span>'abc123'<\/span>, <\/span>20)\r\n<\/span>FROM master<\/span>.<\/span>dbo.<\/span>spt_values a\r\nWHERE <\/span>a.<\/span>type <\/span>= <\/span>'P'\r\n  <\/span>AND <\/span>a.<\/span>number < <\/span>20\r\nGO\r\nDELETE <\/span>CreateLogRecords\r\nGO\r\n<\/span>-- Disable the Event Session\r\n<\/span>ALTER EVENT SESSION <\/span>TransactionLogDemo\r\nON SERVER\r\nSTATE<\/span>=<\/span>STOP\r\nGO<\/span><\/pre>\n
  <\/a><\/p><\/blockquote>\n
Once this is done, we can now query the package0.asynchronous_file_target to get our Event data from Extended Events, and then at the same time query fn_dblog() to get the log records from the Transaction Log as well so that we can validate what we\u2019ve collected in our Event Session.<\/p>\n
\n
-- Fetch the Event Data from the Event Session Target\r\n<\/span>SELECT \r\n    <\/span>XEvent.<\/span>value(<\/span>'(event\/@name)[1]'<\/span>, <\/span>'varchar(50)'<\/span>) <\/span>AS <\/span>event_name,\r\n    <\/span>DATEADD<\/span>(<\/span>hh, \r\n            <\/span>DATEDIFF<\/span>(<\/span>hh, <\/span>GETUTCDATE<\/span>(), <\/span>CURRENT_TIMESTAMP<\/span>), \r\n            <\/span>XEvent.<\/span>value(<\/span>'(event\/@timestamp)[1]'<\/span>, <\/span>'datetime2'<\/span>)) <\/span>AS <\/span>[timestamp],\r\n    <\/span>COALESCE<\/span>(<\/span>XEvent.<\/span>value(<\/span>'(event\/data[@name="database_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>), \r\n             <\/span>XEvent.<\/span>value(<\/span>'(event\/action[@name="database_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>)) <\/span>AS <\/span>database_id,\r\n    <\/span>XEvent.<\/span>value(<\/span>'(event\/data[@name="index_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[index_id],\r\n    <\/span>XEvent.<\/span>value(<\/span>'(event\/data[@name="object_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[object_id],\r\n    <\/span>XEvent.<\/span>value(<\/span>'(event\/data[@name="transaction_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[transaction_id],\r\n    <\/span>XEvent.<\/span>value(<\/span>'(event\/data[@name="log_record_size"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[log_record_size],\r\n    <\/span>XEvent.<\/span>value(<\/span>'(event\/data[@name="operation"]\/text)[1]'<\/span>, <\/span>'varchar(50)'<\/span>) <\/span>AS <\/span>[operation],\r\n    <\/span>XEvent.<\/span>value(<\/span>'(event\/data[@name="context"]\/text)[1]'<\/span>, <\/span>'varchar(50)'<\/span>) <\/span>AS <\/span>[context],\r\n    <\/span>XEvent.<\/span>value(<\/span>'(event\/data[@name="transaction_start_time"]\/value)[1]'<\/span>, <\/span>'datetime2'<\/span>) <\/span>AS <\/span>[transaction_start_time],\r\n    <\/span>XEvent.<\/span>value(<\/span>'(event\/data[@name="replication_command"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[replication_command],\r\n    <\/span>XEvent.<\/span>value(<\/span>'(event\/action[@name="sql_text"]\/value)[1]'<\/span>, <\/span>'varchar(1000)'<\/span>) <\/span>AS <\/span>[sql_text],\r\n    <\/span>CAST<\/span>(<\/span>SUBSTRING<\/span>(<\/span>XEvent.<\/span>value(<\/span>'(event\/action[@name="attach_activity_id"]\/value)[1]'<\/span>, <\/span>'varchar(50)'<\/span>), <\/span>1, <\/span>36) <\/span>AS uniqueidentifier<\/span>) <\/span>as <\/span>activity_id,\r\n    <\/span>CAST<\/span>(<\/span>SUBSTRING<\/span>(<\/span>XEvent.<\/span>value(<\/span>'(event\/action[@name="attach_activity_id"]\/value)[1]'<\/span>, <\/span>'varchar(50)'<\/span>), <\/span>38, <\/span>10) <\/span>AS int<\/span>) <\/span>as <\/span>event_sequence\r\nFROM <\/span>(\r\n    <\/span>SELECT <\/span>CAST<\/span>(<\/span>event_data AS XML<\/span>) <\/span>AS <\/span>XEvent\r\n    FROM <\/span>sys<\/span>.<\/span>fn_xe_file_target_read_file(<\/span>'C:\\SQLBlog\\TransactionLogDemoDenali*.xel'<\/span>, <\/span>'C:\\SQLBlog\\TransactionLogDemoDenali*.xem'<\/span>, null, null)) <\/span>as <\/span>src\r\nORDER BY \r\n    <\/span>DATEADD<\/span>(<\/span>hh, \r\n            <\/span>DATEDIFF<\/span>(<\/span>hh, <\/span>GETUTCDATE<\/span>(), <\/span>CURRENT_TIMESTAMP<\/span>), \r\n            <\/span>XEvent.<\/span>value(<\/span>'(event\/@timestamp)[1]'<\/span>, <\/span>'datetime2'<\/span>)),\r\n    <\/span>CAST<\/span>(<\/span>SUBSTRING<\/span>(<\/span>XEvent.<\/span>value(<\/span>'(event\/action[@name="attach_activity_id"]\/value)[1]'<\/span>, <\/span>'varchar(50)'<\/span>), <\/span>1, <\/span>36) <\/span>AS uniqueidentifier<\/span>),\r\n    <\/span>CAST<\/span>(<\/span>SUBSTRING<\/span>(<\/span>XEvent.<\/span>value(<\/span>'(event\/action[@name="attach_activity_id"]\/value)[1]'<\/span>, <\/span>'varchar(50)'<\/span>), <\/span>38, <\/span>10) <\/span>AS int<\/span>)\r\n<\/span>GO\r\n\r\n<\/span>-- Fetch Log records from log for comparison\r\n<\/span>SELECT \r\n    <\/span>[Xact ID] as <\/span>transaction_id, \r\n    <\/span>[Log Record Fixed Length] as <\/span>log_record_size, \r\n    <\/span>[Operation] as <\/span>operation, \r\n    <\/span>SUBSTRING<\/span>(<\/span>[context], <\/span>5, <\/span>LEN<\/span>(<\/span>context)) <\/span>as <\/span>context, \r\n    <\/span>[Begin Time]\r\nFROM <\/span>fn_dblog<\/span>(null, null) \r\n<\/span>GO<\/span><\/pre>\n
  <\/a><\/a><\/a><\/p><\/blockquote>\n
\n
\"image\"<\/a> <\/p>\n<\/blockquote>\n
When we look at the output of the above two queries, first you\u2019ll note that there are four log records that don\u2019t have associated records in our Extended Event Session.  These are the log records generated by the CHECKPOINT operation and the ALTER EVENT SESSION command and occurred before the Event Session was actually collecting data.  The first two LOP_BEGIN_XACT records in the Event Session correspond to the transaction_id of the log records returned in rows 5 and 6 of the fn_dblog() output, but if you notice the Event Session is missing the transaction_start_time for the log operations, something I believe to be a bug in Denali CTP1 and which I\u2019ve submitted a Connect item for (Denali – Transaction_Log Extended Event Returning Incorrect Data<\/a>).  <\/p>\n
On quick glance it appears that all of our log records are in the same order, but if we look at more closely, there is a LOP_MODIFY_ROW operation that is missing from our Event Session, but exists inside of the fn_dblog() output.<\/p>\n
\n
\"image\"<\/a> <\/p>\n<\/blockquote>\n
If you scroll down further, you\u2019ll also see that there are two missing log records for the delete as well, LOP_MODIFY_HEAP and LOP_SET_BITS with a context of PFS.  However, the Extended Event Session captures the lock logging for the ALTER EVENT SESSION command that stopped the Event collection, whereas the output from fn_dblog() does not show that.<\/p>\n
To cleanup from this Event Session, the following code can be run.<\/p>\n
\n
USE master\r\nGO\r\nDROP DATABASE <\/span>TransactionLogDemo\r\nGO\r\nDROP EVENT SESSION <\/span>TransactionLogDemo\r\nON SERVER\r\nGO\r\nEXECUTE <\/span>sp_configure <\/span>'show advanced options'<\/span>, <\/span>1\r\nGO\r\nRECONFIGURE\r\nGO\r\nEXECUTE <\/span>sp_configure <\/span>'xp_cmdshell'<\/span>, <\/span>1\r\nGO\r\nRECONFIGURE\r\nGO\r\nEXEC <\/span>xp_cmdshell <\/span>'DEL C:\\SQLBlog\\TransactionLogDemoDenali*'\r\n<\/span>GO\r\nEXECUTE <\/span>sp_configure <\/span>'xp_cmdshell'<\/span>, <\/span>0\r\nGO\r\nRECONFIGURE\r\nGO\r\nEXECUTE <\/span>sp_configure <\/span>'show advanced options'<\/span>, <\/span>0\r\nGO\r\nRECONFIGURE\r\nGO<\/span><\/pre>\n
  <\/a><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"
I bet that made you look didn\u2019t it?  Worry not, fn_dblog() still exists in SQL Server Denali, and I plan on using it to validate the information being returned by a new Event in SQL Server Denali CTP1, sqlerver.transaction_log, which brings with it the ability to correlate specific transaction log entries to the operations that […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23,38,45],"tags":[],"class_list":["post-542","post","type-post","status-publish","format-standard","hentry","category-extended-events","category-sql-server-2008","category-xevent-a-day-series"],"yoast_head":"\nAn XEvent a Day (22 of 31) \u2013 The Future \u2013 fn_dblog() No More? Tracking Transaction Log Activity in Denali - Jonathan Kehayias<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"An XEvent a Day (22 of 31) \u2013 The Future \u2013 fn_dblog() No More? Tracking Transaction Log Activity in Denali - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"I bet that made you look didn\u2019t it?  Worry not, fn_dblog() still exists in SQL Server Denali, and I plan on using it to validate the information being returned by a new Event in SQL Server Denali CTP1, sqlerver.transaction_log, which brings with it the ability to correlate specific transaction log entries to the operations that […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2010-12-22T10:38:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:18:30+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday22of31thefuturefn_dblognomor\/038a9711\/image_thumb.png\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"An XEvent a Day (22 of 31) \u2013 The Future \u2013 fn_dblog() No More? Tracking Transaction Log Activity in Denali\",\"datePublished\":\"2010-12-22T10:38:00+00:00\",\"dateModified\":\"2017-04-13T16:18:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\\\/\"},\"wordCount\":1003,\"commentCount\":8,\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday22of31thefuturefn_dblognomor\\\/038a9711\\\/image_thumb.png\",\"articleSection\":[\"Extended Events\",\"SQL Server 2008\",\"XEvent a Day Series\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\\\/\",\"name\":\"An XEvent a Day (22 of 31) \u2013 The Future \u2013 fn_dblog() No More? Tracking Transaction Log Activity in Denali - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday22of31thefuturefn_dblognomor\\\/038a9711\\\/image_thumb.png\",\"datePublished\":\"2010-12-22T10:38:00+00:00\",\"dateModified\":\"2017-04-13T16:18:30+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday22of31thefuturefn_dblognomor\\\/038a9711\\\/image_thumb.png\",\"contentUrl\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windowslivewriter\\\/anxeventaday22of31thefuturefn_dblognomor\\\/038a9711\\\/image_thumb.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Extended Events\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/extended-events\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"An XEvent a Day (22 of 31) \u2013 The Future \u2013 fn_dblog() No More? Tracking Transaction Log Activity in Denali\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"An XEvent a Day (22 of 31) \u2013 The Future \u2013 fn_dblog() No More? Tracking Transaction Log Activity in Denali - Jonathan Kehayias","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/","og_locale":"en_US","og_type":"article","og_title":"An XEvent a Day (22 of 31) \u2013 The Future \u2013 fn_dblog() No More? Tracking Transaction Log Activity in Denali - Jonathan Kehayias","og_description":"I bet that made you look didn\u2019t it?  Worry not, fn_dblog() still exists in SQL Server Denali, and I plan on using it to validate the information being returned by a new Event in SQL Server Denali CTP1, sqlerver.transaction_log, which brings with it the ability to correlate specific transaction log entries to the operations that […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/","og_site_name":"Jonathan Kehayias","article_published_time":"2010-12-22T10:38:00+00:00","article_modified_time":"2017-04-13T16:18:30+00:00","og_image":[{"url":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday22of31thefuturefn_dblognomor\/038a9711\/image_thumb.png","type":"","width":"","height":""}],"author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"An XEvent a Day (22 of 31) \u2013 The Future \u2013 fn_dblog() No More? Tracking Transaction Log Activity in Denali","datePublished":"2010-12-22T10:38:00+00:00","dateModified":"2017-04-13T16:18:30+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/"},"wordCount":1003,"commentCount":8,"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/#primaryimage"},"thumbnailUrl":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday22of31thefuturefn_dblognomor\/038a9711\/image_thumb.png","articleSection":["Extended Events","SQL Server 2008","XEvent a Day Series"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/","name":"An XEvent a Day (22 of 31) \u2013 The Future \u2013 fn_dblog() No More? Tracking Transaction Log Activity in Denali - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/#primaryimage"},"thumbnailUrl":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday22of31thefuturefn_dblognomor\/038a9711\/image_thumb.png","datePublished":"2010-12-22T10:38:00+00:00","dateModified":"2017-04-13T16:18:30+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/#primaryimage","url":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday22of31thefuturefn_dblognomor\/038a9711\/image_thumb.png","contentUrl":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windowslivewriter\/anxeventaday22of31thefuturefn_dblognomor\/038a9711\/image_thumb.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-22-of-31-the-future-fn_dblog-no-more-tracking-transaction-log-activity-in-denali\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Extended Events","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/extended-events\/"},{"@type":"ListItem","position":3,"name":"An XEvent a Day (22 of 31) \u2013 The Future \u2013 fn_dblog() No More? Tracking Transaction Log Activity in Denali"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/542","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=542"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/542\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=542"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=542"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=542"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}