SQL Query to delete records older than two years

Question

I need to clear a very bloated SQL database by deleting records that are over two years old from multiple tables. What is the most efficient way to do this?

Answer 1

Do you have any way to determine how the "old" record is? (i.e. is there a column in the table that represents either the age of the row or the date that can be used to calculate age?). If so, it should just be

DELETE FROM Table WHERE Age > 2 

For example, if you have a DateTime column named CreateDate , you can do this:

 DELETE FROM Table WHERE DATEADD(year, 2, CreateDate) < getdate() 

Answer 2

In addition to Adam Robinson, a good answer is: When performing this type of operation:

• First run a SELECT query with a DELETE WHERE clause to make sure that you are getting the "right data"
• Make a full backup
• Launch the application after hours so as not to affect users too much.

Answer 3

I saw how dba does this in several different companies, and it always seems to use the following format:

• Backup table
• Drop any indexes
• Select the rows you want to keep in the temp table
• Trim Source Table
• Insert (into source table) from temp table
• Recover Indexes

The advantage of this approach is that this update is not written to the logs, so they do not fall into thousands of deleted records. It is also faster.

The downside is that the update is not written to the logs, so your only option is to restore the backup.

You should consider keeping the house in place. If the above is too scary, then you can also use the house to display the database for a certain time.

In MSSQL, you can create a daily task that will delete the first 1000 lines of your query. To steal Adam’s request -

DELETE TOP 1000 FROM WHERE DATEADD table (year, 2, CreateDate) <GETDATE ()

It would be very safe and get rid of your data in three months or so safe, and they would also save the db size in the future.

Your database will use this space in the future, but if you want to restore the space, you will need to compress the database. Read if you are wondering if this is worth it, depends on the amount of recovery space compared to the total db size.