MSSQLTips.com - your daily source for SQL Server tips

Google
 
Web mssqltips.com

ESSENTIALS: Home | Tips | Search | Categories | Top 10 | Products | Authors | Blogs | Forums | Webcasts | Advertise | About
Maintenance Plans Table - MSSQLTips

MSSQLTips

MSSQLTips.com - your daily source for SQL Server tips
Welcome to MSSQLTips Sign in | Join | Help
in Search

Maintenance Plans Table

Last post 07-23-2008 6:17 PM by aprato. 3 replies.
Page 1 of 1 (4 items)
Sort Posts: Previous Next
  • 07-22-2008 4:33 AM

    • Joe
    • Top 200 Contributor
    • Joined on 07-22-2008
    • Posts 2

    Maintenance Plans Table

    Hi, I’m looking for a table ore stored procedure which gives me al the information about the Maintenance Plans on a SQL Server. For example Backup Folder, witch databases are selected and so.I did find a quite a lot store Procedures and some views whit information about Maintenance Plans and Jobs, bud not the Information I was looking for.Dos anybody has a idea? Thanks Joe

     

  • 07-22-2008 11:09 AM In reply to

    • grobido
    • Top 10 Contributor
    • Joined on 10-11-2007
    • Wilton, NH
    • Posts 66

    Re: Maintenance Plans Table

    Here are a couple of queries you can run.  Hopefully this helps.

    select plan_name from msdb.dbo.sysdbmaintplans

    select plan_name, database_name, activity, start_time, duration/60 from msdb.dbo.sysdbmaintplan_history
    where start_time > '11/01/2006'

    select plan_name, database_name
    from msdb.dbo.sysdbmaintplans inner join
    msdb.dbo.sysdbmaintplan_databases on msdb.dbo.sysdbmaintplans.plan_id = msdb.dbo.sysdbmaintplan_databases.plan_id

    select distinct plan_name, activity from msdb.dbo.sysdbmaintplan_history
    where start_time > '05/08/2007'

  • 07-23-2008 6:18 AM In reply to

    • Joe
    • Top 200 Contributor
    • Joined on 07-22-2008
    • Posts 2

    Re: Maintenance Plans Table

    Thanks for your help.It doesn’t work, all I get from the select plan_name from msdb.dbo.sysdbmaintplansis "All ad-hoc plans" as the onli row!Bud I have 5 Maintenance Plans on this Server

     

  • 07-23-2008 6:17 PM In reply to

    • aprato
    • Top 10 Contributor
    • Joined on 12-01-2007
    • Greater Boston
    • Posts 262

    Re: Maintenance Plans Table

     Maybe they're stored as sub plans?


    select * from sysmaintplan_subplans

Page 1 of 1 (4 items)