deleting records without writing to transaction log

  • iadams


    Points: 1890

    For once, I'm glad I'm wrong too!


  • noeld

    SSC Guru

    Points: 96590

    Believe me I've had that feeling too


    * Noel

  • Peter E. Kierstead

    SSCarpal Tunnel

    Points: 4114

    -- Create a trigger on YourTable with the following code:

    Create Trigger [YourTableInsertTrigger] on [YourTable] Instead of Insert


    Truncate Table dbo.[YourTable]

    Insert dbo.[YourTable] Select * from Inserted

    -- Execute this in your script or procedure:

    Insert YourTable

    Select * from YourTable Where <criteria of rows you want to keep>

    I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

  • noeld

    SSC Guru

    Points: 96590

    That's a nice try but:

    • Trucate will Log Page deallocation
    • Truncate Requires Elevated Priviledges
    • Truncate can't be executed if the table has foreign keys
    • Truncate will LOCK the users out so if you try this in a multiuser environment you will get a lot of failed transactions


    ... Too many problems



    * Noel

  • Peter E. Kierstead

    SSCarpal Tunnel

    Points: 4114

    I'm just not feeling the luv...

    I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.

  • Jeff Moden

    SSC Guru

    Points: 996810

    Elaine wrote: " 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"

    Elaine, run the following command to reduce the size of the logged transactions, then shrink the database...


    ... that should free up some space...

    I don't normally recommend shrinking the database but this is an exceptional case.

    --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)

  • Kenneth Wilhelmsson


    Points: 30043

    The 'dangerous' part about using EM for changes, is that you have no explicit transaction control. If you click the wrong button - oops - no other choice than to take the system offline and start digging for the latest good backup.. (and perhaps also start thinking of a good explanation to the PHB)

    In QA you can do:


    <do some SQL>

    ..if all went as expected, COMMIT

    ..if something funky happened, ROLLBACK

    ..after that, you're back to square on (boss and users hopefully unaware) and you can figure out what went wrong and how to fix it.


Viewing 7 posts - 16 through 22 (of 22 total)

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