sqlserver中查找長時間未提交事務

 　　無論是有意無意，如果事務在數據庫中保持打開，則它會阻塞其他進程對修改後的數據進行操作。同樣，對事務日志進行備份也只會截斷不活動事務的那部分事務日志，所以打開的事務會導致日志變多(甚至達到物理限制)，直到事務被提交或回滾。

　　要找到最早的活動事務，可以使用DBCC OPENTRAN命令。

　　給出一個示例：

　　代碼如下：

　　CREATE TABLE T_Product(PKID int, PName Nvarchar(50));

　　GO

　　BEGIN TRAN

　　INSERT INTO T_Product VALUES (101, '嫦娥四號');

　　GO

　　DBCC OPENTRAN;

　　ROLLBACK TRAN;

　　GO

　　DROP TABLE T_Product;

　　GO

　　執行結果：

　　代碼如下：

　　/*

　　(1 row(s) affected)

　　數據庫 'Testdb' 的事務信息。

　　最早的活動事務：

　　SPID (服務器進程 ID)： 54

　　UID (用戶 ID)： -1

　　名稱 : user_transaction

　　LSN : (295:6687:1)

　　開始時間 : 12 24 2010 2:50:15:607PM

　　SID : 0x0105000000000005150000007fe010d31cba1ab1566ac5dff4010000

　　DBCC 執行完畢。如果 DBCC 輸出了錯誤信息，請與系統管理員聯系。

　　*/

　　結果顯示了最早活動日志的相關信息，包括服務器進程ID、用戶ID、和事務的開始時間。關鍵是SPID和Start Time.

　　擁有這些信息後，可以使用動態管理視圖(DMV)來檢驗正在執行的T-SQL,以及在必要時關閉這個過程

　　DBCC OPENTRAN對於孤立連接(在數據庫中是打開的，但與應用程序或客戶端已經斷開的連接)是非常有用的，並能幫助我們找出遺漏了COMMIT或ROLLBACK的事務。該命令也返回在指定數據庫內存在最早的活動事務和最早的分布式和非分布式復制事務。如果沒有活動事務，則顯示信息性消息，而不返回會話級數據。

　　我們看一個實例：

　　代碼如下：

　　SET Transaction isolation level serializable

　　BEGIN TRAN

　　select * from T_Product

　　Insert into T_Product

　　select 'OATest' union all

　　select 'OAPlay'

　　這是一個未提交的事務，在另一個查詢窗口執行如下：

　　代碼如下：

　　select session_id,transaction_id,is_user_transaction,is_local

　　from sys.dm_tran_session_transactions

　　where is_user_transaction=1

　　執行結果：

　　代碼如下：

　　/*返回結果

　　session_id transaction_id is_user_transaction is_local

　　54 489743 1 1

　　*/

　　返回會話ID後，可以通過sys.dm_exec_connections和sys.dm_exec_sql_text來挖掘最近執行的查詢的詳細信息。

　　代碼如下：

　　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

　　這個查詢返回最後執行的語句。也可以使用sys.dm_exec_requests.

　　因為也從sys.dm_tran_session_transactions的第一個查詢中得知事務ID,所以可以使用sys.dm_tran_active_transactions來了解更多事務本身的內容

　　代碼如下：

　　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 back'

　　when 0 then 'been rolled back'

　　end transaction_state

　　from

　　sys.dm_tran_active_transactions

　　where transaction_ID=455520

　　代碼如下：

　　/*結果：

　　transaction_begin_time tran_Type transaction_state

　　2010-12-24 14:05:29.170 Read/Write transaction active

　　*/

　　小結：這裡演示了使用DMV 排除故障和調查長時間的活動事務的一般技巧。基本步驟如下：

　　1、查詢sys.dm_tran_session_transactions獲取會話ID和事務ID之間的映射。

　　2、查詢sys.dm_exec_connections和sys.dm_exec_sql_text查找會話最新執行的命令(most_recent_sql_Handle列)

　　3、最後，查詢sys.dm_tran_active_transactions確定事務被打開了多少時間、事務的類型和事務的狀態。

　　使用這個技巧可以回到應用程序去查明調用的被拋棄的事務(打開但從未提交)以及那些運行時間太長或對於應用程序來說是不必要的不恰當事務。