How to find the log file usage for a transaction in SQL Server 2005?

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)
(  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
	         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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.