Solved

list MS SQL weekly job

Posted on 2014-07-29
8
519 Views
Last Modified: 2014-07-29
Dear all,

Any script that allow me list out any job run per weekly, 6 months or annually ?
0
Comment
Question by:marrowyung
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40226296
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40226299
noo, I mean I just want a list of job that run weekly and monthly,

how ?
0
 
LVL 15

Assisted Solution

by:Vikas Garg
Vikas Garg earned 50 total points
ID: 40226301
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 40226304
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40226336
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40226366
>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
 
LVL 1

Author Comment

by:marrowyung
ID: 40226484
but it just show the schedule name, it don't show the SQL job name, how come ?
0
 
LVL 26

Accepted Solution

by:
Shaun Kline earned 400 total points
ID: 40226501
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Activity Monitor detail 2 28
Requesting help with creating an SQL query with 2 tables 6 27
AD and SQL Server 2016 2 31
MS SQL query to show nearest date 6 44
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question