Solved

list MS SQL weekly job

Posted on 2014-07-29
8
511 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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
noo, I mean I just want a list of job that run weekly and monthly,

how ?
0
 
LVL 14

Assisted Solution

by:Vikas Garg
Vikas Garg earned 50 total points
Comment Utility
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:marrowyung
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
Comment Utility
but it just show the schedule name, it don't show the SQL job name, how come ?
0
 
LVL 25

Accepted Solution

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now