Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.
COURSE of the MONTH
13 days, 23 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Maintenance SubPlan Information
Posted on 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(SubPlanNam
SELECT
SP.Subplan_name, P.name AS PlanName,
--MPL.plan_id, MPL.subplan_id,
DATEDIFF(DD,MPL.start_time
MPL.succeeded,
MPL.start_time, MPL.end_time
FROM msdb.dbo.sysmaintplan_log MPL (nolock)
INNER JOIN msdb.dbo.sysmaintplan_plan
INNER JOIN msdb.dbo.sysmaintplan_subp
INNER JOIN msdb.dbo.sysmaintplan_logd
WHERE 0=0
AND SP.subplan_name LIKE '%ReIndex%'
AND DATEDIFF(DD,MPL.start_time
--AND MPL.succeeded = 1
ORDER BY MPL.start_time DESC
SELECT * FROM @SubPlanResults
Thanks In Advance
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(SubPlanNam
SELECT
SP.Subplan_name, P.name AS PlanName,
--MPL.plan_id, MPL.subplan_id,
DATEDIFF(DD,MPL.start_time
MPL.succeeded,
MPL.start_time, MPL.end_time
FROM msdb.dbo.sysmaintplan_log MPL (nolock)
INNER JOIN msdb.dbo.sysmaintplan_plan
INNER JOIN msdb.dbo.sysmaintplan_subp
INNER JOIN msdb.dbo.sysmaintplan_logd
WHERE 0=0
AND SP.subplan_name LIKE '%ReIndex%'
AND DATEDIFF(DD,MPL.start_time
--AND MPL.succeeded = 1
ORDER BY MPL.start_time DESC
SELECT * FROM @SubPlanResults
Thanks In Advance
[X]
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
2
3 Comments
Active today
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_plan
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_plan
INNER JOIN msdb.dbo.sysmaintplan_subp
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_plan
INNER JOIN msdb.dbo.sysmaintplan_subp
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_
WHERE sp.subplan_id=N'f9f27b1c-b
AND s.id=N'a8a6cbd6-6d58-453b-
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_plan
INNER JOIN msdb.dbo.sysmaintplan_subp
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_
INNER JOIN msdb.dbo.sysmaintplan_logd
WHERE (sp.subplan_id=N'f9f27b1c-
AND(s.id=N'a8a6cbd6-6d58-4
ORDER BY [StartTime] DESC
Query 1 - display a list of maintenance plans
SELECT s.name
FROM msdb.dbo.sysmaintplan_plan
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_plan
INNER JOIN msdb.dbo.sysmaintplan_subp
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_plan
INNER JOIN msdb.dbo.sysmaintplan_subp
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_
WHERE sp.subplan_id=N'f9f27b1c-b
AND s.id=N'a8a6cbd6-6d58-453b-
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_plan
INNER JOIN msdb.dbo.sysmaintplan_subp
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_
INNER JOIN msdb.dbo.sysmaintplan_logd
WHERE (sp.subplan_id=N'f9f27b1c-
AND(s.id=N'a8a6cbd6-6d58-4
ORDER BY [StartTime] DESC
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
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
Active today
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_subplan
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_plan
INNER JOIN msdb.dbo.sysmaintplan_subp
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_
WHERE sp.subplan_id=N'f9f27b1c-b
AND s.id=N'a8a6cbd6-6d58-453b-
ORDER BY [EndTime] DESC
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_subplan
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_plan
INNER JOIN msdb.dbo.sysmaintplan_subp
INNER JOIN msdb.dbo.sysmaintplan_log AS spl ON spl.subplan_id=sp.subplan_
WHERE sp.subplan_id=N'f9f27b1c-b
AND s.id=N'a8a6cbd6-6d58-453b-
ORDER BY [EndTime] DESC
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Course of the Month13 days, 23 hours left to enroll
801 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.