{"id":4612,"date":"2016-05-01T09:45:15","date_gmt":"2016-05-01T16:45:15","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4612"},"modified":"2016-05-02T11:08:15","modified_gmt":"2016-05-02T18:08:15","slug":"code-to-analyze-the-transaction-hierarchy-in-the-log","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-analyze-the-transaction-hierarchy-in-the-log\/","title":{"rendered":"Code to analyze the transaction hierarchy in the log"},"content":{"rendered":"<p>Over the weekend there was a discussion on the MVP distribution list about the <em>sys.dm_tran_database_transactions<\/em> DMV and how one cannot use it to accurately determine how much log an\u00a0operation\u00a0has generated because it doesn&#8217;t provide a roll-up of the sub-transaction metrics to the outer transaction. This makes the output somewhat non-intuitive.<\/p>\n<p>The discussion prompted me to write some code I&#8217;ve been meaning to do since 2012, when SQL Server 2012 introduced a field in <em>LOP_BEGIN_XACT<\/em> log records that tracks the transaction ID of the parent transaction, allowing the hierarchy of transactions to be investigated.<\/p>\n<p>The actual code is at the bottom of the article, and is available in a <a href=\"https:\/\/www.sqlskills.com\/External\/sp_SQLskillsAnalyzeLog.zip\" target=\"_blank\">zip file here<\/a>.<\/p>\n<p>It provides two stored procs, <em>sp_SQLskillsAnalyzeLog<\/em> and <em>sp_SQLskillsAnalyzeLogInner<\/em>, with the former making use of the latter, and the latter calling itself recursively.<\/p>\n<p>The <em>sp_SQLskillsAnalyzeLog<\/em> proc will dump the hierarchy of transactions in the transaction log. By default it will only show the top-level transactions (with no parent transaction), and it has the following parameters:<\/p>\n<ul>\n<li>@DBName (with a default of master)<\/li>\n<li>@Detailed (default 0, when 1 it will shows the transaction begin time and Windows login, for top-level transactions only)<\/li>\n<li>@Deep (default 0, when 1 it will show the sub-transaction hiearchy)<\/li>\n<li>@PrintOption (default 0 for a resultset, 1 for textual output)<\/li>\n<\/ul>\n<p>I&#8217;ve set the procs to be in <em>master<\/em> and system objects using\u00a0<em>sp_MS_marksystemobject<\/em>. You can change them to be stored wherever you want.<\/p>\n<p>The pseudo-code is as follows:<\/p>\n<ul>\n<li>Get the info from the log into temp table 1<\/li>\n<li>Create temp table 2 with a clustered index on an identity column<\/li>\n<li>For each top-level transaction\n<ul>\n<li>If @Detailed, add the user name and start time<\/li>\n<li>Get the last transaction added to temp table 2<\/li>\n<li>If it&#8217;s the same as the one we&#8217;re about to add, increment the counter for the last one added, else add the new one<\/li>\n<li>if @Deep, then, with recursion depth = 1,\n<ul>\n<li>**RP** for each sub-transaction of current next-level up transaction\n<ul>\n<li>Prefix &#8216;&#8230;&#8217; x the recursion depth to the transaction name<\/li>\n<li>Get the last transaction added to temp table 2<\/li>\n<li>If it&#8217;s the same as the one we&#8217;re about to add, increment the counter for the last one added, else add the new one<\/li>\n<li>Recurse to **RP**, increasing recursion depth<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<li>(doing it this way vastly reduces the amount of data to be stored in temp table 2)<\/li>\n<\/ul>\n<\/li>\n<li>select the result set or print it, depending on @PrintOption<\/li>\n<\/ul>\n<p>Let&#8217;s look at an example, using the <em>SalesDB<\/em> database that you can restore from a zip file on our <a href=\"https:\/\/www.sqlskills.com\/sql-server-resources\/sql-server-demos\/\" target=\"_blank\">resources page<\/a>:<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n-- Restore the database\r\nUSE &#x5B;master];\r\nGO\r\nALTER DATABASE &#x5B;SalesDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;\r\nGO\r\nRESTORE DATABASE &#x5B;SalesDB]\r\n\tFROM DISK = N'D:\\SQLskills\\DemoBackups\\SalesDB2014.bak'\r\nWITH STATS = 10, REPLACE;\r\nGO\r\n\r\nALTER DATABASE &#x5B;SalesDB] SET RECOVERY SIMPLE;\r\nGO\r\n\r\n-- Create a smaller copy of the Sales table\r\nUSE &#x5B;SalesDB];\r\nGO\r\n\r\nSELECT *\r\nINTO &#x5B;SalesCopy]\r\nFROM &#x5B;Sales]\r\nWHERE &#x5B;SalesID] &lt; 100000;\r\nGO\r\n\r\nCREATE CLUSTERED INDEX &#x5B;SalesCopy_CL] ON &#x5B;SalesCopy] (&#x5B;SalesID]);\r\nGO\r\n\r\n-- Empty the log\r\nCHECKPOINT;\r\nGO\r\n\r\n-- Online rebuild the clustered index\r\nALTER INDEX &#x5B;SalesCopy_CL] ON &#x5B;SalesCopy] REBUILD WITH (ONLINE = ON);\r\nGO\r\n\r\n-- Analyze the log\r\nEXEC sp_SQLskillsAnalyzeLog salesdb, @Detailed = 1, @Deep = 1, @PrintOption = 1;\r\nGO\r\n<\/pre>\n<pre class=\"brush: plain; gutter: false; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\nALTER INDEX by APPLECROSS\\Paul @ 2016\/05\/01 11:26:48:113\r\n...ONLINE_INDEX_DDL 2 times\r\nOnlineIndexInsertTxn by APPLECROSS\\Paul @ 2016\/05\/01 11:26:48:113\r\n...BTree Split\/Shrink\r\n...BulkExtentAlloc\r\n...SplitPage\r\n...BulkExtentAlloc\r\n...SplitPage\r\n...BTree Split\/Shrink\r\n...BulkExtentAlloc\r\n...SplitPage\r\n...BulkExtentAlloc\r\n...SplitPage 85 times\r\nAllocate Root by APPLECROSS\\Paul @ 2016\/05\/01 11:26:48:113\r\n...AllocFirstPage\r\nAllocate Root by APPLECROSS\\Paul @ 2016\/05\/01 11:26:48:113\r\n...AllocFirstPage\r\nOnlineIndexInsertTxn by APPLECROSS\\Paul @ 2016\/05\/01 11:26:48:150\r\n...SplitPage\r\n...BulkExtentAlloc\r\n...SplitPage\r\n...BulkExtentAlloc\r\n...SplitPage 86 times\r\n...BulkExtentAlloc\r\n...SplitPage 89 times\r\n...BulkExtentAlloc\r\n...SplitPage 57 times\r\n...BulkExtentAlloc\r\n...SplitPage 31 times\r\n...BulkExtentAlloc\r\n...SplitPage 88 times\r\n...BulkExtentAlloc\r\n...SplitPage 52 times\r\nSetFileSize @ 2016\/05\/01 11:26:48:303\r\n<\/pre>\n<p>Pretty cool, eh? You can see that the online rebuild uses a bunch of top-level transactions, which makes it difficult to determine exactly how much transaction log it generated as there isn&#8217;t one transaction that then drives everything else. But using this script, now you can see what an operation does.<\/p>\n<p>There are other uses of this too:<\/p>\n<ul>\n<li>Searching through the log to see who&#8217;s doing what<\/li>\n<li>Analysis of your stored proc transactions and what they cause to happen under the covers on the system (e.g. page splits)<\/li>\n<\/ul>\n<p>I hope you find this useful! Let me know if there are any other features you&#8217;d like to see and I&#8217;ll figure out if they&#8217;re possible and feasible. I can think of at least:<\/p>\n<ul>\n<li>Making it work on log backups<\/li>\n<li>Providing a roll-up of log space used for transactions and their sub-transactions (would be pretty slow, but do-able)<\/li>\n<\/ul>\n<p>Enjoy!<\/p>\n<p>Here&#8217;s the code, and it&#8217;s in the zip file <a href=\"https:\/\/www.sqlskills.com\/External\/sp_SQLskillsAnalyzeLog.zip\" target=\"_blank\">here<\/a>. I&#8217;m sure there are probably some ways to make this code more efficient, I&#8217;m not an expert T-SQL programmer :-)<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n\/*============================================================================\r\n  File:     sp_SQLskillsAnalyzeLog.sql\r\n\r\n  Summary:  This script cracks the transaction log and prints a hierarchy of\r\n\t\t\ttransactions\r\n\r\n  SQL Server Versions: 2012 onwards\r\n------------------------------------------------------------------------------\r\n  Written by Paul S. Randal, SQLskills.com\r\n\r\n  (c) 2016, SQLskills.com. All rights reserved.\r\n\r\n  For more scripts and sample code, check out \r\n    http:\/\/www.SQLskills.com\r\n\r\n  You may alter this code for your own *non-commercial* purposes. You may\r\n  republish altered code as long as you include this copyright and give due\r\n  credit, but you must obtain prior permission before blogging this code.\r\n  \r\n  THIS CODE AND INFORMATION ARE PROVIDED &quot;AS IS&quot; WITHOUT WARRANTY OF \r\n  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED \r\n  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND\/OR FITNESS FOR A\r\n  PARTICULAR PURPOSE.\r\n============================================================================*\/\r\n\r\nUSE &#x5B;master];\r\nGO\r\n\r\nIF OBJECT_ID (N'sp_SQLskillsAnalyzeLog') IS NOT NULL\r\n\tDROP PROCEDURE &#x5B;sp_SQLskillsAnalyzeLog];\r\nGO\r\n\r\nIF OBJECT_ID (N'sp_SQLskillsAnalyzeLogInner') IS NOT NULL\r\n\tDROP PROCEDURE &#x5B;sp_SQLskillsAnalyzeLogInner];\r\nGO\r\n\r\nCREATE PROCEDURE sp_SQLskillsAnalyzeLogInner (\r\n\t@XactID AS CHAR (13),\r\n\t@Depth AS INT)\r\nAS\r\nBEGIN\r\n\tDECLARE @String VARCHAR (8000);\r\n\tDECLARE @InsertString VARCHAR (8000);\r\n\tDECLARE @Name VARCHAR (256);\r\n\tDECLARE @ID INT;\r\n\r\n\tDECLARE @SubXactID CHAR (13);\r\n\tDECLARE @SubDepth INT = @Depth + 3;\r\n\r\n\tDECLARE &#x5B;LogAnalysisX] CURSOR FAST_FORWARD LOCAL FOR\r\n\tSELECT &#x5B;Transaction ID], &#x5B;Transaction Name]\r\n\tFROM ##SQLskills_Log_Analysis\r\n\tWHERE &#x5B;Parent Transaction ID] = @XactID;\r\n\r\n\tOPEN &#x5B;LogAnalysisX];\r\n\r\n\tFETCH NEXT FROM &#x5B;LogAnalysisX] INTO @SubXactID, @Name;\r\n\r\n\tWHILE @@FETCH_STATUS = 0\r\n\tBEGIN\r\n\t\tSELECT @InsertString = REPLICATE ('.', @Depth) + @Name;\r\n\r\n\t\t-- Select the last transaction name inserted into the table\r\n\t\tSELECT TOP 1\r\n\t\t\t@ID = &#x5B;ID],\r\n\t\t\t@String = &#x5B;XactName]\r\n\t\tFROM\r\n\t\t\t##SQLskills_Log_Analysis2\r\n\t\tORDER BY &#x5B;ID] DESC;\r\n\r\n\t\tIF @String = @InsertString\r\n\t\t\tUPDATE\r\n\t\t\t\t##SQLskills_Log_Analysis2\r\n\t\t\tSET\r\n\t\t\t\t&#x5B;Times] = &#x5B;Times] + 1\r\n\t\t\tWHERE\r\n\t\t\t\t&#x5B;ID] = @ID;\r\n\t\tELSE\r\n\t\t\tINSERT INTO ##SQLskills_Log_Analysis2\r\n\t\t\tVALUES (@InsertString, 1);\r\n\r\n\t\t-- Recurse...\r\n\t\tEXEC sp_SQLskillsAnalyzeLogInner @SubXactID, @SubDepth;\r\n\r\n\t\tFETCH NEXT FROM &#x5B;LogAnalysisX] INTO @SubXactID, @Name;\r\n\tEND;\r\n\r\n\tCLOSE &#x5B;LogAnalysisX];\r\n\tDEALLOCATE &#x5B;LogAnalysisX];\r\nEND\r\nGO\r\n\r\nCREATE PROCEDURE sp_SQLskillsAnalyzeLog (\r\n\t-- The name of a database, default of master\r\n\t@DBName AS sysname = N'master',\r\n\r\n\t-- Detailed = 0 means just the transaction name\r\n\t-- Detailed = 1 means time and user\r\n\t@Detailed AS INT = 0,\r\n\r\n\t-- Deep = 0 means only the top-level transactions\r\n\t-- Deep = 1 means sub-transaction hierarchy (slow!)\r\n\t@Deep AS INT = 0,\r\n\r\n\t-- PrintOption = 0 means SELECT as a resultset\r\n\t-- PrintOption = 1 means PRINT as text\r\n\t@PrintOption VARCHAR (25) = 0)\r\nAS\r\nBEGIN\r\n\tSET NOCOUNT ON;\r\n\r\n\tIF EXISTS (SELECT * FROM &#x5B;tempdb].&#x5B;sys].&#x5B;objects]\r\n\t\tWHERE &#x5B;name] = N'##SQLskills_Log_Analysis')\r\n\t\tDROP TABLE &#x5B;##SQLskills_Log_Analysis];\r\n\r\n\tIF EXISTS (SELECT * FROM &#x5B;tempdb].&#x5B;sys].&#x5B;objects]\r\n\t\tWHERE &#x5B;name] = N'##SQLskills_Log_Analysis2')\r\n\t\tDROP TABLE &#x5B;##SQLskills_Log_Analysis2];\r\n\r\n\t-- Only get the detailed info if we need it\r\n\tIF @Detailed = 1\r\n\t\tEXEC ('USE ' + @DBName + ';' +\r\n\t\t\t'SELECT &#x5B;Transaction ID], &#x5B;Transaction Name], &#x5B;Parent Transaction ID],' +\r\n\t\t\t'&#x5B;Begin Time], SUSER_SNAME (&#x5B;Transaction SID]) AS &#x5B;Who] ' +\r\n\t\t\t'INTO ##SQLskills_Log_Analysis FROM fn_dblog (null,null) ' +\r\n\t\t\t'WHERE &#x5B;Operation] = ''LOP_BEGIN_XACT'';');\r\n\tELSE\r\n\t\tEXEC ('USE ' + @DBName + ';' +\r\n\t\t\t'SELECT &#x5B;Transaction ID], &#x5B;Transaction Name], &#x5B;Parent Transaction ID],' +\r\n\t\t\t'NULL AS &#x5B;Begin Time], NULL AS &#x5B;Who]' +\r\n\t\t\t'INTO ##SQLskills_Log_Analysis FROM fn_dblog (null,null) ' +\r\n\t\t\t'WHERE &#x5B;Operation] = ''LOP_BEGIN_XACT'';');\r\n\t\r\n\r\n\tCREATE TABLE ##SQLskills_Log_Analysis2 (\r\n\t\t&#x5B;ID]\t\tINT IDENTITY,\r\n\t\t&#x5B;XactName]\tVARCHAR (8000),\r\n\t\t&#x5B;Times]\t\tINT);\r\n\r\n\tCREATE CLUSTERED INDEX &#x5B;ID_CL]\r\n\tON ##SQLskills_Log_Analysis2 (&#x5B;ID]);\r\n\r\n\t-- Insert a dummy row to make the loop logic simpler\r\n\tINSERT INTO ##SQLskills_Log_Analysis2\r\n\tVALUES ('PSRDummy', 1);\r\n\r\n\t-- Calculate the transaction hierarchy\r\n\tDECLARE @XactID\t\tCHAR (13);\r\n\tDECLARE @Name\t\tVARCHAR (256);\r\n\tDECLARE @Begin\t\tVARCHAR (100);\r\n\tDECLARE @Who\t\tVARCHAR (100);\r\n\tDECLARE @String\t\tVARCHAR (8000);\r\n\tDECLARE @ID\t\t\tINT;\r\n\tDECLARE @Counter\tINT;\r\n\r\n\tDECLARE &#x5B;LogAnalysis] CURSOR FAST_FORWARD FOR\r\n\tSELECT\r\n\t\t&#x5B;Transaction ID], &#x5B;Transaction Name], &#x5B;Begin Time], &#x5B;Who]\r\n\tFROM\r\n\t\t##SQLskills_Log_Analysis\r\n\tWHERE\r\n\t\t&#x5B;Parent Transaction ID] IS NULL;\r\n\r\n\tOPEN &#x5B;LogAnalysis];\r\n\r\n\tFETCH NEXT FROM &#x5B;LogAnalysis] INTO @XactID, @Name, @Begin, @Who;\r\n\r\n\tWHILE @@FETCH_STATUS = 0\r\n\tBEGIN\r\n\t\t-- Select the last transaction name inserted into the table\r\n\t\tSELECT TOP 1\r\n\t\t\t@ID = &#x5B;ID],\r\n\t\t\t@String = &#x5B;XactName]\r\n\t\tFROM\r\n\t\t\t##SQLskills_Log_Analysis2\r\n\t\tORDER BY ID DESC;\r\n\r\n\t\t-- If it's the same as we're about to insert, update the counter,\r\n\t\t-- otherwise insert the new transaction name\r\n\t\tIF @String = @Name\r\n\t\t\tUPDATE\r\n\t\t\t\t##SQLskills_Log_Analysis2\r\n\t\t\tSET\r\n\t\t\t\t&#x5B;Times] = &#x5B;Times] + 1\r\n\t\t\tWHERE\r\n\t\t\t\t&#x5B;ID] = @ID;\r\n\t\tELSE\r\n\t\tBEGIN\r\n\t\t\tSELECT @String = @Name;\r\n\r\n\t\t\t-- Add detail if necessary\r\n\t\t\tIF @Detailed = 1\r\n\t\t\tBEGIN\r\n\t\t\t\t-- Do this separately in case CONCAT_NULL_YIELDS_NULL is set\r\n\t\t\t\tIF @WHO IS NOT NULL\r\n\t\t\t\t\t SELECT @String = @String + ' by ' + @Who;\r\n\r\n\t\t\t\tSELECT @String = @String + ' @ ' + @Begin;\r\n\t\t\tEND\r\n\r\n\t\t\tINSERT INTO ##SQLskills_Log_Analysis2 VALUES (@String, 1);\r\n\t\tEND\r\n\r\n\t\t-- Look for subtransactions of this one\r\n\t\tIF @Deep = 1\r\n\t\t\tEXEC sp_SQLskillsAnalyzeLogInner @XactID, 3;\r\n\r\n\t\tFETCH NEXT FROM &#x5B;LogAnalysis] INTO @XactID, @Name, @Begin, @Who;\r\n\tEND;\r\n\r\n\tCLOSE &#x5B;LogAnalysis];\r\n\tDEALLOCATE &#x5B;LogAnalysis];\r\n\r\n\t-- Discard the dummy row\r\n\tDELETE\r\n\tFROM\r\n\t\t##SQLskills_Log_Analysis2\r\n\tWHERE\r\n\t\t&#x5B;ID] = 1;\r\n\r\n\t-- Print the hierachy\r\n\tDECLARE &#x5B;LogAnalysis2] CURSOR FOR\r\n\tSELECT\r\n\t\t&#x5B;ID],\r\n\t\t&#x5B;XactName],\r\n\t\t&#x5B;Times]\r\n\tFROM\r\n\t\t##SQLskills_Log_Analysis2;\r\n\r\n\tOPEN &#x5B;LogAnalysis2];\r\n\r\n\t-- Fetch the first transaction name, if any\r\n\tFETCH NEXT FROM &#x5B;LogAnalysis2] INTO @ID, @String, @Counter;\r\n\r\n\tWHILE @@FETCH_STATUS = 0\r\n\tBEGIN\r\n\t\tIF @Counter &gt; 1\r\n\t\tBEGIN\r\n\t\t\tSELECT @String = @String + ' ' +\r\n\t\t\t\tCONVERT (VARCHAR, @Counter) + ' times';\r\n\t\tEND\r\n\t\t\r\n\t\t-- If we're going to SELECT the output, update the row\r\n\t\tIF @PrintOption = 0\r\n\t\t\tUPDATE\r\n\t\t\t\t##SQLskills_Log_Analysis2\r\n\t\t\tSET\r\n\t\t\t\t&#x5B;XactName] = @String\r\n\t\t\tWHERE\r\n\t\t\t\t&#x5B;ID] = @ID;\r\n\t\tELSE\r\n\t\t\tPRINT @String;\r\n\r\n\t\tFETCH NEXT FROM &#x5B;LogAnalysis2] INTO @ID, @String, @Counter;\r\n\tEND;\r\n\r\n\tCLOSE &#x5B;LogAnalysis2];\r\n\tDEALLOCATE &#x5B;LogAnalysis2];\r\n\r\n\tIF @PrintOption = 0\r\n\tBEGIN\r\n\t\tSELECT\r\n\t\t\t&#x5B;XactName]\r\n\t\tFROM\r\n\t\t\t##SQLskills_Log_Analysis2;\r\n\tEND\r\n\r\n\tDROP TABLE ##SQLskills_Log_Analysis;\r\n\tDROP TABLE ##SQLskills_Log_Analysis2;\r\nEND\r\nGO\r\n\r\nEXEC sys.sp_MS_marksystemobject &#x5B;sp_SQLskillsAnalyzeLog];\r\nEXEC sys.sp_MS_marksystemobject &#x5B;sp_SQLskillsAnalyzeLogInner];\r\nGO\r\n\r\n-- EXEC sp_SQLskillsAnalyzeLog salesdb, 1, 1, 1;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Over the weekend there was a discussion on the MVP distribution list about the sys.dm_tran_database_transactions DMV and how one cannot use it to accurately determine how much log an\u00a0operation\u00a0has generated because it doesn&#8217;t provide a roll-up of the sub-transaction metrics to the outer transaction. This makes the output somewhat non-intuitive. The discussion prompted me to [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38,48,98],"tags":[],"class_list":["post-4612","post","type-post","status-publish","format-standard","hentry","category-example-scripts","category-inside-the-storage-engine","category-transaction-log"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Code to analyze the transaction hierarchy in the log - Paul S. Randal<\/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\/paul\/code-to-analyze-the-transaction-hierarchy-in-the-log\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Code to analyze the transaction hierarchy in the log - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Over the weekend there was a discussion on the MVP distribution list about the sys.dm_tran_database_transactions DMV and how one cannot use it to accurately determine how much log an\u00a0operation\u00a0has generated because it doesn&#8217;t provide a roll-up of the sub-transaction metrics to the outer transaction. This makes the output somewhat non-intuitive. The discussion prompted me to [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-analyze-the-transaction-hierarchy-in-the-log\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2016-05-01T16:45:15+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-05-02T18:08:15+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\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\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-analyze-the-transaction-hierarchy-in-the-log\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-analyze-the-transaction-hierarchy-in-the-log\/\",\"name\":\"Code to analyze the transaction hierarchy in the log - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2016-05-01T16:45:15+00:00\",\"dateModified\":\"2016-05-02T18:08:15+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-analyze-the-transaction-hierarchy-in-the-log\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-analyze-the-transaction-hierarchy-in-the-log\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-analyze-the-transaction-hierarchy-in-the-log\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Code to analyze the transaction hierarchy in the log\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?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\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Code to analyze the transaction hierarchy in the log - Paul S. Randal","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\/paul\/code-to-analyze-the-transaction-hierarchy-in-the-log\/","og_locale":"en_US","og_type":"article","og_title":"Code to analyze the transaction hierarchy in the log - Paul S. Randal","og_description":"Over the weekend there was a discussion on the MVP distribution list about the sys.dm_tran_database_transactions DMV and how one cannot use it to accurately determine how much log an\u00a0operation\u00a0has generated because it doesn&#8217;t provide a roll-up of the sub-transaction metrics to the outer transaction. This makes the output somewhat non-intuitive. The discussion prompted me to [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-analyze-the-transaction-hierarchy-in-the-log\/","og_site_name":"Paul S. Randal","article_published_time":"2016-05-01T16:45:15+00:00","article_modified_time":"2016-05-02T18:08:15+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-analyze-the-transaction-hierarchy-in-the-log\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-analyze-the-transaction-hierarchy-in-the-log\/","name":"Code to analyze the transaction hierarchy in the log - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2016-05-01T16:45:15+00:00","dateModified":"2016-05-02T18:08:15+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-analyze-the-transaction-hierarchy-in-the-log\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-analyze-the-transaction-hierarchy-in-the-log\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/code-to-analyze-the-transaction-hierarchy-in-the-log\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Code to analyze the transaction hierarchy in the log"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?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\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4612","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4612"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4612\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4612"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4612"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4612"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}