Determining what T-SQL query or SQL Server client application is the source of log file growth is an often asked question.
“Old” SQL DBAs would sometimes refer you to KB 317375 which unfortunately is no longer hosted as a Microsoft KB Article, but sadly infinitely loops on one new Docs @ Microsoft page. Note also that the way the support page at Microsoft was built creates also issue for the WayBack Machine to display.
In order to keep a trace on how to proceed in such a case, let’s store in this article a few points on how to tackle this.
- Since SQL Server 2005, the answer is somewhere in the Dynamic Management Views. The goal is to roughly know which DMVs may be helpful here. Only objection granted to the reader: since SQL Server 2008, may be some Extended Event may also help you.
- In our case, the answer will be stored somewhere in the row of the sys.dm_tran_database_transactions DMV. Fields of interest start with database_transaction_log_bytes_*
- Unfortunately this view is not enough as the transaction in the database contains just an ID and not the query itself.
- To get the query we have to join the database transactions view on the session transactions view. From there, the session id of the session transactions view will allow us to get the T-SQL text in the dynamic management function sql_text by joining on the requests view.
- Notice that the sqltext field doesn’t give you the precise statement you’re hunting, but may give you the full text of a stored procedure if this statement is a part of such an object. You must use statement_start_offset and statement_end_offset to calculate the true “sub-statement” if it exists.
All those directions were more or less explained in the original article but the ready-to-use in-case-of-emergency full T-SQL solution was not part of this article nor it is part of the newer docs page. The ready to use statement is as follows:
<pre class="EnlighterJSRAW" data-enlighter-group="" data-enlighter-highlight="" data-enlighter-language="sql" data-enlighter-linenumbers="" data-enlighter-lineoffset="" data-enlighter-theme="" data-enlighter-title="">SELECT sesstran.session_id AS [spid]
, DB_NAME(dbtran.database_id) AS [dbname]
, QUOTENAME(DB_NAME(sqltxt.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(sqltxt.objectid, sqltxt.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(sqltxt.objectid, sqltxt.dbid)) AS sql_object
, req.command as [sql_command]
, SUBSTRING(sqltxt.text, ( req.statement_start_offset / 2 ) + 1
, ((CASE req.statement_end_offset
WHEN -1 THEN DATALENGTH(sqltxt.text)
ELSE req.statement_end_offset
END - req.statement_start_offset)/2)+1) AS [sql_command_param]
, dbtran.database_transaction_log_bytes_used / 1048576.0 AS [log_used_mb]
, dbtran.database_transaction_log_bytes_used_system / 1048576.0 AS [logsystem_used_mb]
, dbtran.database_transaction_log_bytes_reserved / 1048576.0 AS [log_reserved_mb]
, dbtran.database_transaction_log_bytes_reserved_system / 1048576.0 AS [logsystem_reserved_mb]
, dbtran.database_transaction_log_record_count AS [log_records]
FROM sys.dm_tran_database_transactions dbtran
JOIN sys.dm_tran_session_transactions sesstran ON dbtran.transaction_id = sesstran.transaction_id
JOIN sys.dm_exec_requests req
CROSS apply sys.dm_exec_sql_text(req.sql_handle) AS sqltxt
ON sesstran.session_id = req.session_id
ORDER BY 5 DESC;
This snippet is part of the repository of various tools I store in a SQL Server related Github repository.