delete records in sql without entry in transaction log

  • asingh-773142 (11/18/2009)


    To fast delete records with a "where" clause. You will have to do a little work but it is nothing compared the time it takes to delete the records.

    Try the following:

    1. Script all the primary and foreign key constraints, Identity and indexes etc.

    2. Remove all the primary and foreign key constraints, Identity and indexes etc.

    3. Delete the records using your where clause.

    4. Re-run the script to set the primary and foreign key constraints, Identity and indexes etc.

    Thanks

    Amarjot

    You forgot about replication and triggers...

    Also, why remove identity stuff for deletes??

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • rather than delete u could recreate a version of the table w just the records u want then do the old switcheroo. Rename it to _archive then rename the new table to the original tables name.

  • If your table is very large and the time it takes to delete a subset of the data is an issue then perhaps you could partition the table? Assuming it is, then you can switch out a partition and drop it as a separate table very quickly. Since effectively no data moves or is deleted the transaction is fast fast.

    That's right, so fast I said it twice. 😉

    Ryan
    -----------------
    www.quadrus.com

  • Ryan (1/29/2010)


    If your table is very large and the time it takes to delete a subset of the data is an issue then perhaps you could partition the table? Assuming it is, then you can switch out a partition and drop it as a separate table very quickly. Since effectively no data moves or is deleted the transaction is fast fast.

    That's right, so fast I said it twice. 😉

    And this will only work if you are running the Enterprise Edition of SQL Server 2005.

  • I have the transactional replication running on my database,and to release some space i need to delete the 10000000 rows on 3 tables.The data can't be partitioned.

    I need to delete data witout increasing log space.

    Please tell the post steps after deletion of data.

    Thanks

  • forsqlserver (7/9/2012)


    I have the transactional replication running on my database,and to release some space i need to delete the 10000000 rows on 3 tables.The data can't be partitioned.

    I need to delete data witout increasing log space.

    Please tell the post steps after deletion of data.

    One place to start is with this article, http://www.sqlservercentral.com/articles/T-SQL/67898/.

  • Thank you for the great advice!

    It normally takes over 4 minutes to delete all records in one of my tables but using the set rowcount I can do it in less than a minute.

    Noah Meyer
    www.sqlfixitguy.com

  • better than set rowcount use top option in the loop. It's even faaster.

    wrote this before Sybase had the top option .. sql server always had it.

    begin tran

    delete top 500 (sybase syntax)

    delete top(500) (sql server syntax)

    commit tran

    instead of

    set rowcount 500

    begin tran

    delete from etc

    commit tran

Viewing 8 posts - 31 through 37 (of 37 total)

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