SQL Server Performance

listing databases against maintenance plans

Discussion in 'SQL Server 2005 General DBA Questions' started by Trev256b, Sep 12, 2011.

  1. Trev256b Member

    i have 5 maintenance plans and 700 databases scattered across these 5 database maintenance plans - how can easily list all databases to see which maintenance plans they are added to?

    I want to list all maintenance plans they are added to. I want to see if they are not included in any or if they are accidentally included in more than one.
  2. preethi Member

    I believe you refer to the maintenance plans created using wizards. They are generally created using SSIS. it si difficult to read them using programs. Since you have only 5 maintenance plans, it is easy to open them one by one and note all the databases and verify whether any database is included in more than one plan. If you want you can copy them into a table with the plan name and query against it.
    One a side note, Maintenance plans could be modified using US command lines (DOS commands), VB Script, Powershell, TSQL, SSIS etc. They include dynamic SQL, reading data from sys.databases, custom tables or even constructed (Like using dynamic SQL). If so, it is an almost impossible task to read and interpret the code. Does it worth the cause?

    Hope it helps.
  3. MichaelB Member

    You may want to look at this:

    http://www.rafael-salas.com/2010/01/ssis-loop-through-multiple-database.html

    it is an ssis package that creates a dynamic connection... then once you have the connection you can do whatever through SQL. you can also have a static connection where you insert the data you discovered. Pretty sweet if you ask me. We use this to connect to all our dev servers and set them to simple mode and do other maintance tasks that we dont want locally.
  4. preethi Member

    Hi Michael,
    Thanks for the reply.
    I believe the link you gave is about looping through the databases within an SSIS package. (In order to perform an operation)
    The question about reading SSIS package & identifying the databases involved in the existing SSIS packages.
  5. MichaelB Member

    Once connected you CAN read an SSIS package on all your servers and do it by something like I have below. but since he has several servers...I thought on place to look from is good :cool: :

    Declare SSISCursor CursorLocalFast_ForwardFor
    selectreplace(objectname,'dbo.','')as objectname from
    #FoundObject
    Open SSISCursor
    ;
    FetchNextFrom SSISCursor Into @ProcName
    ;
    While@@Fetch_Status=
    0
    Begin
    Select @SQL =
    '
    Select [Name] As [SSIS Name]

    , Replace(Replace(SubString(Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)),

    CharIndex('''
    + @ProcName +
    ''', Cast(Cast(PackageData As varbinary(Max)) As varchar(Max))) -
    100 / 2, 100), char(13) + char(10), ''''), '''

    +

    @ProcName
    +''', ''***'' + '''+ @ProcName +
    ''' + ''***'') As [SSIS XML]
    From MSDB.dbo.sysSSISPackages

    Where Cast(Cast(PackageData As varbinary(Max)) As varchar(Max)) Like ''%'' + '''
    + @ProcName +
    ''' + ''%'''
    select'Contains: '+ @ProcName as
    SearchFor
    Exec dbo.sp_executesql@SQL
    ;
    FetchNextFrom SSISCursor Into @ProcName
    ;
    End
    ;
    Close SSISCursor
    ;
    Deallocate SSISCursor
    ;
  6. satya Moderator

    Indeed any information that is helpful to the question is valid ... good going guys.. :)

Share This Page