Which query is causing to the log file growth.

 Sometimes when we are online, we could see that log file grow continuously. The below query will tell us which query is causing to that logfile growth of that particular database.



SELECT	session_id, 
		dt.transaction_id, 
		DB_NAME(database_id) as DB, 
		sum(database_transaction_log_bytes_used) TotalLogBytesUsed
FROM sys.dm_tran_database_transactions dt
	LEFT JOIN sys.dm_tran_session_transactions st on (dt.transaction_id = st.transaction_id)
GROUP BY session_id, dt.transaction_id, database_id
ORDER BY 4 DESC

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server