CODEWITHSUNDEEP
sql-serversql-server-2008
Danny
Danny

Reputation: 359

Delete unused/redundant SQL Agent schedules?

Is it possible to delete unused SQL Agent schedules from SQL Server 2008?

If so, how? I'm sure I'm missing something REALLY obvious!

Upvotes: 1

Views: 5431

Answers (1)

Stuart Moore
Stuart Moore

Reputation: 191

If you know the schedules that are redundant then it's just calling sp_delete_schedule - http://msdn.microsoft.com/en-us/library/ms175050(v=sql.100).aspx - to remove

If you need to get a list of schedules not currently enabled, or attached to a job you'll need to go through the sysschedules and sysjobschedules tables in msdb

Disabled jobs are fairly simple to find:

select a.schedule_id, a.name, b.name
from msdb..sysschedules a
left join master..syslogins b on a.owner_sid=b.sid
where enabled=0

To find those that are enabled, but not attached to a job:

select a.schedule_id, a.name, a.owner_sid, b.name
from 
msdb..sysschedules a 
left join master..syslogins b on a.owner_sid=b.sid
where a.schedule_id not in
(select schedule_id from msdb..sysjobschedules)

I include the owner name, so I can check for system schedules (owned by sa), as I can't see any other way to identify them (if anyone else can, I'd love to know).

Upvotes: 4

Related Questions