Documenting Maintenance Plans

I'm not a regular user of Maintenance Plans for SQL Server, but I run into them from time to time. I had a task to document all of the SQL Agent jobs, which for a number of environments, included some Maintenance Plans. This became a more time consuming task than I had anticipated!

I had known beforehand that Maintenance Plans were SSIS packages under the covers. So I started with a query on msdb.dbo.sysssispackages. I also knew that SSIS packages are essentially XML data. This query returns one row for each Maintenance Plan:

SELECT CAST(CAST([packagedata] AS VARBINARY(MAX)) AS XML) AS [Maintenance_Plan_Xml], 
 p.name Package_Name, p.description Package_Description, 
 p.createdate Create_Date, p.isencrypted Is_Encrypted
FROM msdb.dbo.sysssispackages p
WHERE p.packagetype = 6 --Maint Plans

If we look at the raw XML data, it's huge. It's too unwieldy to embed within this post, but you can check it out in its entirety if you like. My first thought was to try to shred the XML, but when I saw how vast and complex it was, I was less than intrepid. Still, I wanted to try. Using the SSMS GUI helped. Here's what the maintenance plan looks like:

Viewing the Maintenance Package as an SSIS package in Visual Studio also helped make sense out of the XML data. To create the SSIS package, I copied the XML output from the query into a .dtsx file. Here's what it looks like:

In both images, we can see the three subplans for the Maintenance Plan. As an SSIS package in Visual Studio, we can more easily see all of the tasks for each subplan. Visual Studio also tells us that each subplan is a Sequence Container. Lastly, I noted each subplan executes a Reporting Task upon completion, which I believe is for logging the outcome of each subplan. I decided to ignore these for my documentation purposes.

XML Shredding

Below is the query to shred the XML of each maintenance plan we saw from the first query (above). Without getting too deep into the XML query syntax, note the CROSS APPLY uses the .nodes() method to find Sequence objects (the subplans) of the Maintenance Plan SSIS package (DTS:Executable[@DTS:ExecutableType="STOCK:SEQUENCE"]).

WITH XMLNAMESPACES (
 'www.microsoft.com/SqlServer/Dts' AS DTS,
 'www.microsoft.com/SqlServer/Dts/Tasks' AS SQLTask
)
SELECT p.Package_Name,
 tasks.value('(../../@DTS:ObjectName)', 'VARCHAR(4000)') AS SubPlanName,
 tasks.value('(../../@DTS:Description)', 'VARCHAR(4000)') AS SubPlanDescription,

 tasks.value('(@DTS:ObjectName)', 'VARCHAR(4000)') AS TaskName,
 tasks.value('(@DTS:Description)', 'VARCHAR(4000)') AS TaskDescription
FROM
(
 SELECT CAST(CAST([packagedata] AS VARBINARY(MAX)) AS XML) AS [Maintenance_Plan_Xml], 
  p.name Package_Name, p.description Package_Description, 
  p.createdate Create_Date, p.isencrypted Is_Encrypted
 FROM msdb.dbo.sysssispackages p
 WHERE p.packagetype = 6 --Maint Plans
) p
CROSS APPLY p.Maintenance_Plan_Xml.nodes('/DTS:Executable/DTS:Executables/DTS:Executable[@DTS:ExecutableType="STOCK:SEQUENCE"]/DTS:Executables/DTS:Executable') AS t(tasks) 

The output is a tabular result showing the Maintenance Plan, its subplan Names and Descriptions, and the task Names and Descriptions of each subplan.

As developers, we have full control over all of the meta data. The subplan names and descriptions can be entered directly from SSMS: double-click a subplan to open its Properties window. Here you can change the subplan Name and Description (among others).

To change the properties of a subplan task, right-click the task and choose Properties from the pop-up menu. From there, the task Name and Description properties can be set (plus many, many others).

SSIS Package Format

The XML format can vary, depending on the version of SQL Server (I'm not certain, but I think it depends directly on the version of SQL Server where the system table msdb.dbo.sysssispackages resides). The example I used is from SQL Server 2017, and the package format version is 8, as noted in this part of the XML data:

<DTS:Property DTS:Name="PackageFormatVersion">8</DTS:Property>

The main query above seems to work for PackageFormatVersion 6 (SQL Server 2012) as well as for PackageFormatVersion 8 (SQL 2014 and above). For PackageFormatVersion 3 (SQL Server 2008 R2), the query needs to be slightly modified to reflect the different XML data structure:

WITH XMLNAMESPACES (
 'www.microsoft.com/SqlServer/Dts' AS DTS,
 'www.microsoft.com/SqlServer/Dts/Tasks' AS SQLTask
)
SELECT p.Package_Name,
 tasks.value('(../DTS:Property[@DTS:Name="ObjectName"][1])', 'VARCHAR(4000)') AS SubPlanName,
 tasks.value('(../DTS:Property[@DTS:Name="Description"][1])', 'VARCHAR(4000)') AS SubPlanDescription,

 tasks.value('(DTS:Property[@DTS:Name="ObjectName"][1])', 'VARCHAR(4000)')  AS TaskName,
 tasks.value('(DTS:Property[@DTS:Name="Description"][1])', 'VARCHAR(4000)')  AS TaskDescription
FROM
(
 SELECT CAST(CAST([packagedata] AS VARBINARY(MAX)) AS XML) AS [Maintenance_Plan_Xml], 
  p.name Package_Name, p.description Package_Description, 
  p.createdate Create_Date, p.isencrypted Is_Encrypted
 FROM msdb.dbo.sysssispackages p
 WHERE p.packagetype = 6
) p
CROSS APPLY p.Maintenance_Plan_Xml.nodes('/DTS:Executable/DTS:Executable[@DTS:ExecutableType="STOCK:SEQUENCE"]/DTS:Executable') AS t(tasks) 

Maintenance Plans might make some of us wince, but if you encounter them, now they should be easy to document. Happy shredding!