Database Maintenance

[ekendricks]

I have several tables where data needs to be deleted, based on some value, on a periodic basis to prevent gigantic volumes of obsolete data in the database.

Also have several preformated mass emails that needs to be sent on a periodic basis based on some value.

I have scripts-routines-sql stmts that says in effect "Delete from table something where DatePosted is < = Date().

As of now, I'm having to manually open a page with these functions listed, and then execute the ones that needs to be executed. What I would like to see happen, is that these functions are run automatically at some given time based on some criteria, without daily-weekly-monthly involvement from me.

If someone could please give me some pointers as to how this is accomplished, it would be greatly appreciated.

Thanks

Ernest

[Webforumz Staff]

What db are you using and are you running this on your own server?

[ekendricks]

I'm using SQL 2K and a shared hosting service.

Ernest

[Webforumz Staff]

I'm not sure what's locked down on your shared SQL account, you might not have permissions to do this. But what you want to do is add a job using a SQL Agent. It's in the Management folder of your SQL account. It's pretty self explanatory to set up.

If you don't have permissions for this there's a couple other things you can set up, but they'll require having your computer turned on to run.

[Smokie]

Use the windows task scheduler to open a browser once per day and visit your page which runs the functions.

[Rob]

I would recommend going down the SQL Agent route. Mosts hosts will allow jobs to run via the agent which perform tasks on your database.

The other option as smokie says is to have a browser navigate the link once a day.

Do these scripts take a long time to run?? If not, consider piggy backing them onto a commonly hit web page..
A mass delete using such a simple filter would not take long at all to run (especially if they get run every day) and your users will not even notice.