SQL Server 2008中能否获取触发BEGIN TRANSACTION的最后SP/查询？

Great question! In SQL Server 2008, tracking down the last stored procedure or query that issued a BEGIN TRANSACTION isn’t something you can pull off with a single built-in command, but there are a few reliable methods depending on your scenario:

1. Query Active Transactions with DMVs (for ongoing transactions) #

If the transaction is still active (not committed or rolled back), you can use dynamic management views (DMVs) to link the transaction to its originating session and get the associated SQL text or stored procedure name. Here’s a ready-to-use query:

SELECT 
    t.transaction_id,
    t.name AS transaction_name,
    t.transaction_begin_time,
    s.session_id,
    s.login_name,
    COALESCE(OBJECT_NAME(st.objectid), 'Ad-hoc Query') AS procedure_or_batch,
    SUBSTRING(
        st.text, 
        (r.statement_start_offset/2) + 1, 
        CASE r.statement_end_offset 
            WHEN -1 THEN DATALENGTH(st.text) 
            ELSE r.statement_end_offset - r.statement_start_offset 
        END / 2 + 1
    ) AS executed_sql_fragment
FROM sys.dm_tran_active_transactions t
JOIN sys.dm_tran_session_transactions stn 
    ON t.transaction_id = stn.transaction_id
JOIN sys.dm_exec_sessions s 
    ON stn.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r 
    ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, s.sql_handle)) st
WHERE t.transaction_type != 2 -- Exclude internal system transactions
ORDER BY t.transaction_begin_time DESC;

This will return the most recent active transactions first, showing you the login, session ID, and either the stored procedure name or the ad-hoc query that started the transaction. Note: This only works for transactions that are still open.

2. Use SQL Server Profiler (for capturing past/future transactions) #

If you need to track transactions that have already completed or want to monitor future ones, SQL Server Profiler is the most straightforward tool in 2008. Here’s how to set it up:

• Open SQL Server Profiler and create a new trace connected to your instance.
• In the Events Selection tab, enable these events:
  • Under Transactions: Begin Transaction, Commit Transaction, Rollback Transaction
  • Under TSQL: SQL:BatchStarting (for ad-hoc queries) and SP:Starting (for stored procedures)
• Add columns like SessionID, DatabaseName, and ObjectName to your trace output to make it easier to link transactions to their origin.
• Run the trace—when a BEGIN TRANSACTION fires, you’ll see the corresponding batch or stored procedure in the trace logs, tied together by the session ID.

Heads up: Profiler can add some overhead to your server, so use it cautiously in production (or apply filters to only track specific databases/logins).

3. Extended Events (lighter-weight alternative) #

SQL Server 2008 introduced Extended Events, which are more performance-friendly than Profiler, though the setup is a bit more manual. You can create an event session to capture transaction starts and their associated SQL text:
First, create the session:

CREATE EVENT SESSION TrackTransactionInitiators ON SERVER 
ADD EVENT sqlserver.transaction_begin(
    ACTION(
        sqlserver.sql_text, 
        sqlserver.session_id, 
        sqlserver.username,
        sqlserver.database_name
    )
)
ADD TARGET package0.event_file(SET filename=N'C:\SQLTraces\TransactionStarts.xel')
WITH (
    MAX_MEMORY=4096 KB, 
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY=30 SECONDS
);

Then start it:

ALTER EVENT SESSION TrackTransactionInitiators ON SERVER STATE=START;

To view captured events later:

SELECT 
    event_data.value('(event/@name)[1]', 'varchar(50)') AS event_type,
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS event_time,
    event_data.value('(event/action[@name="session_id"]/value)[1]', 'int') AS session_id,
    event_data.value('(event/action[@name="username"]/value)[1]', 'varchar(100)') AS username,
    event_data.value('(event/action[@name="database_name"]/value)[1]', 'varchar(100)') AS database_name,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS initiating_sql
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file(N'C:\SQLTraces\TransactionStarts*.xel', NULL, NULL, NULL)
) AS x
ORDER BY event_time DESC;

This will log every transaction start to a file, which you can query later to find the last one. Just make sure the directory C:\SQLTraces\ exists on your server first.

内容的提问来源于stack exchange，提问作者ArcanYellowaine