deleting records without writing to transaction log

  • elaine-131617

    Ten Centuries

    Points: 1066

    Is there any way to delete records from a table without writing to the sql server transaction log?  I don't want to delete all the records in the table, only those created prior to a specified date.  I'm deleting a lot of records and the log is filling up.

    Thanks for any help.

    Elaine

     

     

  • Jim P.

    SSCrazy Eights

    Points: 8725

    Depending on how many records you want to save it might be faster to do a

    SELECT *

    INTO NewTableName

    FROM TableName

    WHERE DateField > DateParameter

    GO

    Truncate Table TableName

    GO

    INSERT INTO TableName

    SELECT *

    FROM NewTableName

    Just my $0.02.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    You could also set the recovery mode to simple so the log takes less space during this operation.

  • elaine-131617

    Ten Centuries

    Points: 1066

    Thank you.  The recovery mode is set to simple, but it's still filling up.  I will probably try Jim P's suggestion to save off the rows I want to keep, truncate the table and then insert the saved rows back.  I will end up saving less rows than I want to delete, so that is probably the best way to do it. I was being lazy and hoping that there was a way to just not write to the transaction log.

    Thanks again.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Can you imagine the implications of that possibility?

  • Jim P.

    SSCrazy Eights

    Points: 8725

    It sounds like you're overunning your checkpoints. Maybe try:

    SELECT YEAR(Datefield) MONTH(Datefield), MAX(Datefield)

    FROM TableName

    GROUP BY YEAR(Datefield) MONTH(Datefield)

    ORDER BY YEAR(Datefield) MONTH(Datefield)

    Then take the results and do search and replace:

    DELETE FROM TableName

    WHERE Datefield < "Your results MAX(Datefield)"

    GO

    That should also solve the problem, by deleting smaller chunks.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • Dave-3000

    SSCertifiable

    Points: 6186

    Remi:  You're right, how would systems function if anyone could turn off logging?  It would be like driving while talking on the cell phone; I know I can do it safely, but those other folks...

    There is no "i" in team, but idiot has two.
  • Chris Hedgate

    One Orange Chip

    Points: 25041

    Setting the recovery mode to simple would not make any difference here. That only lets SQL Server delete the log records before the MinLSN after a checkpoint is made. The problem here seems to be that the transaction is so large that the log runs out of space before the transaction is committed (which in turn would let SQL Server truncate the log). Jim P's solution(s) should solve the problem.

  • Julian Kuiters

    SSCertifiable

    Points: 6555

    Elaine, if there are no Foreign Key / Primary Key relationships on the table you can use the

    TRUNCATE TABLE myTableName

    command. It deletes ALL the data in the table without using the log file, regardless of recovery mode.

    It just unlinks the data pages, and takes only a few seconds for tables of millions of rows.


    Julian Kuiters
    juliankuiters.id.au

  • Frank Kalis

    SSC Guru

    Points: 111183

    It deletes ALL the data in the table without using the log file, regardless of recovery mode.

    Slightly incorrect. TRUNCATE is minimally logged. The deallocation of the datapages actually is recorded in the log. You can also use TRUNCATE within a transaction, and if it is rolled-back, the datapages are reallocated.

     

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Jeff Moden

    SSC Guru

    Points: 996810

    If the Recovery Mode is set to Simple, as you say it is, turn on the TRUNCATE LOG ON CHECKPOINT option and delete in smaller chunks.  You may have to do a Backup Log With Truncate_Only at the end of each sectional delete, as well, but understand that both actions do put your database at risk if the lights go out before your next backup.

    ...OR...

    One previous post made the suggestion of copying only the good stuff to another table.  If you set the Recovery Mode to Bulk Logged and do the copy to a new table using SELECT/INTO, then add the indexes and constraints, rename the old table to something else, and rename the new table to what the original table was, the log won't see more than a hiccup.  Side benefit... if the table you are doing this to is in use, your users will only see a 30 millisecond disturbance.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • elaine-131617

    Ten Centuries

    Points: 1066

    Thank you all for your advice.  Part of the problem is that we ran out of disk space, so I don't have enough room to save to another table the data I need to keep.  I'm going to delete in small increments until enough space is free for me to follow Jim's advice to save what I want to keep to another table and then truncate the original table.  It's a reporting table that no users hit directly, so nobody will be affected until the reporting job runs.  I really appreciate all of your responses.  Thanks again.

    Elaine

     

  • iadams

    SSCommitted

    Points: 1890

    Correct me if I'm wrong...

    If you delete a record using the query tool in Enterprise Manager (NOT Query Analyzer) I believe its not logged.  Thats why its so dangerous to mess with data using this too.

     


    -Isaiah

  • noeld

    SSC Guru

    Points: 96590

     >> Correct me if I'm wrong...

    If you delete a record using the query tool in Enterprise Manager (NOT Query Analyzer) I believe its not logged.  Thats why its so dangerous to mess with data using this too.<<

     

    Yep you are not right. It does not depend on the tool. All "delete" operations are logged and in the end EM uses "delete" too !

     


    * Noel

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Thank god... I was hopping he was wrong cause a few of my backups would have been wrong :-).

Viewing 15 posts - 1 through 15 (of 22 total)

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