I want to change DB maintenance plan from "remove
transaction log if older than 4 weeks" to "remove
transaction log if older tha 1 day", for hundreds of SQL
servers. What is the best way to do it automatically? I
checked system tabels in MSDB but found no info about DB
maintenance plan. How to write a T-SQL or VB to do such a
task? Does SQL DMO offer the properties?

Thanks.

You can do this several ways. One is to update the system tables directly
( sysjobsteps to be exact ) but is the least advisable. Another is to use
sp_update_jobstep. In either case you will need to replace the setting for
4WEEKS to 1DAYS in the current xp_maint command for each job step. You can
do this with a simple REPLACE() command. The trick is to find the right job
steps. BOL has more info on sp_update_jobstep.

--

Andrew J. Kelly
SQL Server MVP


"Locus Adam" <localadm (AT) hotmail (DOT) com> wrote