Post reply

Query to get type of task and its sequence in maintenance plan

  • October 23, 2018 at 2:07 am

    #366835

    Hello Experts,

    I am in process to automate the Maintenance job failure incidents in my environment. but I am facing the below challenge, Please help

    1. Not able to identify the type of task in subplan using sql query. E.g - Maintenance plan name - _SQL_Maint, Subplan Name - Subplan1, Task1 - BKP_Full (Database Full Backup), Task2 - BKP_TLog (Database Transactional Backup).

    Result should be

    Thanks in advance.

  • October 23, 2018 at 9:53 am

    #2010806

    This or this should help

  • October 24, 2018 at 1:04 am

    #2010890

    I already checked, but it didn't help.

  • October 24, 2018 at 11:35 am

    #2010979

    anujkumar.mca - Wednesday, October 24, 2018 1:04 AM

    I already checked, but it didn't help.

    If these are maintenance plans stored on SQL Server, you can query msdb.dbo.sysssispackages and if you include where package type = 6, that returns the rows that are maintenance plans. From there you need to shred the xml in the packagedata column. I don't think you will get the sequences too easily, if at all. Sequences of tasks depend on precedent constraints so that would need to be accounted for. But that's where the data is.

    Sue

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply