September 17, 2020 at 10:40 am
I need all jobs, job steps and schedule for a particular database
September 17, 2020 at 1:44 pm
check msdb dbo.sysjobs dbo.sysjobsteps dbo.sysjobschedules dbo.sysschedules
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 17, 2020 at 1:49 pm
Can you please help me with the script. The script should take database name as an input parameter and should output all its related jobs, steps and schedules.
September 17, 2020 at 2:03 pm
Keep in mind you will need to do some extra validation if to see of jobs address database objects using 4-part naming.
So, the query you'll end up with using the above tables, may not cover all your needs.
Probably it will do, but you have to check
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 17, 2020 at 2:43 pm
Are all of the job steps running pure T-SQL?
Or are there other task types (eg, execution of SSIS packages)? If so, this task could be deceptively difficult.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 17, 2020 at 3:33 pm
Can you please help me with the script. The script should take database name as an input parameter and should output all its related jobs, steps and schedules.
If you check msdb.dbo.sysjobsteps, you'll find a column called "database_name". That column contains the name of the database where the "command" that the job step will run in.
It also contains the "job_id" column, which can be used to join back to the msdb.dbo.sysjobs table to get the name of the job and the msdb.dbo.sysjobschedules table to get the schedules.
Do understand that it won't find all that you may need. For example, if you have a job that calls a stored procedure in the database listed in the msdb.cbo.sysjobsteps table, that stored procedure may have synonyms and "pass-through views" that get information from other databases and may also contain things like "OpenRowSet" and "OpenQuery" that refer to other databases on other systems through linked servers.
I've never had the need to determine such a thing as you've requested and so don't have a script for such a thing or I'd happily cough it up for you. I can't imagine that it would be difficult for you to write, though, now that you know where to start looking. All of the tables in msdb that I've mentioned are fully documented in BOL and in Microsoft Docs including how to interpret the values for the scheduling in msdb.dbo.sysjobschedules.
Give it a try... you'll learn a lot in the process and that's a good thing because you're also the one that would need to explain and support such code.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2020 at 3:36 pm
p.s. I also agree with Phil... if you have SSIS, SSAS, and other SQL "4 letter words" involved in jobs, this could end up being a fair bit more difficult than most might imagine. But, give it a try based on the tables that I've suggested. At least it's a start.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2020 at 8:06 am
This script gets me all jobs and schedules that have "%backup%" in their steps command.
Just modify to the wanted database name and off you go.
select ServerName
, JobName
, last_run_date
, last_run_time
, Sum_last_run_duration
, freq_type
, frequence
, Units_freq_subday_interval
, freq_subday_interval
, freq_relative_interval
, freq_recurrence_factor
, right(cast(1000000 + active_start_time as char(7)), 6) as active_start_time
from (
select SRV.name as ServerName
, J.name as JobName --, J.description
, min(RIGHT(JS.last_run_date + 1000000000000000000, 8)) as last_run_date
, min(RIGHT(JS.last_run_time + 1000000000000000000, 6)) as last_run_time
, sum(JS.last_run_duration) as Sum_last_run_duration
, min(case S.freq_type
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Weekly'
when 16 then 'Monthly'
when 32 then 'Monthly*'
when 64 then 'At SqlServer Start'
else '??????'
end) as freq_type
, min(case S.freq_type
when 1 then convert(varchar(20), S.freq_interval)
when 4 then convert(varchar(20), S.freq_interval)
when 8 then case when convert(binary(2), S.freq_interval) & 1 = 1 then 'Su-'
else '..-'
end + case when convert(binary(2), S.freq_interval) & 2 = 2 then 'Mo-'
else '..-'
end + case when convert(binary(2), S.freq_interval) & 4 = 4 then 'Tu-'
else '..-'
end + case when convert(binary(2), S.freq_interval) & 8 = 8 then 'We-'
else '..-'
end + case when convert(binary(2), S.freq_interval) & 16 = 16 then 'Th-'
else '..-'
end + case when convert(binary(2), S.freq_interval) & 32 = 32 then 'Fr-'
else '..-'
end + case when convert(binary(2), S.freq_interval) & 64 = 64 then 'Sa'
else '..'
end
when 16 then convert(varchar(20), S.freq_interval)
when 32 then case when convert(binary(2), S.freq_interval) & 1 = 1 then 'Su-'
else '..-'
end + case when convert(binary(2), S.freq_interval) & 2 = 2 then 'Mo-'
else '..-'
end + case when convert(binary(2), S.freq_interval) & 3 = 3 then 'Tu-'
else '..-'
end + case when convert(binary(2), S.freq_interval) & 4 = 4 then 'We-'
else '..-'
end + case when convert(binary(2), S.freq_interval) & 5 = 5 then 'Th-'
else '..-'
end + case when convert(binary(2), S.freq_interval) & 6 = 6 then 'Fr-'
else '..-'
end + case when convert(binary(2), S.freq_interval) & 7 = 7 then 'Sa'
else '..'
end + case when convert(binary(2), S.freq_interval) & 8 = 8 then 'Day-'
else '...'
end + case when convert(binary(2), S.freq_interval) & 9 = 9 then 'Weekday-'
else '..'
end + case when convert(binary(2), S.freq_interval) & 10 = 10 then 'Weekend'
else '..'
end
when 64 then convert(varchar(20), S.freq_interval) + ' ???'
else '??????'
end) as frequence
, min(case S.freq_subday_type
when 1 then 'At the specified time'
when 2 then 'Seconds'
when 4 then 'Minutes'
when 8 then 'Hours'
else '???'
end) as Units_freq_subday_interval
, min(S.freq_subday_interval) as freq_subday_interval
, min(case S.freq_type
when 32 then case freq_relative_interval --int Scheduled job's occurrence of the freq_interval in each month when freq_type is 32 (monthly relative):
when 1 then 'First'
when 2 then 'Second'
when 4 then 'Third'
when 8 then 'Fourth'
when 16 then 'Last'
else '???'
end
else '-NA-'
end) as freq_relative_interval
, min(S.freq_recurrence_factor) as freq_recurrence_factor
, min(S.active_start_time) as active_start_time
from msdb.dbo.sysjobs J
inner join msdb.dbo.sysjobsteps JS
on J.job_id = JS.job_id
and JS.command like '%backup%'
and J.enabled = 1
inner join msdb.dbo.sysjobschedules JS1
on J.job_id = JS1.job_id
inner join msdb.dbo.sysschedules S
on JS1.schedule_id = S.schedule_id
and S.enabled = 1
inner join master.sys.servers SRV
on SRV.server_id = J.originating_server_id
group by SRV.name
, J.name --, J.description
) GroupSel
order by JobName
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 18, 2020 at 12:40 pm
Johan,
If I quote your post, I can see your script in it but it's not doing a normal show on your post.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 18, 2020 at 1:40 pm
Hi Jeff,
That is strange.
I hoped the 'insert/edit code sample' would nutralise all dodgy behaviour of ampersants, etc
-- please see attachment
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply