delete files older than 12 months

[kizziebutler]

I am running a schedulded job to delete files older than 12 months.

Would creating a stored procedure be the best option and schedule this job. Please could I have some advice on how to recognise files from getdate() to 12 months.

 

[vongrunt]

> Would creating a stored procedure be the best option and schedule this job.

Probably not. That would require usage of sp_OA* procedures (too clunky) or xp_cmdshell (e.g. running .bat or .cmd file).

With some scripting (a la VBS/Activex) or better command shell (a la 4NT) this is trivial to do.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[kizziebutler]

I am going to create a stored procedure and scheduled in sqlagent.

 

[k108]

Hi,

We have stored procedures that we run from our jobs, no problem. It's very simple.

You just create the stored procedure as normal. Then, you set up a job from EM to call the stored procedure:

EXEC myjob (this would go in the job itself)

The sql would look something like:

DELETE FROM mytable WHERE mydate < GETDATE() - 365

or something like that.

Setting up jobs with EM is a no-brainer. You just go to your specific database > Management > SQL Server Agent > Jobs, then right-click > New job

It's pretty self-explanatory...

 

[jbenson001]

k108,
good answer, but this person wants to delete Files not rows in a table. As vongrunt said VBS/Active script a much better choice

 

[k108]

Oh, missed that. Delete files... but I think using xp_cmdshell from a stored proc might work nicely.

good luck at any rate.

 

[jbenson001]

I agree.. should work fine...