delete records in sql without entry in transaction log

Question

Post reply

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

BaldingLoopMan

SSCertifiable

Points: 6378

November 19, 2009 at 7:00 am

#1081526

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: 442447 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

forsqlserver

SSCoach

Points: 18902

July 9, 2012 at 7:09 am

#1510320

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: 442447 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://qa.sqlservercentral.com/articles/T-SQL/67898/.