Post reply

Any way to delete data without Logging in T-log

  • December 7, 2009 at 8:59 am

    #156480

    Hi,

    I got a interesting requirement where I need to delete data in all the tables where planid<>2 of a database. As there are lot of tables I wrote a query to get tables which has column planid and started delting it..As we know delete logs each row in T-log ...so my t- log is keep increasing (Of course my DB is in simple mode ..) but I dont want sql server to log anything ...

    Is this option available in sql server?

  • December 7, 2009 at 9:08 am

    #1088635

    as far as i know there isnt an option to disable logging, except of course changing the RM to simple. Do a search in the articles from Lynne Petis (sorry if the spelling is wrong), she has done a really good article on deleting a large number of rows in batches using rowcount. That will limit the hit on your t-log

    if i find the link ill post it myself

    edit: just realised that RM will make no difference as your running it as a single transaction anyway.

  • December 7, 2009 at 9:09 am

    #1088636

    got it...

    http://www.sqlservercentral.com/articles/T-SQL/67898/

  • December 7, 2009 at 9:18 am

    #1088647

    Animal Magic (12/7/2009)

    as far as i know there isnt an option to disable logging, except of course changing the RM to simple.

    Changing the recovery model to Simple doesn't disable logging. With the exception of bulk-operations, the same amount of information is logged in full recovery as in simple. The difference is how long the log records are retained and what causes a truncation.

    There is no way to perform an unlogged operation in SQL. Some minimally logged (some depending on recovery model) but nothing is unlogged.

    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

  • December 7, 2009 at 9:20 am

    #1088649

    GilaMonster (12/7/2009)

    Animal Magic (12/7/2009)

    as far as i know there isnt an option to disable logging, except of course changing the RM to simple.

    Changing the recovery model to Simple doesn't disable logging. With the exception of bulk-operations, the same amount of information is logged in full recovery as in simple. The difference is how long the log records are retained and what causes a truncation.

    There is no way to perform an unlogged operation in SQL. Some minimally logged (some depending on recovery model) but nothing is unlogged.

    cheers gail, i did notice that after i posted so went back and edited it 🙂

  • December 7, 2009 at 9:25 am

    #1088658

    Thanks.

    Can we force sql server to truncate log at my convience (using checkpoint...) at the earliest rather than sql server doing by its own?

  • December 7, 2009 at 9:29 am

    #1088664

    kiransuram19 (12/7/2009)

    Can we force sql server to truncate log at my convience (using checkpoint...) at the earliest rather than sql server doing by its own?

    Yes. In Simple recovery the log is truncated on checkpoint. However, only inactive log records can be truncated, so if you're doing the delete as a single transaction, no amount of checkpoint is going to help, the transaction must be committed before checkpoint can truncate them and allow the space to be reused.

    See the article referenced above for a way to delete in chunks that doesn't blow the log up.

    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

  • December 7, 2009 at 9:35 am

    #1088671

    Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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