SQL Query to delete records older than two years

I need to clean out a very bloated SQL database by deleting records that are older than two years from a number of tables. What is the most efficie开发者_运维百科nt way of doing this?.

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

DELETE FROM Table WHERE Age > 2

For example, if you have a DateTime column called CreateDate, you could do this:

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

In addition to Adam Robinson's good answer: When performing this type of operation:

1. Run a SELECT query with the DELETE's WHERE clause first to make sure you're getting "the right data"
2. Do a full backup
3. Run the thing in "off" hours so as not to affect users too much

I've seen dba do this in a few different companies and it always seems to use the following format:

1. Backup the table
2. Drop any indexes
3. Select the rows you want to keep into a temp table
4. Truncate the original table
5. Insert (into your source table) from you temp table
6. Recreate the indexes

The benefit to this approach is that this update doesnt write to the logs so they don't get blown by thousands of delete entries. It's also faster.

The drawback is that the update doesn't write to the logs so your only option is to restore your backup.

You should think about putting house keeping in place. If the above, is too scary, then you could also use the house keeping to winnow the database over a matter of time.

In MSSQL, you could create a job to run daily which deletes the first 1000 rows of your query. To steal Adam's query -

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

This would be very safe and would get rid of your data in three months or so safely and would them also maintain the size of the db in the future.

Your database will get to use this space in the future but if you want to recover the space you will need to shrink the database. Read around if you are interested - whether it is worth it depends on the amount of space to recover versus the total size of the db.