Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Maintenance SubPlan Information

Posted on 2012-12-22
3
818 Views
Last Modified: 2012-12-22
I have a maintenance plan with 6 Subplans - Each Subplan Runs at a different time
I need to get detailed information about each plan - I need StartDate and EndDate plus other basic metadata for each Subplan- The Query I am running is giving me the same time for each SubPlan - Is there a better way of getting this info - Here is the query I am running


DECLARE @SERVERNAME nvarchar(MAX)
SET @SERVERNAME = @@SERVERNAME
--PRINT @SERVERNAME
DECLARE @SubPlanResults TABLE(SubPlanName VARCHAR(100),PlanName VARCHAR(100),DaysOld INT,
Succeeded BIT,StartTime DATETIME,EndTime DATETIME)
INSERT INTO @SubPlanResults(SubPlanName,PlanName,DaysOld,Succeeded,StartTime,EndTime)
 
SELECT  
SP.Subplan_name, P.name AS PlanName,
--MPL.plan_id, MPL.subplan_id,  
DATEDIFF(DD,MPL.start_time, GETDATE()) AS [Duration(Days)],
MPL.succeeded,
MPL.start_time, MPL.end_time
FROM msdb.dbo.sysmaintplan_log MPL (nolock)
INNER JOIN msdb.dbo.sysmaintplan_plans P (nolock) ON P.id = MPL.plan_id
INNER JOIN msdb.dbo.sysmaintplan_subplans (nolock) SP ON SP.plan_id=P.id
INNER JOIN msdb.dbo.sysmaintplan_logdetail (nolock) LD ON LD.task_detail_id = MPL.task_detail_id
WHERE 0=0
AND SP.subplan_name LIKE '%ReIndex%'
AND DATEDIFF(DD,MPL.start_time, GETDATE()) < 50
--AND MPL.succeeded = 1
ORDER BY MPL.start_time DESC


SELECT * FROM @SubPlanResults


Thanks In Advance
0
Comment
Question by:VitaminD
  • 2
3 Comments
 
LVL 42

Expert Comment

by:EugeneZ
ID: 38716150
try this example from http://www.mssqltips.com/sqlservertip/1137/supporting-tables-for-sql-server-2005-style-maintenance-plans/




Query 1 - display a list of maintenance plans

SELECT s.name  
FROM msdb.dbo.sysmaintplan_plans

Query 2 - display a list of plan and subplan IDs for maintenance plans (use the name from above query results for this step)

SELECT
ID, sp.subplan_id
FROM msdb.dbo.sysmaintplan_plans AS s
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id
WHERE s.name=N'MaintenancePlan'

Query 3 - Display history information (use the planID and subPlandID from above query results for this step)

SELECT spl.task_detail_id AS [ID],  
spl.start_time AS [StartTime],  
spl.end_time AS [EndTime],  
spl.succeeded AS [Succeeded]  
FROM msdb.dbo.sysmaintplan_plans AS s
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id  
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_id  
WHERE sp.subplan_id=N'f9f27b1c-b26e-448e-8531-899ccfa817ed'
AND s.id=N'a8a6cbd6-6d58-453b-a4e5-d6dfe187e7a5'  
ORDER BY [EndTime] DESC

Query 4 - Display detail information (use the planID and subPlandID from the query 2 results for this step)

SELECT  
ld.line1 AS [Line1],  
ld.line2 AS [Line2],  
ld.line3 AS [Line3],  
ld.line4 AS [Line4],  
ld.line5 AS [Line5],  
ld.server_name AS [ServerName],  
ld.start_time AS [StartTime],  
ld.end_time AS [EndTime],  
ld.error_number AS [ErrorNo],  
ld.error_message AS [ErrorMessage],  
ld.command AS [Command],  
ld.succeeded AS [Succeeded]  
FROM msdb.dbo.sysmaintplan_plans AS s  
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id  
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_id  
INNER JOIN msdb.dbo.sysmaintplan_logdetail AS ld ON ld.task_detail_id=spl.task_detail_id
WHERE (sp.subplan_id=N'f9f27b1c-b26e-448e-8531-899ccfa817ed')
AND(s.id=N'a8a6cbd6-6d58-453b-a4e5-d6dfe187e7a5')
ORDER BY [StartTime] DESC
0
 

Author Comment

by:VitaminD
ID: 38716424
The issue I am having is that the Maintenance plan history is not the same as the system tables and views
I cannot get the complete status for each Subplan - The system tables are returning the same startTime and EndTime for all subplans - This cannot be correct because they are all scheduled and run at different times
If anyone has had this issue before or knows how to get detailed log info for each subplan that would be helpful
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 38716443
please clarify: are you asking for history or setup info?

as per your Q it looks like setup - but as per last post - it is history:

<need to get detailed information about each plan - I need StartDate and EndDate plus other basic metadata for each Subplan->

--

if you are looking for main plan jobs:

check

the sub maint plans  jobs (if you set)  :
select
      p.name as 'Maintenance Plan'
      ,p.[description] as 'Description'
      ,p.[owner] as 'Plan Owner'
      ,sp.subplan_name as 'Subplan Name'
      ,sp.subplan_description as 'Subplan Description'
      ,j.name as 'Job Name'
      ,j.[description] as 'Job Description'  
from msdb..sysmaintplan_plans p
      inner join msdb..sysmaintplan_subplans sp
      on p.id = sp.plan_id
      inner join msdb..sysjobs j
      on sp.job_id = j.job_id
where j.[enabled] = 1

--


in any case my post has covered all aspects related to the Maint plan
check again
Supporting tables for SQL Server 2005 style maintenance plans

Query 3 - Display history information (use the planID and subPlandID from above query results for this step)
http://www.mssqltips.com/sqlservertip/1137/supporting-tables-for-sql-server-2005-style-maintenance-plans/


SELECT spl.task_detail_id AS [ID],  
spl.start_time AS [StartTime],  
spl.end_time AS [EndTime],  
spl.succeeded AS [Succeeded]  
FROM msdb.dbo.sysmaintplan_plans AS s
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp ON sp.plan_id=s.id  
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_id  
WHERE sp.subplan_id=N'f9f27b1c-b26e-448e-8531-899ccfa817ed'
AND s.id=N'a8a6cbd6-6d58-453b-a4e5-d6dfe187e7a5'  
ORDER BY [EndTime] DESC
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

839 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