Post reply

Stop logs from growing

  • May 19, 2011 at 7:32 am

    #251316

    Have a large table and when I delete records from that table, the log grows and it runs out of disk space. How can I stop the logs from growing?

    Thanks.

  • May 19, 2011 at 7:36 am

    #1327059

    Delete in smaller batches and take log backups every x loops.

  • May 19, 2011 at 7:54 am

    #1327077

    This is test database with limited diskspace. I do not want the logs to grow. Is that possible?

    Thanks.

  • May 19, 2011 at 7:56 am

    #1327081

    Delete in small loop. Switch db to simple recovery. Make sure you don't care about losing all your data and changes between backups tho.

  • May 19, 2011 at 9:10 am

    #1327155

    DB is on Simple recovery. I have over 91 million records. Deleting in small chunks will take long time. This is not the only time that I need to delete. So I am looking for a permanent solution. Is it possible to have the log not grow?

    Thanks.

  • May 19, 2011 at 9:15 am

    #1327159

    NO.

    How much data will you keep out of 91 M rows?

  • May 19, 2011 at 9:18 am

    #1327163

    Deletes are logged operations. They are always logged operations. The log needs to be big enough to accommodate ((size of row being deleted + size of log headers) * number of rows being deleted) at least.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 19, 2011 at 10:22 am

    #1327211

    Gail,

    That was the answer I was looking for. Thanks.

  • May 19, 2011 at 10:23 am

    #1327213

    ramadesai108 (5/19/2011)

    Gail,

    That was the answer I was looking for. Thanks.

    You're still ignoring obvious solutions.

    How much data will be left in the table once the delete is over?

  • May 19, 2011 at 11:45 am

    #1327252

    ramadesai108 (5/19/2011)

    I have over 91 million records. Deleting in small chunks will take long time.

    You'd think so, but in general deleting in chunks is faster than a massive delete. Not suggesting you do it manually, stick it in a loop with a checkpoint in there. Or, what Ninja is hinting at, if more data will be removed than remaining, insert the rows you want to keep into a temp table, truncate the massive table, then insert the rows back.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 9 (of 9 total)

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