Home Forums SQL Server 2008 SQL Server 2008 - General Delete records without logging RE: Delete records without logging

  • June 17, 2013 at 4:28 am

    #1624525

    Lokesh Vij (6/17/2013)

    There is nothing called "No Logging" in SQL Server...there is something called "Minimal Logging". This can be achieved by using table locking hints.

    DELETE FROM TableName with (TABLOCK) WHERE id > 100;

    For more details, refer this link:

    http://blogs.msdn.com/b/sqlserverstorageengine/archive/2008/03/06/minimal-logging-changes-in-sql-server-2008-part-1.aspx

    I agree with Gail. You cannot achieve minimal logging for DELETEs. It also usually takes a whole lot more than just using WITH(TABLOCK) to achieve minimal logging on inserts, as well. There's a whole set of rules in Books Online for what is necessary to achieve minimal logging.

    As Adi pointed out above, if you want to delete the entire contents of a table and it meets the rules he stated, then you could use TRUNCATE instead of DELETE and that will be minimally logged.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)