Delete all records in database older than 2 years

Question

Post reply

Delete all records in database older than 2 years

AJS101

SSC Veteran

Points: 233
More actions
June 23, 2008 at 10:43 am

#188402

I have been trying to find a good sql statement which would delete all records within a particular database which is older than 2 years.
I need to keep all information 2 years and newer but can get rid of the rest after 2 years.
What would be the best solution to do this? I am using SQL 2005

Viewing 7 posts - 1 through 6 (of 6 total)

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

noeld SSC Guru Points: 96590 More actions · Answer 1

You will need a "last_updated" column on all your tables. Do you have that ?

* Noel

Stupeo Hall of Fame Points: 3067 More actions · Answer 2

If you have a column that has the date the record was inserted stored in it, then i would create a SQL Job to run every night and execute the following sql:

delete from myTable where myDateColumn < dateadd(yy, -2, getdate())

Matt.

rajankjohn SSCertifiable Points: 5061 More actions · Answer 3

and make sure you disable the index first, then do the delete, and then enable the index. Make sure you have enough space for the transaction logs as well, otherwise, delete in batches

http://rajanjohn.blogspot.com

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

The first delete will probably be a good size one... but if you delete every day, the number of rows older than 2 years won't be that big... you won't need to do it in batches... and you don't need to disable indexes to do it... 😉

This type of thing can be made to run so fast, that I've got one table where everytime something does an insert to it, a trigger fires and deletes everything older than 60 days... same idea as 2 years... if you delete often enough, you won't have to delete much at any one time (except the very first time)...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

toniupstny Hall of Fame Points: 3986 More actions · Answer 5

You might want to determine if there might be any reason (legal requirements or self-preservation) to keep an archive of the deleted records in a separate database. This would allow better performance in the original db while preserving the historical information. This could be done as part of a Delete function or in a trigger. If the reason to do the delete is disk space constraints, then this might be impractical.

Toni

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

toniupstny (6/26/2008)
You might want to determine if there might be any reason (legal requirements or self-preservation) to keep an archive of the deleted records in a separate database. This would allow better performance in the original db while preserving the historical information. This could be done as part of a Delete function or in a trigger. If the reason to do the delete is disk space constraints, then this might be impractical.
Toni

Heh... yeah... if you think SOX is tough, wait until you get hit with the new "E-Discovery" requirements... makes SOX look like a walk in the park...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions