list MS SQL weekly job

Dear all,

Any script that allow me list out any job run per weekly, 6 months or annually ?
LVL 1
marrowyungAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
Try this:
SELECT j.name, c.Name, SUBSTRING(j.Description, 1, 100), j.enabled, s.enabled,
    s.freq_type, s.active_start_date, s.active_start_time, s.active_end_date, s.active_end_time,
    s.freq_recurrence_factor, s.freq_interval,
    s.freq_relative_interval, s.freq_subday_type, s.freq_subday_interval
FROM msdb.dbo.sysjobs j WITH (NOLOCK)
    INNER JOIN msdb.dbo.syscategories c WITH (NOLOCK) ON j.category_id = c.category_id
    LEFT OUTER JOIN msdb.dbo.sysjobschedules js WITH (NOLOCK) ON j.job_id = js.job_id
    LEFT OUTER JOIN msdb.dbo.sysschedules s WITH (NOLOCK) ON js.schedule_id = s.schedule_id

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, sql server jobs (sql agent) can do that, no "script" needed.
not available in the SQL Express version, though, in which case you use the OS scheduler + sqlcmd tool
0
 
marrowyungAuthor Commented:
noo, I mean I just want a list of job that run weekly and monthly,

how ?
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
Vikas GargConnect With a Mentor Business Intelligence DeveloperCommented:
Hi,

Using the below query you can get the complete information of the Jobs running on your SQL instance.

Hope this will help.

select *
 from  msdb..sysjobs s 
 left join master.sys.syslogins l on s.owner_sid = l.sid

Open in new window

0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
dbo.sysschedules
http://msdn.microsoft.com/en-us/library/ms178644.aspx
field: freq_type
8 = Weekly (every week)
16 = Monthly (every month)
32 = Monthly, relative to freq_interval => with 12, it becomes yearly
0
 
marrowyungAuthor Commented:
Vikas Garg,

I don't get it:

That query return no SQL job name for me.

Guy Hengel [angelIII / a3],

What is the query to show this out ?  select * from dbo.sysschedules; will gives errors.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>select * from dbo.sysschedules; will gives errors.
you need to be in the msdb database:
use msdb
go
select * from dbo.sysschedules;

Open in new window

apart from that: which error
0
 
marrowyungAuthor Commented:
but it just show the schedule name, it don't show the SQL job name, how come ?
0
All Courses

From novice to tech pro — start learning today.