I need a plan to cleans all the tables in a database for every 9 months.

Question

I need a plan to cleans all the tables in a database for every 9 months.

m.rajesh.uk

Hall of Fame

Points: 3653
More actions
February 6, 2013 at 9:30 pm

#273905

I need a plan to cleans all the tables in a database for every 9 months.
what are the best ways to do that.This time period can be modified.

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

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

nishantcomp2512 Grasshopper Points: 21 More actions · Answer 1

create a blank schedule job.

after creating job you can add schedule to it using stored procedure sp_add_jobschedule and stored procedure update schedule using sp_update_jobschedule.

http://msdn.microsoft.com/en-us/library/ms178560.aspx

Thanks,

Nishant

ID:nishantcomp2512

vinu512 SSCoach Points: 15729 More actions · Answer 2

m.rajesh.uk (2/6/2013)
cleans all the tables in a database

What do you mean by cleaning tables??

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

demonfox SSCertifiable Points: 6289 More actions · Answer 3

m.rajesh.uk (2/6/2013)
I need a plan to cleans all the tables in a database for every 9 months.
what are the best ways to do that.This time period can be modified.

you plan should be , if this is the first time you are implementing it ; start with a history table creation ; delete records with 9 months prior data and keep that backup in hostory table ; after a while(time decision) , if you are sure , or the business suggests, that data is unusable, then purge that from the history tables;

And you need scheduler jobs for that ..

As quoted by Nishant , check that link.

One for deleting from main table and parallely inerting in history table ; and one for purging history table.

I think should do ..

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:

demonfox SSCertifiable Points: 6289 More actions · Answer 4

vinu512 (2/6/2013)
m.rajesh.uk (2/6/2013)
cleans all the tables in a database
What do you mean by cleaning tables??

Yes , An edit regarding this ;

For staging tables cleaning , one job should do the work and history tables shouldn't be required..

But, generally , while deleting from transaction table ; which often happens when ununsed data grows in the table and read from the table starts to take too much time ; it's better to have history table . that's how I follow ..

~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: