SQL Server – Cleaning Disabled SQL Agent Jobs

sql serversql-server-agent

For many of our old servers, we have found that there are lot of SQL agent jobs which were either run as an ad-hoc for instant purpose and now no more needed OR Jobs which are not functional/working and replaced by some new jobs, are just sitting idle and occupying the unwanted attention on the job activity list:

Hence, I would like to know, should we be good to clean up all those disabled jobs?
(I am not sure if they are sitting idle there, what would be current or future impact)

If yes, what should be the safest way of cleaning them in one go, for cases where there is quite a big list of disabled jobs.

Best Answer

Only you can answer if the jobs can be dropped completely. If they are not jobs like from the import/export wizard I would probably script them out. This can be done easily with PowerShell.

A quick way of dropping them is via Object Explorer details pane, it allows multiple selections and then just hit the delete key or right click and delete.

Related Solutions

Sql-server – History from SQL Agent Jobs (SQL Server 2005)

You need msdb.dbo.sysjobhistory and a few JOINs of course to read the data

To change how SQL Server Agent uses this table, use this stored procedure with suitable values

EXEC msdb.dbo.sp_set_sqlagent_properties
    @jobhistory_max_rows = 1000,
    @jobhistory_max_rows_per_job = 100

SQL Server – SQL Server Agent Jobs and Availability Groups

Within your SQL Server Agent job, have some conditional logic to test for if the current instance is serving the particular role you are looking for on you availability group:

if (select
        ars.role_desc
    from sys.dm_hadr_availability_replica_states ars
    inner join sys.availability_groups ag
    on ars.group_id = ag.group_id
    where ag.name = 'YourAvailabilityGroupName'
    and ars.is_local = 1) = 'PRIMARY'
begin
    -- this server is the primary replica, do something here
end
else
begin
    -- this server is not the primary replica, (optional) do something here
end

All this does is pull the current role of the local replica, and if it's in the PRIMARY role, you can do whatever it is that your job needs to do if it is the primary replica. The ELSE block is optional, but it's to handle possible logic if your local replica isn't primary.

Of course, change 'YourAvailabilityGroupName' in the above query to your actual availability group name.

Don't confuse availability groups with failover cluster instances. Whether the instance is the primary or secondary replica for a given availability group doesn't affect server-level objects, like SQL Server Agent jobs and so on.

Best Answer

Related Solutions

Sql-server – History from SQL Agent Jobs (SQL Server 2005)

SQL Server – SQL Server Agent Jobs and Availability Groups

Related Question