Delete Records older than 90 days

Question

Delete Records older than 90 days

div4u22

Old Hand

Points: 339
More actions
October 20, 2010 at 9:41 pm

#240001

I have two tables one contains @300 millions records & the other contains @ 500 millions of record.I want to create two stored Procedure that will delete records older than 90 days.Also will need to remove errors from error table for any records removed in the both table..i appreciate any help to solve this issue..

Viewing 5 posts - 1 through 4 (of 4 total)

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

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 1

div4u22 (10/20/2010)
I have two tables one contains @300 millions records & the other contains @ 500 millions of record.I want to create two stored Procedure that will delete records older than 90 days. i appreciate any help to solve this issue..

Why do you want 2 Store procedures to delete, it cant be deleted in single sp ?

Also will need to remove errors from error table for any records removed in the both table

Delete from Error_table As er join Mydata_table1 as my where er.Commonid = my.Commonid

and my.Datefield< Convert(Varchar,GETDATE()-90,101)

Delete from Error_table As er join Mydata_table2 as my where er.Commonid = my.Commonid

and my.Datefield< Convert(Varchar,GETDATE()-90,101)

that will delete records older than 90 days.

Delete from Mydata_table1 Where Datefield< Convert(Varchar,GETDATE()-90,101)

Delete from Mydata_table2 Where Datefield< Convert(Varchar,GETDATE()-90,101)

Thanks
Parthi

Brandie Tarvin SSC Guru Points: 173003 More actions · Answer 2

Could you please post your table structure and some sample data so we could verify the advice we're giving you before we give it? It's hard to help you when we don't know what you're working off of.

Also, what have you tried before this?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

kbadarinath SSC Veteran Points: 269 More actions · Answer 3

I would like to delete records from all tables which is older than 90 days. I know that stored procedure can perform this task. But is there a easy way in Management studio to cleanup data on a periodic basis using maintenance plans at the object level.

Thanks

Brandie Tarvin SSC Guru Points: 173003 More actions · Answer 4

Please post this as a new question to the forum. Most people won't read an old thread.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.