March 29, 2010 at 5:03 am
i want to delete 2000 jobs from a server sorting by last run date.Is there any easy way or query to do this
March 29, 2010 at 5:48 am
Use a WHILE loop or a CURSOR on the sp_delete_job command.
The table sysjobhistory contains the columns run_date and run_time. You can use those columns to determine the last time the job ran by doing a MAX() function in a SELECT statement joined to the sysjobs table.
Inside the loop, create a dynamic sql string using the command and execute it.
You can look all this information up in Books Online for more details.
March 29, 2010 at 5:51 am
why u want to sort when u want to delete all jobs?
March 29, 2010 at 5:56 am
Brandie Tarvin (3/29/2010)
Use a WHILE loop or a CURSOR on the sp_delete_job command.The table sysjobhistory contains the columns run_date and run_time. You can use those columns to determine the last time the job ran by doing a MAX() function in a SELECT statement joined to the sysjobs table.
Inside the loop, create a dynamic sql string using the command and execute it.
You can look all this information up in Books Online for more details.
do you have that query pls?????????
March 29, 2010 at 6:07 am
No, I don't have a query. I've never needed to write one.
It's not that hard, though. Look up the code in Books Online. It's the best way for you to learn it.
March 29, 2010 at 6:11 am
I'm curious; someone went to a lot of trouble to create 2000 jobs. what is your reason for wanting to delete them now? are you replacing them with something new? Are you sure deleting those jobs is a wise decision? Do you know what those jobs are doing, so you can make an informed decision as to wether the jobs should be deleted?
why not just disable the jobs in case you need them later?
Lowell
March 29, 2010 at 6:46 am
Brandie Tarvin (3/29/2010)
No, I don't have a query. I've never needed to write one.It's not that hard, though. Look up the code in Books Online. It's the best way for you to learn it.
March 29, 2010 at 6:47 am
shanila_minnu (3/29/2010)
Brandie Tarvin (3/29/2010)
No, I don't have a query. I've never needed to write one.It's not that hard, though. Look up the code in Books Online. It's the best way for you to learn it.
DECLARE @job nvarchar(50)
DECLARE job_cursor CURSOR FAST_FORWARD FOR
select job_id
from MSDB.dbo.sysjobs
where date_modified < '2009-01-01 00:00:00.000'
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @job
WHILE @@FETCH_STATUS = 0
BEGIN
USE MSDB
EXEC sp_delete_job
@job_id = @job;
FETCH NEXT FROM job_cursor INTO @job
END
CLOSE job_cursor
DEALLOCATE job_cursor
March 29, 2010 at 6:47 am
shanila_minnu (3/29/2010)
shanila_minnu (3/29/2010)
Brandie Tarvin (3/29/2010)
No, I don't have a query. I've never needed to write one.It's not that hard, though. Look up the code in Books Online. It's the best way for you to learn it.
DECLARE @job nvarchar(50)
DECLARE job_cursor CURSOR FAST_FORWARD FOR
select job_id
from MSDB.dbo.sysjobs
where date_modified < '2009-01-01 00:00:00.000'
OPEN job_cursor
FETCH NEXT FROM job_cursor INTO @job
WHILE @@FETCH_STATUS = 0
BEGIN
USE MSDB
EXEC sp_delete_job
@job_id = @job;
FETCH NEXT FROM job_cursor INTO @job
END
CLOSE job_cursor
DEALLOCATE job_cursor
will this work?????///
March 29, 2010 at 7:28 am
One potential problem I see right off the bat. You're dropping the job off it's modified date instead of it's last run date, which is what you originally wanted. If you want run date, you'll need to do a join to a subquery of sysjobhistory that has the Max(Run_Date) chosen.
Don't use the "USE MSDB" statement in the cursor. Just "Exec msdb.dbo.sp_delete_job". Same difference and it'll be less likely to cause problems.
Other than that, test this in Dev by creating a couple of fake jobs. Then change out your where clause on the Cursor statement to "Where Jobname in ('FakeJob1','FakeJob2')" and then test the cursor. This will prevent accidental deletes of jobs you want to keep and make sure you can test this.
When you're through testing, switch the WHERE clause back and you should be golden.
March 29, 2010 at 7:53 am
Brandie Tarvin (3/29/2010)
One potential problem I see right off the bat. You're dropping the job off it's modified date instead of it's last run date, which is what you originally wanted. If you want run date, you'll need to do a join to a subquery of sysjobhistory that has the Max(Run_Date) chosen.Don't use the "USE MSDB" statement in the cursor. Just "Exec msdb.dbo.sp_delete_job". Same difference and it'll be less likely to cause problems.
Other than that, test this in Dev by creating a couple of fake jobs. Then change out your where clause on the Cursor statement to "Where Jobname in ('FakeJob1','FakeJob2')" and then test the cursor. This will prevent accidental deletes of jobs you want to keep and make sure you can test this.
When you're through testing, switch the WHERE clause back and you should be golden.
wat is the difference between date_modified and run_time.when i executed like below
SELECTb.run_time,b.run_date,a.date_modified,*
FROM
MSDB.dbo.sysjobs a inner join MSDB.dbo.sysjobhistory b
on a.job_id=b.job_id
where a.date_modified > '2006-08-17 12:22:14.440'
it is showing both run_date,date_modified almost simillar
March 29, 2010 at 7:54 am
Brandie Tarvin (3/29/2010)
One potential problem I see right off the bat. You're dropping the job off it's modified date instead of it's last run date, which is what you originally wanted. If you want run date, you'll need to do a join to a subquery of sysjobhistory that has the Max(Run_Date) chosen.Don't use the "USE MSDB" statement in the cursor. Just "Exec msdb.dbo.sp_delete_job". Same difference and it'll be less likely to cause problems.
Other than that, test this in Dev by creating a couple of fake jobs. Then change out your where clause on the Cursor statement to "Where Jobname in ('FakeJob1','FakeJob2')" and then test the cursor. This will prevent accidental deletes of jobs you want to keep and make sure you can test this.
When you're through testing, switch the WHERE clause back and you should be golden.
thanks it worked out
March 29, 2010 at 8:02 am
aww, you didn't answer any of my questions....
well, i'm expecting the followup post that says "help i deleted 2000 jobs and now i need some of them back"
did you backup the msdb database before you did this?
Lowell
March 29, 2010 at 8:08 am
shanila_minnu (3/29/2010)
wat is the difference between date_modified and run_time.when i executed like belowSELECTb.run_time,b.run_date,a.date_modified,*
FROM
MSDB.dbo.sysjobs a inner join MSDB.dbo.sysjobhistory b
on a.job_id=b.job_id
where a.date_modified > '2006-08-17 12:22:14.440'
it is showing both run_date,date_modified almost simillar
The date_modified changes if someone opens a job and then closes it by clicking the "Ok" button. There might have been an edit, there might not have been. If one of your jobs was created in 2003 and last modified in 2003, it can STILL have run yesterday. So if you use date_modified instead of run_date in your code, you could literally be deleting jobs you still use.
And I agree with the posts about "Hope you backed up MSDB" and why you need to delete all these jobs to begin with. That's an awful lot of jobs. You definitely want to practice CYA in this situation.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply