Search

Monday, March 24, 2014

T-Log space used by which transaction

Sometime T-Log space is increasing very fast and we are not aware that which transaction is culprit. You can use below DMV to check which transaction is culprit.

SELECT ST.Session_ID AS [Session ID], DT.Transaction_ID AS [Transaction ID], DB_NAME(DT.Database_ID) AS [DataBase Name], 
CASE Database_Transaction_Type WHEN 1 THEN 'Read / Write' WHEN 2 THEN 'Read Only' WHEN 3 THEN 'System' END AS [Transaction Type], 
CASE Database_Transaction_State WHEN 1 THEN 'Not Initialized' WHEN 3 THEN 'Transaction No Log' WHEN 4 THEN 'Transaction With Log' WHEN 5 THEN 'Transaction Prepared' WHEN 10 THEN 'Commited' WHEN 11 THEN 'Rolled Back' WHEN 12 THEN 'Commited And Log Generated' END AS [Transaction State], 
Database_Transaction_Begin_Time AS [Transaction Begin Time], SP.HostName AS [Host Name], SQLT.Text, SP.LogiName AS [Login Name], SP.LastWaitType AS [Last Wait Type], SP.Status, 
Database_Transaction_Log_Bytes_Reserved AS [Log Bytes Reserved], Database_Transaction_Log_Record_Count AS [Log Record Count], Database_Transaction_Log_Bytes_Used AS [Log Bytes Used], 
(Database_Transaction_Log_Bytes_Used + Database_Transaction_Log_Bytes_Reserved )/1024 AS [Total Log Space Used KB]
FROM SYS.DM_TRAN_Database_TransactionS AS DT JOIN SYS.DM_TRAN_SESSION_TransactionS AS ST ON DT.Transaction_ID=ST.Transaction_ID JOIN SYS.SYSPROCESSES AS SP ON SP.SPID = ST.SESSION_ID CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) SQLT 
WHERE DT.Transaction_ID > 1000 AND ST.Session_ID > 50


No comments:

Post a Comment