Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sometimes certain SQL Server maintenance plan tasks can be problematic, like Shrink Database, Rebuild Index, Update Statistics, etc. I'd like to search for the use of one or more of these tasks on an instance of SQL server, for further review of whether they are necessary.

Is there a way to query for maintenance plans that perform one or more specific tasks?

(Not limited to the tasks that I mentioned.)

share|improve this question
add comment

1 Answer

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.

Sample ExecutableType

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;
share|improve this answer
    
Please somebody help w/ the code formatting. I tried, and failed. –  dev_etter Feb 13 at 21:15
1  
big things: (1) use the {} button or Ctrl+K, not `, to surround code blocks. (2) code indentation here understands 4 spaces, but not tabs in all scenarios. (3) after a bulleted list, you either need a <!-- language --> tag or need to indent twice (8 spaces). –  Aaron Bertrand Feb 13 at 21:35
    
2005 SSIS used tables called sysdtspackagefolders90 and sysdtspackages90. For 2012, they introduced a new deployment model known as the Project Deployment Model. Those packages won't be in the msdb and instead will reside in their own dedicated catalog, SSISDB. I have not confirmed but I believe they are stored in their deployment unit, the .ispac file, which is a glorified zip. –  billinkc Feb 13 at 22:51
    
No, this works in 2012. AFAIK, there is only one deployment method for a maintenance plan - saving the plan, which stores it in msdb, no different than the prior versions. And the project deployment model is only an option for custom SSIS packages. –  dev_etter Feb 13 at 23:52
    
In 2012, if you use the maintenance plan wizard, that is created using the legacy package deployment model. However, one could create a maintenance plan using SSDT which, by default for 2012+, is to create a project using the new project deployment model. Package deployment model goes to msdb. Project deployment model goes to SSISDB. –  billinkc Feb 14 at 15:04
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.