Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    6

    Unanswered: Maintenance Plan on 2005

    Hi all,

    I need to find out on runtime, a list of Maintenance plans and Jobs.
    i dont know how to acquire the database name on which the plan/job are operating.
    i also dont know from which table can i get the tasks ( backup,integrity,rebuild index etc.) that belongs to the plan.

    its quite urgent,

    10x,

    Gil.

  2. #2
    Join Date
    Jan 2006
    Posts
    6
    Quote Originally Posted by gilp
    Hi all,

    I need to find out on runtime, a list of Maintenance plans and Jobs.
    i dont know how to acquire the database name on which the plan/job are operating.
    i also dont know from which table can i get the tasks ( backup,integrity,rebuild index etc.) that belongs to the plan.

    its quite urgent,

    10x,

    Gil.
    wasnt sure the question was clear so -

    where dows SQLSERVER 2005 keeps the database name and tasks concerning maintanace Plans/Jobs.
    for example :
    I have a Maintanace Plan name TEST1 and a Job with the same name that works on TEST DB and does a Backup task. i want to be able to make a query that will bring back :


    TEST1, TEST1, TEST, Backup.





    thanks alot,

    gil.

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    The tables you need to look at are in the msdb database. there are at least 14 by my count. Follow this link to get to a download page for the map:
    http://www.microsoft.com/sql/prodinf...systables.mspx

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Jan 2006
    Posts
    6
    Quote Originally Posted by tomh53
    The tables you need to look at are in the msdb database. there are at least 14 by my count. Follow this link to get to a download page for the map:
    http://www.microsoft.com/sql/prodinf...systables.mspx
    thats the previous version system tables documantation.
    i need it for 2005.
    thanks anyway,
    gil.

  5. #5
    Join Date
    Oct 2003
    Posts
    10

    This might help..

    When profiling the SQL Server Management Studio during the expansion of the Maintenance Plans node, it queries the msdb.dbo.sysmaintplan_plans table.

    SELECT *
    FROM
    msdb.dbo.sysmaintplan_plans AS s
    ORDER BY
    [Name] ASC

    There are a few tables in the msdb database that deal with maintenance plans.

    sysdbmaintplan_databases
    sysdbmaintplan_history
    sysdbmaintplan_jobs
    sysdbmaintplans

    I hope this helps.

  6. #6
    Join Date
    Jan 2006
    Posts
    6
    Quote Originally Posted by ScottW
    When profiling the SQL Server Management Studio during the expansion of the Maintenance Plans node, it queries the msdb.dbo.sysmaintplan_plans table.

    SELECT *
    FROM
    msdb.dbo.sysmaintplan_plans AS s
    ORDER BY
    [Name] ASC

    There are a few tables in the msdb database that deal with maintenance plans.

    sysdbmaintplan_databases
    sysdbmaintplan_history
    sysdbmaintplan_jobs
    sysdbmaintplans

    I hope this helps.
    all the sysdbmaint$$$ table are from the previous version.
    i'm looking for the new ones...
    thanks anyway,
    gil.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •