delete records in sql without entry in transaction log

Question

delete records in sql without entry in transaction log

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

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

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

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 2

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.

Ryan SSC Veteran Points: 284 More actions · Answer 3

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

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.

forsqlserver SSCoach Points: 18902 More actions · Answer 5

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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/.

noahm31 SSC Journeyman Points: 93 More actions · Answer 7

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

sasher SSC Enthusiast Points: 190 More actions · Answer 8

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