locked
How to find query or activity causing Log file growing extremly fast RRS feed

  • Question

  • Hi There,

    I tried to identify particular activities causing log file growing extremely. I'm using a Job to report the size of Log file, and I can see when this happening, and it recurring every day at same time. But I cannot to identify which query or activities caused. I search all the jobs, but I couldn't find anything. I hope anyone can help me to identify it.

    Thanks in advance,

    Tony

    Tuesday, August 2, 2016 1:48 AM

Answers

  • Hi Tony,

    Since you know the time when this happens, I would use sys.dm_tran_database_transactions and sys.dm_tran_session_transactions tables to get more information. Once you know the session, it's easy to get the query with sys.dm_exec_sql_text() or dbcc inputbuffer().

    Try this query out:

    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


    You can also look at the default trace to see who is growing the file. Try this query for the default trace:

    -- Get the oldest file in the Default Trace
    DECLARE @dftrc nvarchar(520), 
    	@filename nvarchar(520), 
    	@OldestFile nvarchar(520)
    
    SELECT @dftrc = path, 
    	@filename = REVERSE(SUBSTRING(reverse(path),0,CHARINDEX('\',reverse(path)))),
    	@OldestFile = REPLACE(path, REVERSE(SUBSTRING(reverse(path),0,CHARINDEX('\',reverse(path)))), '') 
    				+ (N'log_' + convert(nvarchar(520), (CONVERT(INT, REPLACE(REPLACE(REVERSE(SUBSTRING(
    				   reverse(path),0,CHARINDEX('\',reverse(path)))), '.trc', ''), 'log_', '')) 
    				- (max_files-1))) + N'.trc')
    FROM sys.traces 
    WHERE is_default = 1
    
    -- Display all Log File Auto Grow events
    SELECT ServerName, name, DatabaseName, FileName, LoginName, ClientProcessID, ApplicationName, SPID, Duration, StartTime, EndTime
    FROM ::fn_trace_gettable(@OldestFile, default) a
    	INNER JOIN sys.trace_events b on (a.eventclass = b.trace_event_id)
    WHERE name = 'Log File Auto Grow'
    ORDER BY StartTime DESC

    Here's some more info to consider as well.

    https://support.microsoft.com/EN-US/kb/317375

    https://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

    I hope those help!!


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)                         http://www.sqltechblog.com


    Tuesday, August 2, 2016 3:36 AM

All replies

  • Hi Tony,

    Based on your description, it seems you have enabled auto-growth on your log file and some huge query triggers auto-growth every day. I can think of two ways for you to gather some information about who/what triggered the auto-growth:
    1. Use SQL Profiler to track for log file auto-growth events. Simply open SQL Profiler, connect to your instance, then select “Log file auto growth” event from event selection tab in trace properties window.
    2. Enable default trace for you instance, then gather information from default trace. For more information, please review this blog.

    If you have any other questions, please let me know.

    Regards,
    Lin
    Tuesday, August 2, 2016 3:00 AM
  • Hello,

    See the query provided on below thread:

    http://dba.stackexchange.com/questions/59937/how-to-pinpoint-root-cause-of-excessive-log-file-growth

    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    • Proposed as answer by Shanky_621MVP Wednesday, August 10, 2016 1:45 PM
    Tuesday, August 2, 2016 3:19 AM
  • Hi Lin,

    Thanks for your quick reply. The issue is this log  growing happen very morning about 3:00 AM. Hardly for me using SQL Profiler, I knew I can use Profiler or sp_who2 to verify any query are running too long. I thought I might use DBCC OPENTRAN or select name, log_reuse_wait_desc from sys.databases  as a job to run every 5 or 10 minutes. But I'm looking for better solution.

    Regards,

    Tony 

    Tuesday, August 2, 2016 3:25 AM
  • Hi Alberto,

    Good ideal, using alert that triggered by size of Log file to monitor log file growing.

    Thanks,

    Tony

    Tuesday, August 2, 2016 3:34 AM
  • Hi Tony,

    Since you know the time when this happens, I would use sys.dm_tran_database_transactions and sys.dm_tran_session_transactions tables to get more information. Once you know the session, it's easy to get the query with sys.dm_exec_sql_text() or dbcc inputbuffer().

    Try this query out:

    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


    You can also look at the default trace to see who is growing the file. Try this query for the default trace:

    -- Get the oldest file in the Default Trace
    DECLARE @dftrc nvarchar(520), 
    	@filename nvarchar(520), 
    	@OldestFile nvarchar(520)
    
    SELECT @dftrc = path, 
    	@filename = REVERSE(SUBSTRING(reverse(path),0,CHARINDEX('\',reverse(path)))),
    	@OldestFile = REPLACE(path, REVERSE(SUBSTRING(reverse(path),0,CHARINDEX('\',reverse(path)))), '') 
    				+ (N'log_' + convert(nvarchar(520), (CONVERT(INT, REPLACE(REPLACE(REVERSE(SUBSTRING(
    				   reverse(path),0,CHARINDEX('\',reverse(path)))), '.trc', ''), 'log_', '')) 
    				- (max_files-1))) + N'.trc')
    FROM sys.traces 
    WHERE is_default = 1
    
    -- Display all Log File Auto Grow events
    SELECT ServerName, name, DatabaseName, FileName, LoginName, ClientProcessID, ApplicationName, SPID, Duration, StartTime, EndTime
    FROM ::fn_trace_gettable(@OldestFile, default) a
    	INNER JOIN sys.trace_events b on (a.eventclass = b.trace_event_id)
    WHERE name = 'Log File Auto Grow'
    ORDER BY StartTime DESC

    Here's some more info to consider as well.

    https://support.microsoft.com/EN-US/kb/317375

    https://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

    I hope those help!!


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)                         http://www.sqltechblog.com


    Tuesday, August 2, 2016 3:36 AM
  • 1.Who Is Active

    Who Is Active? v10.00 (2010-10-21)
    (C) 2007-2010, Adam Machanic

    Feedback: mailto:amachanic@gmail.com
    Updates: http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx
    "Beta" Builds: http://sqlblog.com/files/folders/beta/tags/who+is+active/default.aspx

    License: 
    Who is Active? is free to download and use for personal, educational, and internal 
    corporate purposes, provided that this header is preserved. Redistribution or sale 
    of Who is Active?, in whole or in part, is prohibited without the author's express 
    written consent.

    2.DBCC OPENTRAN


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, August 2, 2016 6:59 AM