List jobs using a history retention setting other than the default

Language: SQL

View on GitHub to download or comment.

See the database model page for more information on querying the database.

This query lists all jobs that are configured to use a history retention setting other than the instance default setting, showing what the setting is for each job. The default setting is configured in Configuration>General Server Options.

sample.sql 
select j.oid,g.name as 'Group',j.name as 'Job' ,j.historyRetentionLimit as 'Limit',
Setting = case j.historyretentionoptions
	when 0 then 'Default'
	when 1 then 'No Retention'
	when 2 then 'Days'
	when 3 then 'Executions'
	when 4 then 'Forever'
end 
from job j join jobGroup g on j.jobGroupOID=g.oid
where j.historyRetentionOptions<>0
order by g.name,j.name

Comments

View on GitHub to comment.