locked
TLog grows huge in simple recovery mode RRS feed

  • Question

  • I hAVE SQL SERVER 2005 user database that is set in SIMPLE recovery mode.

    The Tlog size is very huge and  occupies nearly upto the disk ..

    The Transaction Log for this database keeps on increasing more than 10-15 times than that of the .mdf files even cleaered a couple of times.. 

    It would be great if any suggests to avoid this situation and solve this issue permanently..

    Thursday, June 24, 2010 11:18 AM

Answers

  • Hi

    Check out DBCC OPENTRAN command to see open/active transactions

    Run DBCC SHRINKFILE to reduce physical size of log


    Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Tom Li - MSFT Monday, June 28, 2010 2:19 AM
    • Marked as answer by Tom Li - MSFT Wednesday, June 30, 2010 1:49 AM
    Thursday, June 24, 2010 11:34 AM
    Answerer
  • Please Refer http://msdn.microsoft.com/en-us/library/ms345414.aspx which explain you the factors that can delay log truncation.

     


    HTH Thanks, Narendra Angane
    • Proposed as answer by Tom Li - MSFT Monday, June 28, 2010 2:19 AM
    • Marked as answer by Tom Li - MSFT Wednesday, June 30, 2010 1:49 AM
    Thursday, June 24, 2010 11:46 AM
  • All transactions are logged to the transaction log.  Simple recovery only determines when the log space can be reused.  The log is large because it needs all that space.  You need to look at your processes to find out why your transactions are so large.

     

    • Proposed as answer by Tom Li - MSFT Monday, June 28, 2010 2:20 AM
    • Marked as answer by Tom Li - MSFT Wednesday, June 30, 2010 1:49 AM
    Thursday, June 24, 2010 1:48 PM

All replies

  • Hi

    Check out DBCC OPENTRAN command to see open/active transactions

    Run DBCC SHRINKFILE to reduce physical size of log


    Best Regards, Uri Dimant http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Proposed as answer by Tom Li - MSFT Monday, June 28, 2010 2:19 AM
    • Marked as answer by Tom Li - MSFT Wednesday, June 30, 2010 1:49 AM
    Thursday, June 24, 2010 11:34 AM
    Answerer
  • Please Refer http://msdn.microsoft.com/en-us/library/ms345414.aspx which explain you the factors that can delay log truncation.

     


    HTH Thanks, Narendra Angane
    • Proposed as answer by Tom Li - MSFT Monday, June 28, 2010 2:19 AM
    • Marked as answer by Tom Li - MSFT Wednesday, June 30, 2010 1:49 AM
    Thursday, June 24, 2010 11:46 AM
  • I will suggest to fix the Tlog size Not to autogrow. As there is no point in keeping the Transaction log to auto grow in simple recovery mode.

     


    Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.
    Thursday, June 24, 2010 12:27 PM
  • Hi Rajesh,

    This is the time you need to revisit the T-SQL code and the Indexes created on the table(s).

    If everything is fine, it is better to break the code (which causes the growing Tlog) into convenient parts without losing the consistency of the transaction.

     --------------------------------

    Thanks,

    RajaSekhar Reddy . K

     

    Thursday, June 24, 2010 12:48 PM
  • All transactions are logged to the transaction log.  Simple recovery only determines when the log space can be reused.  The log is large because it needs all that space.  You need to look at your processes to find out why your transactions are so large.

     

    • Proposed as answer by Tom Li - MSFT Monday, June 28, 2010 2:20 AM
    • Marked as answer by Tom Li - MSFT Wednesday, June 30, 2010 1:49 AM
    Thursday, June 24, 2010 1:48 PM