SQL Server – How to Search for Maintenance Plans Executing Specific Tasks

Yes. This can be done by using a combination of the system SSIS tables and XQuery.

Note: In SQL 2012, this will only work for Maintenance Plans created within SSMS. If you used Data Tools to deploy a package that performs maintenance via the project deployment model, this will not work.

• SQL Server saves maintenance plans as SSIS packages, hence the need for the system SSIS tables (sysssispackages and sysssispackagefolders).
• The package-level data where we'll find the references to the tasks you're searching for will be found in the column sysssispackages.packagedata.

XQuery

If you are not familiar with XQuery, you can start here.

Find the task ExecutionType(s) for the Task(s)

The first thing that you'll need to find is the ExecutionType of the task(s) you're looking for.

1. Find an existing maintenance plan with the task(s) in question or build a new maintenance plan.
2. Remember the name(s) of the maintenance plans you're looking for.

3. Query msdb..sysssispackages for the plan name(s).

   Example query to retrieve the package XML based on the maintenance plan name:

    SELECT 
       name PlanName   
       ,createdate PlanCreated
       ,CAST(CAST(pack.[packagedata] AS varbinary(max)) AS XML) PackageDataXml 
    FROM msdb.dbo.sysssispackages pack
    WHERE name = 'RebuildIndexes'
   

4. Click on the results in the PackageDataXml column which will open the raw XML in another query tab.

5. In the newly opened XML tab, search for the string "ExecutableType". (Nothing fancy required here - just good old-fashioned CTRL+F.)

   • We do this because each task is saved as a DTS:Executable node, and each DTS:Executable node has a DTS:ExecutableType. The Executable type is what we can use in our final query to search for the specific tasks.
   • You'll find some ExecutableTypes for Executable nodes that don't relate to our quest. Keep searching until you find the relevant Executable node(s).
   • You'll know a good one when you see it, because the value for DTS:ExecutableType will have a useful descriptive name.
   • Only grab the first portion of the ExecutableType, which is usually the part of the string before the comma.

Search for the maintenance plans using the ExecutableType(s)

The following query uses XQuery and the ExecutableType values that were found in the step above to search in all maintenance plans.

• There are commented out lines that would allow for filtering for plans associated with active jobs and/or active schedules.
• Credit goes to StuartLC who helped me with the XQuery portion of this.
• Has only been tested on 2012 and 2008R2.

/*
  Description: Search SSISPackages w/ xQuery for specific 
  Maintenance Plan tasks. In this example: 

    --> Index Rebuild
    --> Shrink Database
    --> Update Statistics

Known compatible versions: 2008R2 & 2012

    --> No 2008 or 2005 instances to try it out on. :'(
*/

-- Build a temp table to retrieve the list of plans
-- that use the tasks we're searching for.

IF OBJECT_ID('tempdb..#BadPlans') IS NOT NULL 
  DROP TABLE #BadPlans;

CREATE TABLE #BadPlans 
(
  PlanName nvarchar(128)
  ,PlanCreated datetime
  ,PlanFolder nvarchar(128)
  ,TaskDescription varchar(50)
  ,TaskExecutableType varchar(200)
);

;WITH XMLNAMESPACES 
(
  'www.microsoft.com/SqlServer/Dts' AS DTS
  ,'www.microsoft.com/SqlServer/Dts/Tasks' AS SQLTask
)
INSERT INTO #BadPlans

--******2012 Results******

SELECT 
  PlanName
  ,PlanCreated
  ,PlanFolder
  ,Nodes.node.value('(@DTS:Description)[1]', 'varchar(50)') 
    AS TaskDescription
  ,Nodes.node.value('(@DTS:ExecutableType)[1]', 'varchar(200)') 
    AS TaskExecutableType
