January 2, 2019 at 4:29 pm
Hi All,
Someone suggested me to create a new post for this issue and here I am 🙂
The problem is how to track the changes which are being made to maintenance plans?
Few of my instance jobs fail due to the databases where changed in maintenance plan.
I know sysjobs has 'is_modified' column but to my surprise there was no entry there for any recent change.
Is it possible to alter sysjobs table as well? what are other way to audit changes to maintenance plan.
Thank you!
January 2, 2019 at 7:47 pm
sizal0234 - Wednesday, January 2, 2019 4:29 PMHi All,Someone suggested me to create a new post for this issue and here I am 🙂
The problem is how to track the changes which are being made to maintenance plans?
Few of my instance jobs fail due to the databases where changed in maintenance plan.
I know sysjobs has 'is_modified' column but to my surprise there was no entry there for any recent change.
Is it possible to alter sysjobs table as well? what are other way to audit changes to maintenance plan.Thank you!
I read that post on the other thread - there was a modified date for when you fixed the job but that is all it's going to show you, the date/time the job was last changed. There isn't a history of changes. If you want history and more information, you need to set something up using sq audit or extended events. There are examples of both in this post:
SQL Server Agent logging actions and changes done to jobs
Sue
January 2, 2019 at 7:53 pm
Thank you! will create audits , I would not prefer extended events as it might cause little burden on sql server. Let me know if that is not the case?
January 2, 2019 at 8:00 pm
sizal0234 - Wednesday, January 2, 2019 7:53 PMThank you! will create audits , I would not prefer extended events as it might cause little burden on sql server. Let me know if that is not the case?
It's not the case. You'd be fine with extended events. Test them both out and see which one works best for you. And in the process you'll know about setting up both as well.
Sue
January 2, 2019 at 8:07 pm
will do. thanks again
January 3, 2019 at 5:10 am
In terms of overall impact to the server, I'd pick extended events over audit, every day. Audit is much more likely to have a negative impact on the instance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2019 at 8:04 am
I have created a job with the following code to track whats going (run every minute). above options are good however you have to check em daily/hourly/ whenever, I found it easy when I get email notification.
begin
-- create table for fist run
IF object_id('msdb..my_sysjobs') is null
begin
select * into msdb..my_sysjobs from msdb..sysjobs
end
if exists (select 'New Job Added --> ' action,* from msdb..sysjobs where job_id not in (select job_id from msdb..my_sysjobs))
begin
EXEC msdb..sp_send_dbmail
@profile_name = 'mail_profile',
@recipients = 'someone@somewhere.com',
@subject = 'Job Added ',
@body = 'Following Jobs were added since last run',
@execute_query_database = 'msdb',
@query = '
select ''New Job Added --> '' action,* from msdb..sysjobs where job_id not in (select job_id from msdb..my_sysjobs)'
end
if exists (select 'Job Deleted --> ' action,* from msdb..my_sysjobs where job_id not in (select job_id from msdb..sysjobs))
begin
EXEC msdb..sp_send_dbmail
@profile_name = 'mail_profile',
@recipients = 'someone@somewhere.com',
@subject = 'Job Deleted ',
@body = 'Following Jobs were deleted since last run',
@execute_query_database = 'msdb',
@query = 'select ''Job Deleted --> '' action,* from msdb..my_sysjobs where job_id not in (select job_id from msdb..sysjobs)'
end
if exists (select 'Modfied Job --> ' action,* from msdb..sysjobs sj
join msdb..my_sysjobs msj on sj.job_id=msj.job_id
where sj.date_modified != msj.date_modified and sj.enabled = msj.enabled)
begin
EXEC msdb..sp_send_dbmail
@profile_name = 'mail_profile',
@recipients = 'someone@somewhere.com',
@subject = 'Job Modified ',
@body = 'Following Jobs were modfied since last run',
@execute_query_database = 'msdb',
@query = 'select ''Modfied Job --> '' action,* from msdb..sysjobs sj
join msdb..my_sysjobs msj on sj.job_id=msj.job_id
where sj.date_modified != msj.date_modified
and sj.enabled = msj.enabled'
end
if exists (select 'Enabled Job --> ' action,* from msdb..sysjobs sj
join msdb..my_sysjobs msj on sj.job_id=msj.job_id
where sj.enabled != msj.enabled and sj.enabled=1)
begin
EXEC msdb..sp_send_dbmail
@profile_name = 'mail_profile',
@recipients = 'someone@somewhere.com',
@subject = 'Job Enabled ',
@body = 'Following Jobs were enabled since last run',
@execute_query_database = 'msdb',
@query = 'select ''Enabled Job --> '' action,* from msdb..sysjobs sj
join msdb..my_sysjobs msj on sj.job_id=msj.job_id
where sj.enabled != msj.enabled and sj.enabled=1'
end
if exists (select 'Disabled Job --> ' action,* from msdb..sysjobs sj
join msdb..my_sysjobs msj on sj.job_id=msj.job_id
where sj.enabled != msj.enabled and sj.enabled=0)
begin
EXEC msdb..sp_send_dbmail
@profile_name = 'mail_profile',
@recipients = 'someone@somewhere.com',
@subject = 'Job Disabled ',
@body = 'Following Jobs were disabled since last run',
@execute_query_database = 'msdb',
@query = 'select ''Enabled Job --> '' action,* from msdb..sysjobs sj
join msdb..my_sysjobs msj on sj.job_id=msj.job_id
where sj.enabled != msj.enabled and sj.enabled=0'
end
-- since notification has bern send out, get rid of data as it is no longer valid
drop table msdb..my_sysjobs
-- must creat table for next run
IF object_id('msdb..my_sysjobs') is null
begin
select * into msdb..my_sysjobs from msdb..sysjobs
end
end
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply