2

I have database with around 160 GB size. The database contains the data is from 2013 to until today, there are 180 tables, I want to remove the records in specific date range like 2013 to 2014 in all tables. How to do in SQL Server 2008, Is there any query technique or any tool in SSMS?

1
  • Please provide the table structure for all to see Jul 6, 2015 at 2:24

1 Answer 1

1

Deleting data is easy from the syntax point of view, but that's not all there is. DELETE FROM myTable WHERE someDate < deletionDate might give you a few nasty suprises.

Constraints might block you from doing a delete. A textbook example is a foreign key constraint between customer and order tables. Deleting a customer is not allowed as long as there are orders. If it would, you would end up having orders not linked to a customer. In order to overcome this kind of limitations, you must understand the database schema and start deleting from the right table first (or rely to constraint to use ON DELETE CASCADE.)

Don't underestimate the performance effects. If you are lacking proper indexing (or have overextensive indexing), deleting records might generate serious a performance hit by creating lots of unnecessary index updates and/or full table scans. In addition, write locks will cause overall performance hits for any operations from the affected tables.

Transaction log management is a common gotcha. Pruning a lot of data will generate lots of transactions and you log files will go sky high. As a best practice, break the delete operations in batches. Aaron's blog has detailed discussion about the issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.