July 16, 2015 at 7:32 am
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.
July 16, 2015 at 7:37 am
kbadarinath (7/16/2015)
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.
Hi and welcome to the forums! If you already know how to delete the data in your system (it takes a column in your table to identify the date) then the easiest approach would be to create your stored procedure with all the deletes. Then just setup a sql job on a schedule to run how frequently you want.
_______________________________________________________________
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/
July 16, 2015 at 7:42 am
Basically, what Sean said.
There's no option included in SSMS because most people won't delete rows older that a certain amount of time. There's also no standard on defining the date column to be used to define how old a row is.
July 16, 2015 at 7:48 am
Even if there was a maintenance plan item that could do it for me, I would still my write own stored procedure to do it. This way I would be sure it's being done the way I want it to be done.
Whether you run it as a job on a schedule, or run it manually is up to you.
Add my vote to what the others already said.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 16, 2015 at 7:52 am
+1 to what's out there. This may change with Temporal tables and SQL Server 2016, but for now, write your own query.
I suggest you write a select, be sure it returns what you want to delete and then convert that to a delete.
July 16, 2015 at 8:02 am
Let me clarify something here. I don't think I will take an answer and implement that solution without testing it.
I wanted to know if there is anything out there other than writing code.. I am not familiar with SQL Server, thats why trying to find out.
Thanks
July 16, 2015 at 8:06 am
kbadarinath (7/16/2015)
Let me clarify something here. I don't think I will take an answer and implement that solution without testing it.I wanted to know if there is anything out there other than writing code.. I am not familiar with SQL Server, thats why trying to find out.
Thanks
Before deleting these records, make sure you have good backups, just in case you find out later you deleted records you did not mean to delete.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 16, 2015 at 8:08 am
And if that means deleting millions of rows in any of the tables, you should consider implementing a batch delete process to avoid excessive locking and TLog growth.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 16, 2015 at 8:19 am
Phil Parkin (7/16/2015)
And if that means deleting millions of rows in any of the tables, you should consider implementing a batch delete process to avoid excessive locking and TLog growth.
Agree 100% with Phil.
If you have that many rows, then you should also consider deleting theses rows as often as possible and practical, to reduce the number of rows to be deleted.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 16, 2015 at 8:31 am
Sorry, some posts got crossed.
To answer your questions, no, no way other than writing code.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply