Maintenance plans 1

[Nowell]

Hi,

I have a few maintenance plans and I have no idea which database they work on, how can I find out?

If I look at the jobs' steps all I can see is this:

EXECUTE master.dbo.xp_sqlmaint N'-PlanID BE090B40-9519-46C6-81A2-2B43A3568EDA -Rpt "d:\MSSQL\LOG\DB Maintenance Plan12.txt" -DelTxtRpt 1WEEKS -CkDB '

 

[RonPro]

If you go into EM and open the Management node for the server you'll see a maintenance plans node. Hightlight (click) that and you'll see the various mtce plans in the main pane.

If you dbl-click any of those they will expand into the mtce plan wizard and you can see what databases they apply to.

Also, if you use EM and highlight just the database node(s) and select View/Taskpad the main pane will list the mtce plans that a database is referenced in.

 

[JayKusch]

For a quick and dirty list ...

SELECT *
FROM MSDB.dbo.sysdbmaintplan_databases
ORDER BY plan_id

Thanks

J. Kusch

 

[nigelrivett]

Another option is never to use maintenance plans then you will know exactly what you have implemented.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[JayKusch]

Hey nigel ... I am all with you on doing everything that is within a maintenance plan as a seperate "user created" job.
Much easier to see what is being manipulated.

Question is ... how can you accomplish Log Shipping if you need to configure it w/out the use of a Mplan in SS2K?

I know personally it was a real chore doing log shipping in SS7 where you had to set up the backup/copy/load functions manually and then use BORK, BackOffice Resourse Kit, to tie everything together. REALLY MESSY!!!

Is there a method to do this more eloquently in SS2K w/out the use of an associated Mplan????

Thanks

J. Kusch

 

[Nowell]

J. Kusch

I have log shipping set up on SQL2000 standard edition, I could post a few scripts for you to look at if you like

Nowell

 

[nigelrivett]

JayKusch

Not sure what you mean.
I always advocate creating jobs on the source DB to do the backups and separate jobs on the destination to do the copy and apply.
The dest just applies everything that is in a directory in order of date - if there is a full backup it does that first.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.