SQL Server 2008通過DBCC OPENTRAN和會話查詢事務(wù)是本文我們主要要介紹的內(nèi)容，無論是有意無意，如果事務(wù)在數(shù)據(jù)庫中保持打開，則它會阻塞其他進程對修改后的數(shù)據(jù)進行操作。同樣，對事務(wù)日志進行備份也只會截斷不活動事務(wù)的那部分事務(wù)日志，所以打開的事務(wù)會導(dǎo)致日志變多（甚至達(dá)到物理限制），直到事務(wù)被提交或回滾。要找到最早的活動事務(wù)，可以使用DBCC OPENTRAN命令。詳細(xì)用法見MSDN：http://msdn.microsoft.com/zh-cn/library/ms182792.aspx。

給出一個示例：

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 

執(zhí)行結(jié)果：

/*    
(1 row(s) affected)   
數(shù)據(jù)庫 'Testdb' 的事務(wù)信息。  
最早的活動事務(wù):  
SPID (服務(wù)器進程 ID): 54  
UID (用戶 ID): -1  
名稱 : user_transaction  
LSN : (295:6687:1)  
開始時間 : 12 24 2010 2:50:15:607PM  
SID : 0x0105000000000005150000007fe010d31cba1ab1566ac5dff4010000DBCC 執(zhí)行完畢。如果 DBCC 輸出了錯誤信息，請與系統(tǒng)管理員聯(lián)系。  
*/ 

結(jié)果顯示了最早活動日志的相關(guān)信息，包括服務(wù)器進程ID、用戶ID、和事務(wù)的開始時間。關(guān)鍵是SPID和Start Time。擁有這些信息后，可以使用動態(tài)管理視圖(DMV)來檢驗正在執(zhí)行的T-SQL,以及在必要時關(guān)閉這個過程DBCC OPENTRAN對于孤立連接（在數(shù)據(jù)庫中是打開的，但與應(yīng)用程序或客戶端已經(jīng)斷開的連接）是非常有用的，并能幫助我們找出遺漏了COMMIT或ROLLBACK事務(wù)。該命令也返回在指定數(shù)據(jù)庫內(nèi)存在最早的活動事務(wù)和最早的分布式和非分布式復(fù)制事務(wù)。如果沒有活動事務(wù)，則顯示信息性消息，而不返回會話級數(shù)據(jù)。

我們看一個實例：

SET Transaction isolation level serializable  
BEGIN TRAN  
select * from T_Product  
Insert into T_Product   
select 'OATest' union all  
select 'OAPlay' 

這是一個未提交的事務(wù),在另一個查詢窗口執(zhí)行如下：

select session_id,transaction_id,is_user_transaction,is_local   
from sys.dm_tran_session_transactions  
where is_user_transaction=1 

執(zhí)行結(jié)果： 

/*返回結(jié)果  
session_id transaction_id is_user_transaction is_local  
54 489743 1 1  
*/ 

返回會話ID后，可以通過sys.dm_exec_connections和sys.dm_exec_sql_text來挖掘最近執(zhí)行的查詢的詳細(xì)信息。

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 

這個查詢返回最后執(zhí)行的語句。也可以使用sys.dm_exec_requests。因為也從sys.dm_tran_session_transactions的第一個查詢中得知事務(wù)ID，所以可以使用sys.dm_tran_active_transactions來了解更多事務(wù)本身的內(nèi)容.

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 

結(jié)果：

/*  
transaction_begin_time tran_Type transaction_state  
2010-12-24 14:05:29.170 Read/Write transaction active  
*/ 

小結(jié)：這里演示了使用DMV 排除故障和調(diào)查長時間的活動事務(wù)的一般技巧。

基本步驟如下：

1、查詢sys.dm_tran_session_transactions獲取會話ID和事務(wù)ID之間的映射；

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

3、最后，查詢sys.dm_tran_active_transactions確定事務(wù)被打開了多少時間、事務(wù)的類型和事務(wù)的狀態(tài)。使用這個技巧可以回到應(yīng)用程序去查明調(diào)用的被拋棄的事務(wù)（打開但從未提交）以及那些運行時間太長或?qū)τ趹?yīng)用程序來說是不必要的不恰當(dāng)事務(wù)。

關(guān)于SQL Server 2008通過DBCC OPENTRAN和會話查詢事務(wù)的相關(guān)知識就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！

 【編輯推薦】

1. SQL Server多表查詢優(yōu)化方案總結(jié)
2. SQL Server數(shù)據(jù)庫ISNULL函數(shù)的應(yīng)用實例
3. SQL Server數(shù)據(jù)庫DATEPART的語法及使用實例
4. SQL Server根據(jù)子節(jié)點查詢所有父節(jié)點的代碼示例
5. SQL Server臟讀方式數(shù)據(jù)提取之NOLOCK和READPAST