mayurkb (6/25/2013)
I'm designing an application which will be run everyday at a scheduled time and it will clean up old data from the database based on its age. I wanted to ask which factors should be considered in designing an app like this.Constraints and known things:
1. This will be run while other applications will still be running.
2. Right now I do not have any numbers like how much it will delete every day, since this is a new product.
3. This will delete data from approx 30 tables, out of which 7 are main tables and remaining are their child tables. The thing that I want to point out is that I need to have at least 7 transactions if not 1 big transaction.
I would really appreciate if anybody can point to some link or have done this kind of thing before and respond with some ideas.
Thanks in advance.
Not much in the way of details here. I have far more questions than answers.
How are you going to handle concurrency? You said other applications will be running at the same time. This could be a huge problem depending on how you deal with it.
You need to have some kind of idea what is going to be deleted based on the application. You have enough details that you know how many tables are involved but you have no concept of the volume of data? I would recommend figuring that out before you go any further. It is tough to design a successful system without some kind of idea for data volume. The amount of volume can make a HUGE difference in how you approach this. If it is hundreds or thousands of rows it will be a vastly different animal than if there are tens of millions of rows.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/