asked
started sql server
Hi I have a table where I am getting 150 millon rows every day. What sp should i write so that all records after 90 days get deleted. Could somebody help me in it(script).And how will I schedule it so taht it automatically deletes the records after 90 days. Thanks!
There was a [similar question][1] someone asked in October of last year, it has pretty good discussions. One of the answers there offers an excellent technique completed with the code sample which you might find useful. While that answer by @Scot Hauder is not the one with most votes, it is really good. Please check it out. [1]:
http://ask.sqlservercentral.com/questions/27158/deleting-10-million-records-from-a-database-table
Are you a DBA? Have you tried starting this project on your own yet? I don't know that you can get much assistance with the information you provided. Maybe provide the script/sp that you have started with...
As you probably already know, you can't delete 150 000 000 records in one transaction. The best solution for you would be partitioning and sliding window, that's ligthening fast, but there are a lot of rules to follow. On the other hand , if you don't use partitions with this amount of data, I strongly recommend you to implement it. You'll get a significant performance boost if you select correct partitioning scheme. Search for sliding window in books online and you will find what you need or look at this question [
http://ask.sqlservercentral.com/questions/27158/deleting-10-million-records-from-a-database-table][1] Second best option is to delete the records in smaller chunks, but be careful so you dont get a table scan for each chunk you try to delete. [1]:
http://ask.sqlservercentral.com/questions/27158/deleting-10-million-records-from-a-database-table
