Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to query SQL 2005 Maintenance Job Information

Posted on 2009-02-16
5
Medium Priority
?
1,349 Views
Last Modified: 2012-05-06
I am trying to figure out how to query the Maintenance Plan Jobs and their associated detail (sub plans, schedule detail, etc.).  I read a previous article called "SQL query to show all databases in a Maint Plan" however the entire script is for SQL 2000.  
I know I can get the list of the plan names from:
SELECT * FROM sysmaintplan_plans  (this is a view)

I can get schedule info from:
SELECT * FROM [MSDB].[dbo].[sysschedules]

And there are other tables such as "msdb.dbo.sysmaintplan_subplans"

So what I want is to be able to query or script so I can obtain a list of all the maintenance jobs on a SQL server with the schedule details for each.  
0
Comment
Question by:66chawger
  • 3
5 Comments
 
LVL 13

Expert Comment

by:St3veMax
ID: 23653066
I think you may struggle; as if you have created a Maintenance Plan; then you would have used SSIS to create a package; the guts of it would be stored in the package. I'm not aware of a way to see what's what without using SSIS to open the package.

If you had dont this with T-SQL; then you could have looked at the system tables in MSDB.

I belive you're looking at in this instance are:

SysJobs
SysJobHistory
SysJobSchedules

HTH
0
 

Author Comment

by:66chawger
ID: 23654857
Yes, it is a mixture of SQL, Quest LiteSpeed, etc.  Definitely not easy to get at.  I might be able to get at what I need in one of the tables.  I don't need to granular, but do need at least the name of the job/plan and the schedule info...
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 23654961
may be this is useful for you.
SELECT a.name AS job_name,
       a.enabled,
       a.description,
       a.date_created,
       a.date_modified,
       b.last_outcome_message,
       b.last_run_date,
       b.last_run_time,
       b.last_run_duration,
       convert(nvarchar(10),(b.last_run_duration/10000)) + ':' + 
       convert(nvarchar(10),((b.last_run_duration%10000)/100)) + ':' + 
       convert(nvarchar(10),(((b.last_run_duration%10000)%100)%100)) as duration,
       s.next_run_date,
       s.next_run_time,
       v.name,
       v.freq_type,
       v.freq_subday_type,
       v.freq_interval
  FROM MSDB.dbo.sysjobs AS a
  JOIN MSDB.dbo.sysjobservers AS b ON a.job_id = b.job_id
  JOIN MSDB.dbo.syscategories AS c ON a.category_id = c.category_id
  JOIN MSDB.dbo.sysjobschedules AS S ON A.job_id = s.job_id
  JOIN MSDB.dbo.sysschedules_localserver_view AS v ON s.schedule_id = v.schedule_id
 WHERE (c.name <> 'Report Server' ) 
 ORDER BY job_name 

Open in new window

0
 

Author Comment

by:66chawger
ID: 23797209
Hey thanks Sharath!  That is the closest I have seen to what I need.  I can script it out more to interpret the values (freq type, freq_subdat_type..etc)...
0
 

Author Closing Comment

by:66chawger
ID: 31547424
The solutions was as complete as you can get.  This will give me what I need  and I can parse out the jobs that are not what I need.  Thanks!!!!
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

580 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