{"id":553,"date":"2010-12-11T22:46:00","date_gmt":"2010-12-11T22:46:00","guid":{"rendered":"\/blogs\/jonathan\/post\/An-XEvent-a-Day-(11-of-31)-e28093-Targets-Week-e28093-Using-Multiple-Targets-to-Debug-Orphaned-Transactions.aspx"},"modified":"2017-04-13T12:17:39","modified_gmt":"2017-04-13T16:17:39","slug":"an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/","title":{"rendered":"An XEvent a Day (11 of 31) \u2013 Targets Week \u2013 Using Multiple Targets to Debug Orphaned Transactions"},"content":{"rendered":"

Yesterday\u2019s blog post Targets Week \u2013 etw_classic_sync_target<\/a> covered the ETW integration that is built into Extended Events and how the etw_classic_sync_target can be used in conjunction with other ETW traces to provide troubleshooting at a level previously not possible with SQL Server.  In today\u2019s post we\u2019ll look at how to use multiple targets to simplify analysis of Event collection.<\/p>\n

<\/h2>\n

Why Multiple Targets?<\/h2>\n

You might ask why you would want to use multiple Targets in an Event Session with Extended Events?  The best answer is because each of the Targets handles the Event data in different ways, and by combining their uses, you can easily simplify the task of tracking down problems.  Two days ago I talked about the pair_matching Target and how it only retains Events that have not been matched.  Two years ago out of nowhere, one of the production databases I supported started having transaction log growth problems.  After growing the log for the second time in a day, I started looking at the database because something was obviously not right.  <\/p>\n

The first place I looked was the sys.databases DMV and specifically the log_reuse_wait_desc column, which provides the reason that the transaction log for a database is not truncating.  When I queried this DMV, I found that the log_reuse_wait_desc was ACTIVE_TRANSACTION, meaning that the database had an open transaction.  So I ran DBCC OPENTRAN to get some more information about the open transaction (you can query the DMV\u2019s for this information as well) and found that the transaction had been open for over 8 hours.  I queried sys.dm_exec_sessions for the session_id and found that the session was still being used and had submitted a request within the last minute, so it seemed to be an orphaned transaction.<\/p>\n

\n
SELECT \r\n    <\/span>session_id,\r\n    <\/span>login_time, \r\n    <\/span>last_request_start_time, \r\n    <\/span>last_request_end_time\r\nFROM <\/span>sys<\/span>.<\/span>dm_exec_sessions\r\n<\/span>WHERE <\/span>session_id = <\/span>76<\/pre>\n<\/blockquote>\n
After discussions with the Analyst for the application and the applications vendor, it was decided that the session should be killed forcing a ROLLBACK of the transaction and allowing the log to be truncate.  (I\u2019ll discuss why this might prove be problematic later in this post)  This resolved the problem, at least until the next day when the database began running out of space in the transaction log again, and once again had an open transaction that had been open for hours on a session that was sill being used by the application.  What was really interesting was there was no correlation between the previous days open transactions begin time, and the begin time of the second occurrence of the problem, so it seemed to be a completely random occurrence which was not going to be easy to troubleshoot.  <\/p>\n
I created a server side trace and tried over the next two days to figure out what the issue actually was, but didn\u2019t make much head way until I expanded the trace to have the statement starting and completed events along with the Errors and Warnings Events in the trace.  When the problem reoccurred, I was able to read through the trace files using filtering to minimize the Trace Events captured down to the specific spid that held the open transaction and the events that occurred five minutes before and after the transaction_begin_time for the open transaction.  While looking at the event information I found an Attention Event and was able to deduce what had happened. <\/p>\n
The application was a ASP.NET application, and the vendor used the CommandTimeout default which is 30 seconds.  What happened was that a process was invoked that called a stored procedure to archive information from a transactional table into an archive table inside of the database, and the number of rows being archived caused the stored procedures execution to exceeded 30 seconds resulting in a timeout in the ASP.NET application, and the application silently handled the exception by doing nothing.  The problem was that the stored procedure issued a BEGIN TRANSACTION before archiving the rows, and when the timeout occurred and the Attention Event was raised, the command aborted leaving the open transaction and creating the problem with the log not truncating.  <\/p>\n
The connection was returned to the ASP.NET Connection Pool, and was constantly being reused by the application to do who knows what other operations, which is where killing the connection was potentially a very bad thing to do.  All of the activity performed by this session was performed under the open transaction, so by killing the session, all of the activity would be rolled back.  With no way to tell what exactly would be rolled back, killing the session should not be taken lightly.<\/p>\n
For the remainder of this post I am going to show a repro of this particular problem and how to use Multiple Targets in Extended Events to simplify the troubleshooting this.<\/p>\n
Setting Up the Demo<\/h2>\n
To setup a reproduction of this problem you will need two instances of SSMS open.  One of them will be used to connect to the SQL Server normally, and the other will be used to act like the ASP.NET application that originally had the problem.  To setup the second instance of SSMS to act like the ASP.NET application, we are going to set the Execution Timeout using the Options of the Connect to Database Engine window, and we are also going to add an Additional Connection Parameter to the connection to set the Application Name on the connection to \u201cSome Poorly Written App\u201d as shown in the below screenshots from SSMS.<\/p>\n
\"image\"<\/a>   \"image\"<\/a> <\/p>\n
For the remainder of this blog post I am going to refer to the two different instances of SSMS as Normal and PoorApp in the hopes that this prevents confusion.  <\/p>\n
In the Normal SSMS we will create a database and some objects to support the repro of the problem:<\/p>\n
\n
CREATE DATABASE <\/span>[MultiTargetDemo]\r\nGO\r\nUSE <\/span>[MultiTargetDemo]\r\nGO\r\nCREATE TABLE <\/span>dbo.<\/span>RandomObjectsArchive\r\n(<\/span>ArchiveID int identity primary key<\/span>,\r\n <\/span>TableName nvarchar<\/span>(<\/span>128),\r\n <\/span>IndexName nvarchar<\/span>(<\/span>128),\r\n <\/span>ColumnName nvarchar<\/span>(<\/span>128))\r\n<\/span>GO\r\nCREATE TABLE <\/span>dbo.<\/span>TrackArchiveRunTimes\r\n(<\/span>RuntimeID int identity primary key<\/span>,\r\n <\/span>ArchiveRuntime datetime DEFAULT<\/span>(<\/span>CURRENT_TIMESTAMP<\/span>))\r\n<\/span>GO\r\nCREATE PROCEDURE <\/span>dbo.<\/span>GenerateRandomObjects\r\nAS\r\nBEGIN TRANSACTION\r\n    INSERT INTO <\/span>dbo.<\/span>TrackArchiveRunTimes\r\n    DEFAULT VALUES<\/span>;\r\n\r\n    <\/span>INSERT INTO <\/span>RandomObjectsArchive\r\n        (<\/span>TableName, <\/span>IndexName, <\/span>ColumnName)\r\n    <\/span>SELECT TOP <\/span>10000 a.<\/span>name, <\/span>i.<\/span>name, <\/span>c.<\/span>name\r\n    FROM <\/span>sys<\/span>.<\/span>objects <\/span>AS <\/span>a\r\n        CROSS JOIN <\/span>sys<\/span>.<\/span>indexes <\/span>AS <\/span>i\r\n        CROSS JOIN <\/span>sys<\/span>.<\/span>columns <\/span>AS <\/span>c\r\n        CROSS JOIN <\/span>master<\/span>.<\/span>dbo.<\/span>spt_values AS <\/span>sv\r\n    WHERE <\/span>sv.<\/span>type <\/span>= <\/span>'P' \r\n      <\/span>AND <\/span>sv.<\/span>number < <\/span>6 --Adjust to increase runtime\r\n    <\/span>ORDER BY <\/span>NEWID<\/span>() <\/span>DESC\r\nCOMMIT TRANSACTION\r\nGO\r\nUSE <\/span>[master]\r\nGO<\/span><\/pre>\n<\/blockquote>\n
The GenerateRandomObjects stored procedure Inserts a row into a tracking table that tracks when the stored procedure was executed, and then simulates a long running archive process by doing something that you should never do in production code.  The sv.number predicate in the query can be increased or decreased based on the performance of the system being tested against to ensure that the stored procedure runs longer than the Execution Timeout setting, which on my PoorApp SSMS instance was set to 10 seconds.  Increasing the value by 1 has an exponential impact on the performance degradation of the stored procedure, so any changes should be made incrementally to ensure that you don\u2019t create a tempdb bloat problem with the Cartesian product of the query being executed.<\/p>\n
Setting Up the Event Session<\/h2>\n
To troubleshoot this problem using Extended Events we will create an Event Session that captures the following Events:<\/p>\n
\n
sqlserver.database_transaction_begin
\n    
sqlserver.database_transaction_end <\/p>\n
sqlserver.sql_statement_starting <\/p>\n
sqlserver.sql_statement_completed <\/p>\n
sqlserver.sp_statement_starting <\/p>\n
sqlserver.sp_statement_completed <\/p>\n
sqlserver.rpc_starting <\/p>\n
sqlserver.rpc_completed <\/p>\n
sqlserver.module_start <\/p>\n
sqlserver.module_end <\/p>\n
sqlserver.error_reported<\/p>\n<\/blockquote>\n
We\u2019ll add the following Actions to each of the Events:<\/p>\n
\n
sqlserver.session_id
\n    
sqlserver.database_id <\/p>\n
sqlserver.tsql_stack<\/p>\n<\/blockquote>\n
and add the sqlserver.sql_text Action to the starting Events so that we can track what is actually being executed.  Every Event in the Event Session will have a Predicate on the sqlserver.client_app_name so that the Event only fires for connections and requests from \u201cSome Poorly Written App\u201d.  <\/p>\n
\n
IF <\/span>EXISTS(<\/span>SELECT <\/span>* \r\n         <\/span>FROM <\/span>sys<\/span>.<\/span>server_event_sessions \r\n         <\/span>WHERE <\/span>name=<\/span>'OrphanedTransactionHunter'<\/span>) \r\n    <\/span>DROP EVENT SESSION <\/span>[OrphanedTransactionHunter] ON SERVER<\/span>; \r\n<\/span>CREATE EVENT SESSION <\/span>OrphanedTransactionHunter\r\nON SERVER\r\nADD EVENT <\/span>sqlserver.<\/span>database_transaction_begin\r\n(    <\/span>ACTION<\/span>(<\/span>sqlserver.<\/span>session_id, <\/span>sqlserver.<\/span>database_id, <\/span>sqlserver.<\/span>tsql_stack)\r\n    <\/span>WHERE <\/span>(<\/span>sqlserver.<\/span>client_app_name = <\/span>'Some Poorly Written App'<\/span>)),\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>database_transaction_end\r\n(    <\/span>ACTION<\/span>(<\/span>sqlserver.<\/span>session_id, <\/span>sqlserver.<\/span>database_id, <\/span>sqlserver.<\/span>tsql_stack)\r\n    <\/span>WHERE <\/span>(<\/span>sqlserver.<\/span>client_app_name = <\/span>'Some Poorly Written App'<\/span>)),\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>sql_statement_starting\r\n(    <\/span>ACTION<\/span>(<\/span>sqlserver.<\/span>session_id, <\/span>sqlserver.<\/span>database_id, <\/span>sqlserver.<\/span>tsql_stack, <\/span>sqlserver.<\/span>sql_text)\r\n    <\/span>WHERE <\/span>(<\/span>sqlserver.<\/span>client_app_name = <\/span>'Some Poorly Written App'<\/span>)),\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>sql_statement_completed\r\n(    <\/span>ACTION<\/span>(<\/span>sqlserver.<\/span>session_id, <\/span>sqlserver.<\/span>database_id, <\/span>sqlserver.<\/span>tsql_stack)\r\n    <\/span>WHERE <\/span>(<\/span>sqlserver.<\/span>client_app_name = <\/span>'Some Poorly Written App'<\/span>)),\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>sp_statement_starting\r\n(    <\/span>ACTION<\/span>(<\/span>sqlserver.<\/span>session_id, <\/span>sqlserver.<\/span>database_id, <\/span>sqlserver.<\/span>tsql_stack, <\/span>sqlserver.<\/span>sql_text)\r\n    <\/span>WHERE <\/span>(<\/span>sqlserver.<\/span>client_app_name = <\/span>'Some Poorly Written App'<\/span>)),\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>sp_statement_completed\r\n(    <\/span>ACTION<\/span>(<\/span>sqlserver.<\/span>session_id, <\/span>sqlserver.<\/span>database_id, <\/span>sqlserver.<\/span>tsql_stack)\r\n    <\/span>WHERE <\/span>(<\/span>sqlserver.<\/span>client_app_name = <\/span>'Some Poorly Written App'<\/span>)),\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>rpc_starting\r\n(    <\/span>ACTION<\/span>(<\/span>sqlserver.<\/span>session_id, <\/span>sqlserver.<\/span>database_id, <\/span>sqlserver.<\/span>tsql_stack, <\/span>sqlserver.<\/span>sql_text)\r\n    <\/span>WHERE <\/span>(<\/span>sqlserver.<\/span>client_app_name = <\/span>'Some Poorly Written App'<\/span>)),\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>rpc_completed\r\n(    <\/span>ACTION<\/span>(<\/span>sqlserver.<\/span>session_id, <\/span>sqlserver.<\/span>database_id, <\/span>sqlserver.<\/span>tsql_stack)\r\n    <\/span>WHERE <\/span>(<\/span>sqlserver.<\/span>client_app_name = <\/span>'Some Poorly Written App'<\/span>)),\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>module_start\r\n(    <\/span>ACTION<\/span>(<\/span>sqlserver.<\/span>session_id, <\/span>sqlserver.<\/span>database_id, <\/span>sqlserver.<\/span>tsql_stack, <\/span>sqlserver.<\/span>sql_text)\r\n    <\/span>WHERE <\/span>(<\/span>sqlserver.<\/span>client_app_name = <\/span>'Some Poorly Written App'<\/span>)),\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>module_end\r\n(    <\/span>ACTION<\/span>(<\/span>sqlserver.<\/span>session_id, <\/span>sqlserver.<\/span>database_id, <\/span>sqlserver.<\/span>tsql_stack)\r\n    <\/span>WHERE <\/span>(<\/span>sqlserver.<\/span>client_app_name = <\/span>'Some Poorly Written App'<\/span>)),\r\n<\/span>ADD EVENT <\/span>sqlserver.<\/span>error_reported\r\n(    <\/span>ACTION<\/span>(<\/span>sqlserver.<\/span>session_id, <\/span>sqlserver.<\/span>database_id, <\/span>sqlserver.<\/span>tsql_stack)\r\n    <\/span>WHERE <\/span>(<\/span>sqlserver.<\/span>client_app_name = <\/span>'Some Poorly Written App'<\/span>))\r\n<\/span>ADD TARGET <\/span>package0.<\/span>ring_buffer,\r\n<\/span>ADD TARGET <\/span>package0.<\/span>pair_matching\r\n( <\/span>SET <\/span>begin_event = <\/span>'sqlserver.database_transaction_begin'<\/span>,\r\n      <\/span>begin_matching_actions = <\/span>'sqlserver.session_id'<\/span>,\r\n      <\/span>end_event = <\/span>'sqlserver.database_transaction_end'<\/span>,\r\n      <\/span>end_matching_actions = <\/span>'sqlserver.session_id'<\/span>,\r\n      <\/span>respond_to_memory_pressure = <\/span>1\r\n)\r\n<\/span>WITH <\/span>(<\/span>MAX_DISPATCH_LATENCY=<\/span>5 SECONDS, <\/span>TRACK_CAUSALITY=<\/span>ON<\/span>)\r\n<\/span>GO\r\n\r\nALTER EVENT SESSION <\/span>OrphanedTransactionHunter\r\nON SERVER\r\nSTATE<\/span>=<\/span>START\r\nGO<\/span><\/pre>\n<\/blockquote>\n
Fired Events will be dispatched to two different Targets, the package0.ring_buffer to capture the Raw Data (in a true production environment, the package0.asynchronous_file_target would generally be a better Target for Raw Data capture of any volume), and the package0.pair_matching Target which has been configured to match on the sqlserver.database_transaction_begin\/end Events based on the sqlserver.session_id Action.  To ensure that we can track the relationship between events, the Event Session will have TRACK_CAUSALITY set to ON, and to minimize the time it takes for Events to be dispatched for our test, the MAX_DISPATCH_LATENCY will be set to 5 seconds.<\/p>\n
Putting It All Together<\/h2>\n
With the Event Session running, we can change over to our PoorApp SSMS instance and execute the GenerateRandomObjects stored procedure inside of the MultiTargetDemo database.<\/p>\n
\n
EXECUTE <\/span>MultiTargetDemo.<\/span>dbo.<\/span>GenerateRandomObjects<\/pre>\n<\/blockquote>\n
When this executes, the command will timeout and leave the transaction open, simulating the original problem exactly.  Once the query times out, switch back to the Normal SSMS Instance and in a new window execute the stored procedure again and allow it complete its execution.  Since the default timeout of 0 is used in the Normal SSMS Instance, the execution will not time out.  Then we can look at the sys.databases DMV and see that the log_reuse_wait_desc is ACTIVE_TRANSACTION.<\/p>\n
<\/blockquote>\n
\n
SELECT <\/span>log_reuse_wait_desc \r\nFROM <\/span>sys<\/span>.<\/span>databases\r\n<\/span>WHERE <\/span>database_id = <\/span>DB_ID<\/span>(<\/span>'MultiTargetDemo'<\/span>)<\/span><\/pre>\n<\/blockquote>\n
\n
\"image\"<\/a> <\/p>\n<\/blockquote>\n
If we look at DBCC OPENTRAN for the MultiTargetDemo database we will see our orphaned transaction:<\/p>\n
\n
DBCC <\/span>OPENTRAN(<\/span>[MultiTargetDemo])<\/span><\/pre>\n<\/blockquote>\n
\n
\"image\"<\/a> <\/p>\n<\/blockquote>\n
As I mentioned earlier in this post, the transaction can also be seen in the DMV\u2019s:<\/p>\n
\n
SELECT \r\n    <\/span>dtst.<\/span>session_id, \r\n    <\/span>dtdt.<\/span>database_id,\r\n    <\/span>dtst.<\/span>transaction_id, \r\n    <\/span>dtat.<\/span>name,\r\n    <\/span>dtdt.<\/span>database_transaction_begin_time\r\nFROM <\/span>sys<\/span>.<\/span>dm_tran_session_transactions <\/span>AS <\/span>dtst\r\nJOIN <\/span>sys<\/span>.<\/span>dm_tran_active_transactions <\/span>AS <\/span>dtat\r\n    ON <\/span>dtst.<\/span>transaction_id = <\/span>dtat.<\/span>transaction_id\r\nJOIN <\/span>sys<\/span>.<\/span>dm_tran_database_transactions <\/span>AS <\/span>dtdt\r\n    ON <\/span>dtdt.<\/span>transaction_id = <\/span>dtst.<\/span>transaction_id\r\nWHERE <\/span>database_id = <\/span>DB_ID<\/span>(<\/span>'MultiTargetDemo'<\/span>)<\/span><\/pre>\n<\/blockquote>\n
\n
\"image\"<\/a> <\/p>\n<\/blockquote>\n
Now that we have our problem reproduced, lets look at how we can use the information captured by our Extended Event Session to track it back to the source of the problem.  First we\u2019ll query the pair_matching Target to find out information about the sqlserver.database_transaction_begin Event that was unmatched.<\/p>\n
\n
-- Query the XML to get the Target Data\r\n<\/span>SELECT \r\n    <\/span>n.<\/span>value(<\/span>'(event\/@name)[1]'<\/span>, <\/span>'varchar(50)'<\/span>) <\/span>AS <\/span>event_name,\r\n    <\/span>n.<\/span>value(<\/span>'(event\/@package)[1]'<\/span>, <\/span>'varchar(50)'<\/span>) <\/span>AS <\/span>package_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>n.<\/span>value(<\/span>'(event\/@timestamp)[1]'<\/span>, <\/span>'datetime2'<\/span>)) <\/span>AS <\/span>[timestamp],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/action[@name="session_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>as <\/span>session_id,\r\n    <\/span>n.<\/span>value(<\/span>'(event\/action[@name="database_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>as <\/span>[database_id],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/action[@name="tsql_stack"]\/value)[1]'<\/span>, <\/span>'nvarchar(max)'<\/span>) <\/span>as <\/span>tsql_stack,    \r\n    <\/span>n.<\/span>value(<\/span>'(event\/action[@name="attach_activity_id"]\/value)[1]'<\/span>, <\/span>'varchar(50)'<\/span>) <\/span>as <\/span>attach_activity_id\r\nFROM\r\n<\/span>(    <\/span>SELECT <\/span>td.<\/span>query(<\/span>'.'<\/span>) <\/span>as <\/span>n\r\n    FROM \r\n    <\/span>(\r\n        <\/span>SELECT <\/span>CAST<\/span>(<\/span>target_data AS XML<\/span>) <\/span>as <\/span>target_data\r\n        FROM <\/span>sys<\/span>.<\/span>dm_xe_sessions <\/span>AS <\/span>s \r\n        JOIN <\/span>sys<\/span>.<\/span>dm_xe_session_targets <\/span>AS <\/span>t \r\n            ON <\/span>t.<\/span>event_session_address = <\/span>s.<\/span>address\r\n        WHERE <\/span>s.<\/span>name = <\/span>'OrphanedTransactionHunter'\r\n          <\/span>AND <\/span>t.<\/span>target_name = <\/span>'pair_matching'\r\n    <\/span>) <\/span>AS <\/span>sub\r\n    CROSS APPLY <\/span>target_data.<\/span>nodes(<\/span>'PairingTarget\/event'<\/span>) <\/span>AS <\/span>q(<\/span>td)\r\n) <\/span>as <\/span>tab\r\n-- We are interested in unmatched sqlserver.database_transaction_begin Events\r\n<\/span>WHERE <\/span>n.<\/span>value(<\/span>'(event\/@name)[1]'<\/span>, <\/span>'varchar(50)'<\/span>) = <\/span>'database_transaction_begin'\r\n<\/span>ORDER BY <\/span>session_id, <\/span>activity_id\r\nGO\r\n<\/span><\/pre>\n<\/blockquote>\n
\n<\/blockquote>\n
\n
\"image\"<\/a> <\/p>\n<\/blockquote>\n
From this we can see our orphaned transaction event, and find the attach_activity_id of that Event.  The attach_activity_id Action is added to the Events in an Event Session when TRACK_CAUSALITY is turned ON.  There are two pieces of information contained in the attach_activity_id Action, the activity Guid (the first 36 characters of the value) and the sequence number for the Event, the number following the Guid.  The Guid can be used to find related Events, and the sequence number can be used to determine the order that the Events occurred.  By using the Guid from the attach_activity_id Action from our first query, we can query the ring_buffer Target and parse out the specific Events we are interested in looking at further.<\/p>\n
\n
-- Query the XML to get the Target Data\r\n<\/span>SELECT \r\n    <\/span>n.<\/span>value(<\/span>'(event\/@name)[1]'<\/span>, <\/span>'varchar(50)'<\/span>) <\/span>AS <\/span>event_name,\r\n    <\/span>n.<\/span>value(<\/span>'(event\/@package)[1]'<\/span>, <\/span>'varchar(50)'<\/span>) <\/span>AS <\/span>package_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>n.<\/span>value(<\/span>'(event\/@timestamp)[1]'<\/span>, <\/span>'datetime2'<\/span>)) <\/span>AS <\/span>[timestamp],  \r\n    <\/span>n.<\/span>value(<\/span>'(event\/action[@name="database_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>as <\/span>[database_id],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/action[@name="session_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>as <\/span>[session_id],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="object_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[object_id],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="object_type"]\/value)[1]'<\/span>, <\/span>'nvarchar(128)'<\/span>) <\/span>AS <\/span>[object_type],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="object_name"]\/value)[1]'<\/span>, <\/span>'nvarchar(128)'<\/span>) <\/span>AS <\/span>[object_name],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="error"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[error],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="severity"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[severity],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="state"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[state],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="user_defined"]\/value)[1]'<\/span>, <\/span>'bit'<\/span>) <\/span>AS <\/span>[user_defined],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="message"]\/value)[1]'<\/span>, <\/span>'nvarchar(4000)'<\/span>) <\/span>AS <\/span>[message],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="duration"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[duration],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="row_count"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[row_count],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="cpu"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[cpu],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="reads"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[reads],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="writes"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[writes],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/action[@name="tsql_stack"]\/value)[1]'<\/span>, <\/span>'nvarchar(max)'<\/span>) <\/span>AS <\/span>[tsql_stack],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="offset"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[offset],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="offset_end"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[offset_end],\r\n    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="nest_level"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[nest_level],           \r\n     <\/span>n.<\/span>value(<\/span>'(event\/action[@name="sql_text"]\/value)[1]'<\/span>, <\/span>'nvarchar(max)'<\/span>) <\/span>AS <\/span>[sql_text],\r\n    <\/span>CAST<\/span>(<\/span>SUBSTRING<\/span>(<\/span>n.<\/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>n.<\/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\r\n<\/span>(    <\/span>SELECT <\/span>td.<\/span>query(<\/span>'.'<\/span>) <\/span>as <\/span>n\r\n    FROM \r\n    <\/span>(\r\n        <\/span>SELECT <\/span>CAST<\/span>(<\/span>target_data AS XML<\/span>) <\/span>as <\/span>target_data\r\n        FROM <\/span>sys<\/span>.<\/span>dm_xe_sessions <\/span>AS <\/span>s \r\n        JOIN <\/span>sys<\/span>.<\/span>dm_xe_session_targets <\/span>AS <\/span>t \r\n            ON <\/span>t.<\/span>event_session_address = <\/span>s.<\/span>address\r\n        WHERE <\/span>s.<\/span>name = <\/span>'OrphanedTransactionHunter'\r\n          <\/span>AND <\/span>t.<\/span>target_name = <\/span>'ring_buffer'\r\n    <\/span>) <\/span>AS <\/span>sub\r\n    CROSS APPLY <\/span>target_data.<\/span>nodes(<\/span>'RingBufferTarget\/event'<\/span>) <\/span>AS <\/span>q(<\/span>td)\r\n) <\/span>as <\/span>tab\r\n-- We are interested in unmatched sqlserver.database_transaction_begin Events\r\n<\/span>WHERE <\/span>SUBSTRING<\/span>(<\/span>n.<\/span>value(<\/span>'(event\/action[@name="attach_activity_id"]\/value)[1]'<\/span>, <\/span>'varchar(50)'<\/span>), <\/span>1, <\/span>36) = <\/span>'ADCF379A-4BCA-41BA-9B08-4C2265894392'\r\n<\/span>ORDER BY <\/span>session_id, <\/span>event_sequence\r\nGO\r\n<\/span><\/pre>\n<\/blockquote>\n
 <\/p>\n
With a little bit more work, we can reduce the XML parsing to only the important data elements that we need, and we can parse the tsql_stack Action to retrieve the related Event level statement_text from the sys.dm_exec_sql_text() DMF, since the sql_text Action did not have the intended information. <\/p>\n
\n
-- Query the XML to get the Target Data\r\n<\/span>SELECT \r\n    <\/span>event_name,\r\n    <\/span>timestamp<\/span>,\r\n    <\/span>database_id, \r\n    <\/span>OBJECT_NAME<\/span>(<\/span>st.<\/span>objectid, <\/span>st.<\/span>dbid<\/span>) <\/span>AS <\/span>ObjectName,\r\n    <\/span>SUBSTRING<\/span>(<\/span>st.<\/span>text<\/span>, (<\/span>tsql_stack.<\/span>value(<\/span>'(\/frame\/@offsetStart)[1]'<\/span>, <\/span>'int'<\/span>)\/<\/span>2)+<\/span>1, \r\n        ((<\/span>CASE <\/span>tsql_stack.<\/span>value(<\/span>'(\/frame\/@offsetEnd)[1]'<\/span>, <\/span>'int'<\/span>)\r\n            <\/span>WHEN <\/span>-<\/span>1 THEN <\/span>DATALENGTH<\/span>(<\/span>st.<\/span>text<\/span>)\r\n            <\/span>ELSE <\/span>tsql_stack.<\/span>value(<\/span>'(\/frame\/@offsetEnd)[1]'<\/span>, <\/span>'int'<\/span>)\r\n            <\/span>END <\/span>- <\/span>tsql_stack.<\/span>value(<\/span>'(\/frame\/@offsetStart)[1]'<\/span>, <\/span>'int'<\/span>))\/<\/span>2) + <\/span>1) <\/span>AS <\/span>statement_text,\r\n    <\/span>duration,\r\n    <\/span>activity_id,\r\n    <\/span>event_sequence\r\nFROM\r\n<\/span>(\r\n<\/span>SELECT \r\n    <\/span>n.<\/span>value(<\/span>'(event\/@name)[1]'<\/span>, <\/span>'varchar(50)'<\/span>) <\/span>AS <\/span>event_name,\r\n<\/span>    <\/span>DATEADD<\/span>(<\/span>hh, \r\n            <\/span>DATEDIFF<\/span>(<\/span>hh, <\/span>GETUTCDATE<\/span>(), <\/span>CURRENT_TIMESTAMP<\/span>), \r\n            <\/span>n.<\/span>value(<\/span>'(event\/@timestamp)[1]'<\/span>, <\/span>'datetime2'<\/span>)) <\/span>AS <\/span>[timestamp],  \r\n    <\/span>n.<\/span>value(<\/span>'(event\/action[@name="database_id"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>as <\/span>[database_id],\r\n<\/span>    <\/span>n.<\/span>value(<\/span>'(event\/data[@name="duration"]\/value)[1]'<\/span>, <\/span>'int'<\/span>) <\/span>AS <\/span>[duration],\r\n<\/span>    <\/span>CAST<\/span>(<\/span>n.<\/span>value(<\/span>'(event\/action[@name="tsql_stack"]\/value)[1]'<\/span>, <\/span>'nvarchar(max)'<\/span>) <\/span>AS XML<\/span>) <\/span>AS <\/span>[tsql_stack],\r\n<\/span>    <\/span>CAST<\/span>(<\/span>SUBSTRING<\/span>(<\/span>n.<\/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>n.<\/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\r\n<\/span>(    <\/span>SELECT <\/span>td.<\/span>query(<\/span>'.'<\/span>) <\/span>as <\/span>n\r\n    FROM \r\n    <\/span>(\r\n        <\/span>SELECT <\/span>CAST<\/span>(<\/span>target_data AS XML<\/span>) <\/span>as <\/span>target_data\r\n        FROM <\/span>sys<\/span>.<\/span>dm_xe_sessions <\/span>AS <\/span>s \r\n        JOIN <\/span>sys<\/span>.<\/span>dm_xe_session_targets <\/span>AS <\/span>t \r\n            ON <\/span>t.<\/span>event_session_address = <\/span>s.<\/span>address\r\n        WHERE <\/span>s.<\/span>name = <\/span>'OrphanedTransactionHunter'\r\n          <\/span>AND <\/span>t.<\/span>target_name = <\/span>'ring_buffer'\r\n    <\/span>) <\/span>AS <\/span>sub\r\n    CROSS APPLY <\/span>target_data.<\/span>nodes(<\/span>'RingBufferTarget\/event'<\/span>) <\/span>AS <\/span>q(<\/span>td)\r\n) <\/span>as <\/span>tab\r\n) <\/span>as <\/span>tab2\r\nCROSS APPLY <\/span>sys<\/span>.<\/span>dm_exec_sql_text<\/span>(<\/span>tsql_stack.<\/span>value(<\/span>'xs:hexBinary(substring((\/frame\/@handle)[1], 3))'<\/span>, <\/span>'varbinary(max)'<\/span>)) <\/span>AS <\/span>st\r\n-- We are interested in Events in activity_id sequence of the orphaned transaction only\r\n<\/span>WHERE <\/span>activity_id = <\/span>'ADCF379A-4BCA-41BA-9B08-4C2265894392'\r\n<\/span>ORDER BY <\/span>session_id, <\/span>event_sequence\r\nGO\r\n<\/span><\/pre>\n<\/blockquote>\n
\n
\"image\"<\/a> <\/p>\n<\/blockquote>\n
The highlighted value shows that the stored procedure execution ended at the Execution Timeout limit that was set for the PoorApp SSMS Instance.  Beyond that we can track each of the statements and see that when the execution ended, it was in the INSERT INTO RandomObjectsArchive statement in the stored procedure allowing us to target our efforts at troubleshooting to the specific problem in a short amount of time.<\/p>\n","protected":false},"excerpt":{"rendered":"
Yesterday\u2019s blog post Targets Week \u2013 etw_classic_sync_target covered the ETW integration that is built into Extended Events and how the etw_classic_sync_target can be used in conjunction with other ETW traces to provide troubleshooting at a level previously not possible with SQL Server.  In today\u2019s post we\u2019ll look at how to use multiple targets to simplify […]<\/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,40,45],"tags":[],"class_list":["post-553","post","type-post","status-publish","format-standard","hentry","category-extended-events","category-sql-server-2008","category-sql-server-denali","category-xevent-a-day-series"],"yoast_head":"\nAn XEvent a Day (11 of 31) \u2013 Targets Week \u2013 Using Multiple Targets to Debug Orphaned Transactions - 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-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/\" \/>\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 (11 of 31) \u2013 Targets Week \u2013 Using Multiple Targets to Debug Orphaned Transactions - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"Yesterday\u2019s blog post Targets Week \u2013 etw_classic_sync_target covered the ETW integration that is built into Extended Events and how the etw_classic_sync_target can be used in conjunction with other ETW traces to provide troubleshooting at a level previously not possible with SQL Server.  In today\u2019s post we\u2019ll look at how to use multiple targets to simplify […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2010-12-11T22:46:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:17:39+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windows-live-writer\/anxeventaday11of31targetsweekusingmulti\/53b77f03\/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=\"17 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-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"An XEvent a Day (11 of 31) \u2013 Targets Week \u2013 Using Multiple Targets to Debug Orphaned Transactions\",\"datePublished\":\"2010-12-11T22:46:00+00:00\",\"dateModified\":\"2017-04-13T16:17:39+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\\\/\"},\"wordCount\":1812,\"commentCount\":3,\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windows-live-writer\\\/anxeventaday11of31targetsweekusingmulti\\\/53b77f03\\\/image_thumb.png\",\"articleSection\":[\"Extended Events\",\"SQL Server 2008\",\"SQL Server Denali\",\"XEvent a Day Series\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\\\/\",\"name\":\"An XEvent a Day (11 of 31) \u2013 Targets Week \u2013 Using Multiple Targets to Debug Orphaned Transactions - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windows-live-writer\\\/anxeventaday11of31targetsweekusingmulti\\\/53b77f03\\\/image_thumb.png\",\"datePublished\":\"2010-12-11T22:46:00+00:00\",\"dateModified\":\"2017-04-13T16:17:39+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-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windows-live-writer\\\/anxeventaday11of31targetsweekusingmulti\\\/53b77f03\\\/image_thumb.png\",\"contentUrl\":\"https:\\\/\\\/www.SQLskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/windows-live-writer\\\/anxeventaday11of31targetsweekusingmulti\\\/53b77f03\\\/image_thumb.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\\\/#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 (11 of 31) \u2013 Targets Week \u2013 Using Multiple Targets to Debug Orphaned Transactions\"}]},{\"@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 (11 of 31) \u2013 Targets Week \u2013 Using Multiple Targets to Debug Orphaned Transactions - 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-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/","og_locale":"en_US","og_type":"article","og_title":"An XEvent a Day (11 of 31) \u2013 Targets Week \u2013 Using Multiple Targets to Debug Orphaned Transactions - Jonathan Kehayias","og_description":"Yesterday\u2019s blog post Targets Week \u2013 etw_classic_sync_target covered the ETW integration that is built into Extended Events and how the etw_classic_sync_target can be used in conjunction with other ETW traces to provide troubleshooting at a level previously not possible with SQL Server.  In today\u2019s post we\u2019ll look at how to use multiple targets to simplify […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/","og_site_name":"Jonathan Kehayias","article_published_time":"2010-12-11T22:46:00+00:00","article_modified_time":"2017-04-13T16:17:39+00:00","og_image":[{"url":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windows-live-writer\/anxeventaday11of31targetsweekusingmulti\/53b77f03\/image_thumb.png","type":"","width":"","height":""}],"author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"17 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"An XEvent a Day (11 of 31) \u2013 Targets Week \u2013 Using Multiple Targets to Debug Orphaned Transactions","datePublished":"2010-12-11T22:46:00+00:00","dateModified":"2017-04-13T16:17:39+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/"},"wordCount":1812,"commentCount":3,"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/#primaryimage"},"thumbnailUrl":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windows-live-writer\/anxeventaday11of31targetsweekusingmulti\/53b77f03\/image_thumb.png","articleSection":["Extended Events","SQL Server 2008","SQL Server Denali","XEvent a Day Series"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/","name":"An XEvent a Day (11 of 31) \u2013 Targets Week \u2013 Using Multiple Targets to Debug Orphaned Transactions - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/#primaryimage"},"thumbnailUrl":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windows-live-writer\/anxeventaday11of31targetsweekusingmulti\/53b77f03\/image_thumb.png","datePublished":"2010-12-11T22:46:00+00:00","dateModified":"2017-04-13T16:17:39+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-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/#primaryimage","url":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windows-live-writer\/anxeventaday11of31targetsweekusingmulti\/53b77f03\/image_thumb.png","contentUrl":"https:\/\/www.SQLskills.com\/blogs\/jonathan\/wp-content\/uploads\/windows-live-writer\/anxeventaday11of31targetsweekusingmulti\/53b77f03\/image_thumb.png"},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/an-xevent-a-day-11-of-31-targets-week-using-multiple-targets-to-debug-orphaned-transactions\/#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 (11 of 31) \u2013 Targets Week \u2013 Using Multiple Targets to Debug Orphaned Transactions"}]},{"@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\/553","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=553"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/553\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=553"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=553"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=553"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}