none
SQL Server 2008 R2 transaction log file continues to grow despite log backups

    Question

  • I have a database set to Full recovery model and stored procedures maintain the backups for all database.  They do a full backup weekly, differential daily, and transaction logs hourly.  With the exception of the one database the log files behave as expected witht the log file itself remaining a resonable size and the transaction log backups remaining tiny.

    The exceptional database has a data file with a size of 398,784 KB.  Running profiler I see only SELECT activity caused by a Windows application that polls the database every 5 seconds.  There are no data modification operations occurring and I have run DBCC CHECKDB against the dtabaase with no errors.

    I got an alert that told me the backup job failed, and when I looked at the drive it had been filled with 5 GB transaction log backups for the problematic database.  In order to get the system back up quickly I deleted all the log backups, performed a full backup, set the database to Simple revovery mode, shrank the log file, then set it back to full recovery and took another full backup.  Despite this, the log file continues to grow rapidly.  It went from something under 200k to 2.5 GB in just over an hour.  It seems to jump in size at random times and sizes, but the increases seem to be in the range of 200,000 - 300,000 KB.

    As I finish this the transaction log is just under 7 GB, the last hourly transaction log file was about 6GB and the one before that was 2 GB.  The size of the database has not changed.  I will have to set it back to Simple recovery mode until I can get this figured out.

    Thanks in advance for any suggestions.

    Saturday, July 14, 2012 5:15 PM

Answers

  • If no jobs are being triggered.

    Please capture following events in SQL Server Profilers with NO FILTERS.  The Transaction Log will not grow free of size.  Another thing you can consider doing is reading the T-Log in raw format to see what kind of changes are taking place it (seems a bit of overkill but if profiler doesn't get anything I am a bit lost).

    My thought is your application is doing something you are not catching in profiler.  Either of not the right events being captured or filtering.

    • Database\Log File Auto Grow
    • Object\Object: Altered
    • Object\Object: Created
    • Object\Object: Deleted
    • Performance\Auto Stats
    • Progress Report\Progress Report: Online Index Operation
    • Stored Procedures\RPC:Completed
    • TSQL\SQL:BatchCompleted

    All Columns.

    If this still does not show anything interesting...

    Try fn_dblog function to read the transaction log, here is an example on my blog, http://sqlcan.wordpress.com/tag/fn_dblog/. 

    You can also try fn_dump_dblog to read the backed up t-logs if you still have them, check out http://www.sqlskills.com/BLOGS/PAUL/post/Using-fn_dblog-fn_dump_dblog-and-restoring-with-STOPBEFOREMARK-to-an-LSN.aspx.

    Cheers!


    Mohit K. Gupta --- MCTS, MCITP, MCC2011
    My Blog OpsVault My MSDN Blog Twitter @SQLCAN

    Saturday, July 14, 2012 8:53 PM

All replies

  • If you only have SELECT activity against the database, than there is no real reason for transaction log to go by such large amounts.  Please verify you don't have any SQL Server Jobs or task that might be updating indexes and statistics on the database.  Alternatively you can look at how often are the statistics being updated by Auto_Update_Statistics?  I doubt that is the problem though. 

    If you are willing to delete your transaction log for disk recovery, why not leave the database in simple recovery model?  What events did you capture in your SQL Server Profiler trace?

    Does the database in question have any kind of extras on it, Replication or Database Mirroring?

    Please check the LOG_REUSE_WAIT_DESC field to see why the transaction log is not being reused.

    SELECT * FROM sys.databases

    Thanks.


    Mohit K. Gupta --- MCTS, MCITP, MCC2011
    My Blog OpsVault My MSDN Blog Twitter @SQLCAN

    Saturday, July 14, 2012 5:42 PM
  • Thanks for the quick reply.

    We are migrating the databases to a new hosting provider and have not yet gone live on the new server, so I'm ok with losing transaction log backups right now and I will leave the recovery mode simple until we figure out this issue.

    In profiler I see mostly selects on one table that is apparently used for signaling changes back to the app that is polling.  There were a very few updates - maybe 2-3 per hour - that seemed to target one row, so I don't think it's a runaway update in which the WHERE clauses was omitted.  The database belongs to a 3rd party application that I am not yet familiar with and we are awaiting a reply form the vendor about the issue now.

    There are no scheduled jobs and the there is no replication or mirroring going on.  Right now, LOG_REUSE_WAIT_DESC is 'NOTHING', but since I'm in Simple recovery that's what I would expect.  If I don't get a reasonable answer from the vendor I'll set it back to Full recovery when I'm in a position to monitor the disk usage and see if that chnages to something else.

    Saturday, July 14, 2012 5:59 PM
  • Take a look at these different causes of transaction log file growth (taken from this Microsoft KB article). 

    • Uncommitted transactions
    • Extremely large transactions
    • Operations: DBCC DBREINDEX and CREATE INDEX
    • While restoring from transaction log backups
    • Client applications do not process all results
    • Queries time out before a transaction log completes the expansion and you receive false 'Log full' error messages
    • Unreplicated transactions

    My first instinct would be to look at active/open transactions in the log using DBCC OPENTRAN because a LOG backup will only backup the inactive portions of the log. If you have active transactions, these will cause your log file to grow and be skipped by the LOG backup


    Edwin Sarmiento SQL Server MVP
    Blog | Twitter | LinkedIn


    Saturday, July 14, 2012 8:44 PM
  • If no jobs are being triggered.

    Please capture following events in SQL Server Profilers with NO FILTERS.  The Transaction Log will not grow free of size.  Another thing you can consider doing is reading the T-Log in raw format to see what kind of changes are taking place it (seems a bit of overkill but if profiler doesn't get anything I am a bit lost).

    My thought is your application is doing something you are not catching in profiler.  Either of not the right events being captured or filtering.

    • Database\Log File Auto Grow
    • Object\Object: Altered
    • Object\Object: Created
    • Object\Object: Deleted
    • Performance\Auto Stats
    • Progress Report\Progress Report: Online Index Operation
    • Stored Procedures\RPC:Completed
    • TSQL\SQL:BatchCompleted

    All Columns.

    If this still does not show anything interesting...

    Try fn_dblog function to read the transaction log, here is an example on my blog, http://sqlcan.wordpress.com/tag/fn_dblog/. 

    You can also try fn_dump_dblog to read the backed up t-logs if you still have them, check out http://www.sqlskills.com/BLOGS/PAUL/post/Using-fn_dblog-fn_dump_dblog-and-restoring-with-STOPBEFOREMARK-to-an-LSN.aspx.

    Cheers!


    Mohit K. Gupta --- MCTS, MCITP, MCC2011
    My Blog OpsVault My MSDN Blog Twitter @SQLCAN

    Saturday, July 14, 2012 8:53 PM