FROM 
(
    -- Grab the plan properties and the plan XML so that we can
    -- use CROSS APPLY for the DTS:Executable node.

  SELECT
    pack.name PlanName
    ,pack.createdate PlanCreated
    ,folder.foldername PlanFolder
    ,CAST(CAST(pack.[packagedata] as varbinary(max)) as xml) PackageDataXml
  FROM [msdb].[dbo].[sysmaintplan_subplans] sub
  JOIN msdb.dbo.sysssispackages pack on sub.plan_id = pack.id
  JOIN msdb.dbo.sysssispackagefolders folder on pack.folderid = folder.folderid 
  --JOIN msdb.dbo.sysjobs jobs on sub.job_id = jobs.job_id            
     -- optional, only needed if filtering for active jobs
  --JOIN msdb.dbo.sysjobschedules jsched on jobs.job_id = jsched.job_id        
     -- optional, only needed if filtering for active schedules
  --JOIN msdb.dbo.sysschedules sched on jsched.schedule_id = sched.schedule_id    
     -- optional, only needed if filtering for active schedules
  WHERE pack.packagetype = 6  -- required, indicates a user-created maintenance plan
    --AND jobs.enabled = 1    -- optional, will filter for plans associated with active jobs
    --AND sched.enabled = 1   -- optional, will filter for jobs with active schedules
) AS SysPackages
CROSS APPLY 
-- Version-specific XPath for 2012. Look at this singular-plural-singular-plural-singular madness.
  SysPackages.PackageDataXml.nodes('/DTS:Executable/DTS:Executables/DTS:Executable/DTS:Executables/DTS:Executable') 
  Nodes(Node)
WHERE
-- Each string that follows LIKE is the ExecutableType you found in the raw XML
-- in sysssispackages.packagedata.
  Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask%'

UNION ALL

--******2008R2 Results******

SELECT
  PlanName
  ,PlanCreated
  ,PlanFolder
  ,Nodes.node.value('(DTS:Property[@DTS:Name="ObjectName"])[1]', 'varchar(50)') 
    AS TaskDescription
  ,Nodes.node.value('(@DTS:ExecutableType)[1]', 'varchar(200)') 
    AS TaskExecutableType
FROM 
(
    -- Grab the plan properties and the plan XML so that we can
    -- use CROSS APPLY for the DTS:Executable node.

  SELECT
    pack.name PlanName
    ,pack.createdate PlanCreated
    ,folder.foldername PlanFolder
    ,CAST(CAST(pack.[packagedata] as varbinary(max)) as xml) PackageDataXml
  FROM [msdb].[dbo].[sysmaintplan_subplans] sub
  JOIN msdb.dbo.sysssispackages pack on sub.plan_id = pack.id
  JOIN msdb.dbo.sysssispackagefolders folder on pack.folderid = folder.folderid 
  --JOIN msdb.dbo.sysjobs jobs on sub.job_id = jobs.job_id            
     -- optional, only needed if filtering for active jobs
  --JOIN msdb.dbo.sysjobschedules jsched on jobs.job_id = jsched.job_id        
     -- optional, only needed if filtering for active schedules
  --JOIN msdb.dbo.sysschedules sched on jsched.schedule_id = sched.schedule_id    
     -- optional, only needed if filtering for active schedules
  WHERE pack.packagetype = 6  -- required, indicates a user-created maintenance plan
    --AND jobs.enabled = 1    -- optional, will filter for plans associated with active jobs
    --AND sched.enabled = 1   -- optional, will filter for jobs with active schedules
) AS SysPackages
CROSS APPLY 
-- Version-specific XPath for 2008R2. Not as much madness here.
  SysPackages.PackageDataXml.nodes('/DTS:Executable/DTS:Executable/DTS:Executable') 
  Nodes(Node)
WHERE
-- Each string that follows LIKE is the ExecutableType you found in the raw XML
-- in sysssispackages.packagedata.
  Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceReindexTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceShrinkTask%'
  OR Nodes.node.value('@DTS:ExecutableType', 'varchar(200)') 
    LIKE 'Microsoft.SqlServer.Management.DatabaseMaintenance.DbMaintenanceUpdateStatisticsTask%';

SELECT * FROM #BadPlans;