Como posso pesquisar no SQL Server planos de manutenção que executam tarefas específicas?

Sim. Isso pode ser feito usando uma combinação das tabelas SSIS do sistema e XQuery.

Observação: No SQL 2012, isso funcionará apenas para Planos de Manutenção criados no SSMS. Se você usou o Data Tools para implantar um pacote que realiza manutenção por meio do modelo de implantação do projeto, isso não funcionará.

• O SQL Server salva os planos de manutenção como pacotes SSIS, daí a necessidade das tabelas SSIS do sistema (sysssispackages e sysssispackagefolders).
• Os dados em nível de pacote onde encontraremos as referências às tarefas que você está procurando serão encontrados na coluna sysssispackages.packagedata.

XQueryName

Se você não estiver familiarizado com XQuery, pode começar aqui .

Encontre o(s) ExecutionType(s) da tarefa para a(s) tarefa(s)

A primeira coisa que você precisa encontrar é o ExecutionType da(s) tarefa(s) que está procurando.

1. Encontre um plano de manutenção existente com a(s) tarefa(s) em questão ou crie um novo plano de manutenção.
2. Lembre-se do(s) nome(s) dos planos de manutenção que procura.

3. Consulte msdb..sysssispackages para obter o(s) nome(s) do(s) plano(s).

   Consulta de exemplo para recuperar o XML do pacote com base no nome do plano de manutenção:

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

4. Clique nos resultados na coluna PackageDataXml que abrirá o XML bruto em outra guia de consulta.

5. Na guia XML recém-aberta, procure a string "ExecutableType". (Nada sofisticado é necessário aqui - apenas o bom e velho CTRL+F.)

   • Fazemos isso porque cada tarefa é salva como um nó DTS:Executable e cada nó DTS:Executable tem um DTS:ExecutableType. O tipo Executable é o que podemos usar em nossa consulta final para pesquisar as tarefas específicas.
   • Você encontrará alguns ExecutableTypes para nós executáveis ​​que não estão relacionados à nossa busca. Continue pesquisando até encontrar o(s) nó(s) executável relevante(s).
   • Você reconhecerá um bom quando o vir, porque o valor para DTS:ExecutableType terá um nome descritivo útil.
   • Pegue apenas a primeira parte do ExecutableType, que geralmente é a parte da string antes da vírgula.

Pesquise os planos de manutenção usando o(s) ExecutableType(s)

A consulta a seguir usa XQuery e os valores ExecutableType que foram encontrados na etapa acima para pesquisar em todos os planos de manutenção.

• Existem linhas comentadas que permitiriam a filtragem de planos associados a trabalhos ativos e/ou horários ativos.
• O crédito vai para StuartLC, que me ajudou com a parte XQuery disso.
• Só foi testado em 2012 e 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;