Post reply

Log file for simple recovery db is 30GB

  • December 22, 2006 at 10:10 am

    #63025

    Hi everyone, and happy holidays to all of you!

    I have a problem with a database of mine. It is set to simple recovery mode and yet it keeps growing to over 30GB every week. I thought that in simple recovery mode there is little logging. What operations can cause so much logging? Or how can I pinpoint what script/statement it is that is responsible? We have several nightly jobs that run and execute many scripts....

    We are running SQL 2K SP4 on Win 2K SP$. Thanks for your help.

  • December 22, 2006 at 10:13 am

    #679778

    you can see a lot of log bloat if you have scheduled jobs that are doing things like DBCC Reindex or other similar maintenance operations. could that be what you are seeing? dropping or creating indexes, etc.?

    i seem to remember reading that that operation can cost 1.5X the space of the database, so a 20meg database bloats the log to 30 meg, or 50 in total until a checkpoint and  backup with truncate log

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 22, 2006 at 10:33 am

    #679786

    DBCC INDEXDEFRAG increased log bigger than db reindex...

    Check if there is any maintenance plan running for the db or REINDEX/INDEXDEFRAG  job is running... OR it could be a big data load as single transaction.

     

    MohammedU
    Microsoft SQL Server MVP

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply