Deletion of Old Jobs

Question

Deletion of Old Jobs

lykins

SSC Veteran

Points: 210
More actions
June 4, 2004 at 2:20 pm

#83009

How do I automatically delete old Jobs. Our payroll software creates a job on our sever everytime calculations are made or checks are printed. This wouldn't be bad if the jobs were deleted after a certain period of time, but when we average around 750 jobs per

Patrick L. Lykins

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

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

Edward McGovern Ten Centuries Points: 1299 More actions · Answer 1

Ick, thats not too smart a way to do a SQL Process. It only runs once but creates a job to do it? Thats strange. You sure its not rerunning the jobs?

You need to be able to identify the jobs, you can't just delete all old jobs because what about the maintenance plan? What about the job to delete jobs? You need an identifier. Look at the msdb.dbo.sysjobs table and determine a way to identify jobs made by this program. Then modify my script to filter the jobs table for only that identifier.

Anyway the script for the procedure would be:

Declare @Job_ID as uniqueidentifier

select job_ID Into ##Temp from msdb.dbo.sysjobs where DateDiff(day, date_created, getdate()) > 14

While Exists(Select Job_ID from ##Temp)

begin

Select @Job_ID = max(Job_ID) from ##Temp

sp_delete_job @Job_ID = @Job_ID

Delete from ##Temp where Job_ID = @Job_ID

end

drop Table ##Temp

Edward McGovern Ten Centuries Points: 1299 More actions · Answer 2

I did it for 14 days, but you should change to what makes sense. Don't forget to find an identifier, that script will delete all jobs after 14 days!

lykins SSC Veteran Points: 210 More actions · Answer 3

Thanks, I will try to get this to work either this evening or Monday morning.

This is just one of about two hundred reasons I believe the company was forced to sell to another company. The schema for the system was over 16,000 pages long when I attempted to print it. Instead of fixing issues they just patched the code and kept patching. We are looking to move to the new version that was re-wrote by the new company but until then, I have to create my own custom patches to fix their garbage. It's sad that they were somewhere in the top three payroll packages when they sold.

Thanks for the code.

Patrick L. Lykins

Edward McGovern Ten Centuries Points: 1299 More actions · Answer 4

Holy Cow, 16,000 pages. Yeah I'd leave it as is too.