Word of caution before you try/read this: I am using undocumented function to read the trace file. I do not grantee the results to be accurate, please use it your own risk.
Client was wondering if there was anyway we can figure out how much log file is being used for each statement executed on SQL Server? The simple answer is NO. We can look at the Log Bytes Flush/sec in the SQL Server: Database object; however it doesn’t really correlate back to which transaction did the work. So I decided to some random testing using fn_trace_gettable (Used to read Trace Files) and fn_dblog (Undocumented Command).
First open and setup a Trace with with following events/columns; you can add more events if you think it is needed for example StmtCompleted.
Please note this is minimal data needed, you are free to add additional data however it will not be needed for purpose of finding T-Log file usage.
Next turn on the trace and execute some random statements that will be logged to T-Log. After you have finished capturing your trace events, stop the trace and save the trace file where you can read it using fn_trace_gettable function.
Now execute following statement in the context of the DB we want to track log space usage:
WITH TranTotal (TransactionID, TotalLogFileUsage, OperationCount)
AS
( SELECT [Transaction ID]
, SUM([Log Record Length])
, COUNT(*)
FROM sys.fn_dblog(null,null)
GROUP BY [Transaction ID])
SELECT dblog.[Transaction ID]
, OperationCount
, T.TextData
, TotalLogFileUsage
, dblog.[Begin Time]
FROM sys.fn_dblog(null,null) dblog
INNER JOIN TranTotal TT
ON dblog.[Transaction ID] = TT.TransactionID
LEFT OUTER JOIN sys.fn_trace_gettable('C:\temp\TraceDataForCalculation.trc',default) T
ON dblog.[Begin Time] = T.StartTime
From which you’ll get output like below; please note one batch can have more the one transaction. I am not quite sure how it is deteremined batch id, if I find more on this I will post.
Where the TextData is NULL my Trace File did not have any data, since I justed my regular test database and I have never done back on it, I have transactions in there from before.
PS I am sure Extended Events makes this easy, rightt now 2005 was goal. I’ll post on 2008 later when I have “spare time” :p.
SQL Canada (SQLCAN) 