To find the oldest active transaction, you can use the DBCC OPENTRAN command. See Msdn:http://msdn.microsoft.com/zh-cn/library/ms182792.aspx for detailed usage
Give an example:
Copy Code code as follows:
CREATE TABLE t_product (pkid int, pname Nvarchar (50));
Go
BEGIN TRAN
INSERT into T_product VALUES (101, ' Chang ' e Fourth ');
Go
DBCC Opentran;
ROLLBACK TRAN;
Go
DROP TABLE t_product;
Go
Execution results:
Copy Code code as follows:
/*
(1 row (s) affected)
Transaction information for the database ' TestDB '.
The oldest active transaction:
SPID (server process ID): 54
UID (User ID):-1
Name: user_transaction
LSN: (295:6687:1)
Start time: 2:50:15:607pm
sid:0x0105000000000005150000007fe010d31cba1ab1566ac5dff4010000
DBCC execution completed. If DBCC prints an error message, contact your system administrator.
*/
The results show information about the oldest activity log, including the server process ID, the user ID, and the start time of the transaction. The key is the SPID and start time.
Once you have this information, you can use the dynamic management view (DMV) to verify the T-SQL that is being executed, and to close the procedure if necessary
DBCC Opentran are useful for orphaned connections (which are open in the database but are disconnected from applications or clients) and can help us to identify transactions that have been omitted from commit or rollback. This command also returns the oldest active transaction and the oldest distributed and distributed replication transaction in the specified database. If there is no active transaction, an informational message is displayed, and no session-level data is returned.
Let's look at an example:
Copy Code code as follows:
SET Transaction Isolation Level serializable
BEGIN TRAN
SELECT * FROM T_product
Insert into T_product
Select ' Oatest ' UNION ALL
Select ' Oaplay '
This is an uncommitted transaction and is executed in another query window as follows:
Copy Code code as follows:
Select Session_id,transaction_id,is_user_transaction,is_local
From Sys.dm_tran_session_transactions
where is_user_transaction=1
Execution results:
Copy Code code as follows:
/* Return results
session_id transaction_id is_user_transaction is_local
54 489743 1 1
*/
After the session ID is returned, the details of the most recently executed query can be mined through sys.dm_exec_connections and sys.dm_exec_sql_text.
Copy Code code as follows:
Select S.text from sys.dm_exec_connections C
Cross apply Sys.dm_exec_sql_text (C.most_recent_sql_handle) s
where session_id=54
This query returns the last executed statement. You can also use sys.dm_exec_requests.
Because the transaction ID is also known from the first query of sys.dm_tran_session_transactions, you can use Sys.dm_tran_active_transactions to learn more about the transaction itself
Copy Code code as follows:
Select Transaction_begin_time,
Case Transaction_type
When 1 Then ' Read/write transaction '
When 2 Then ' read-only transaction '
When 3 Then ' System transaction '
When 4 Then ' Distributed transaction '
End Tran_type,
Case Transaction_state
When 0 Then ' not been comoletely initaialiaed yet '
When 1 Then ' initaialiaed but Ha notstarted '
When 2 Then ' active '
When 3 Then ' ended (read-only transaction) '
When 4 Then ' commit initiated for distributed transaction '
When 5 Then ' transaction prepared and waiting resolution '
When 6 then ' commited '
When 7 then ' being rolled '
When 0 Then ' been rolled '
End Transaction_state
From
Sys.dm_tran_active_transactions
where transaction_id=455520
Copy Code code as follows:
/* Results:
Transaction_begin_time Tran_type Transaction_state
2010-12-24 14:05:29.170 read/write Transaction Active
*/
Summary: Here is a demonstration of general techniques for using the DMV to troubleshoot and investigate Long-running activity transactions. The basic steps are as follows:
1, query sys.dm_tran_session_transactions get the mapping between the session ID and the transaction ID.
2, Query sys.dm_exec_connections and sys.dm_exec_sql_text find session the most recently executed command (most_recent_sql_handle column)
3. Finally, query sys.dm_tran_active_transactions determines how much time the transaction was opened, the type of transaction, and the state of the transaction.
Using this technique, you can go back to the application to identify the discarded transactions that were invoked (open but never committed) and those that were running too long or unnecessary for the application.
Invite the Month Note: This article copyright by invite month and the blog Garden Common All, reprint please indicate the source.
