Post reply

turning off transaction logging

  • April 8, 2008 at 4:46 am

    #70163

    is there anyway to turn off transaction logging programmatically? i need to do

    deletes of large numbers of rows & the trans log grows to an unmanageable size.

    thanks in advance.

    Bill Riggio

  • April 8, 2008 at 7:04 am

    #799702

    Depending on a number of things: you could truncate the table if you wanted to completely empty the it.

    put the db in simple recovery and delete the records in batches.

  • April 8, 2008 at 7:13 am

    #799710

    i can't truncate since some rows need to remain.

    was under the impression that simple recovery mode just had to do with the backups of

    the trans log. just looking for a way to tell SQL not to play for a rollback since i can always

    reissue the deletes on failure.

    thanks for responding.

    Bill

  • April 8, 2008 at 7:21 am

    #799716

    You can't switch off logging it is a fundamental part of SQL Server.

    What is the ratio of records you want to keep? Maybe you could store those records in a temp table, truncate the table in question and then import the records from the temp table.

  • April 8, 2008 at 7:31 am

    #799721

    approx. 15% is to be deleted from a VLDB. unfortunately it would be way too much data

    to copy into a temp table.

  • April 8, 2008 at 7:36 am

    #799727

    Bill Riggio (4/8/2008)

    approx. 15% is to be deleted from a VLDB. unfortunately it would be way too much data

    to copy into a temp table.

    What you can do is to switch to simple recovery mode, and then break up your delete into smaller transactions (by deleting a fewer number of rows in individual transactions, your log space can be reused, and your log file will not grow) (make sure there are no long running transactions)

    You can break up the delete either by using TOP in the delete statement (2005), or by using "set rowcount", or by rewriting the where clause.

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • April 8, 2008 at 7:42 am

    #799732

    thanks - i've already broken up the deletes. will simple mode speed up the deletes?

    they are being done using the primary clustered index.

  • April 8, 2008 at 7:46 am

    #799737

    Simple mode won't necessarily speed them up, but it will help to keep the log file from growing. Any speed increase will really be from not having to stop, grow the log file, and then keep going.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • April 8, 2008 at 7:47 am

    #799741

    Bill Riggio (4/8/2008)

    thanks - i've already broken up the deletes. will simple mode speed up the deletes?

    they are being done using the primary clustered index.

    Switching to simple will avoid the transaction log file growing. Since disaster recovery does not seem to be important here (after all you wanted to switch logging off :)), it is OK. This will speed things up.

    Another thing to do/consider: disabling non-clustered indexes, foreign keys, etc, and rebuilding them after the deletes.

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • April 8, 2008 at 7:50 am

    #799749

    thanks all. really appreciate the help.

    Bill

  • April 9, 2008 at 11:03 am

    #800545

    Don't forget to update statistics when you're done!

    Steve G. 😀

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

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