DBAzine.com

Sections

Personal tools

You are not logged in
Log in
Join

You are here: Home » SQL Server » SQL Server Articles Archive » Scripting Database Maintenance Plans » Additional Maintenance Plan Info

Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.

Who Are You?

I am a:
[ Results | Polls ]
Votes : 3568

Comment

Above in this comment thread: Scripting Database Maintenance Plans

Additional Maintenance Plan Info

Posted by ddaiker at 2005-12-29 10:51 AM

First off I'd like to thank you for this great article, I want to automatically create a maintenance plan for a database we are installing from a shrinkwrap application we are writting and this did the trick.

One problem I'm having though is setting some of the additional information stored in sysdbmaintplans. Specificly the max_history_rows value. I can set this value from the wizard but I can't find a way to do it from the script, other than directly to the table which I prefer to avoid. Do you have any other suggestions for setting this value.

I need this for SQL Server 2000 for now.

Figuring out what the wizards are doing.

Posted by johnpaulcook at 2005-12-29 11:28 AM

You are of course correct in not wanting to update sysdbmaintplans directly. Usually the wizards invoke stored procedures to ensure that changes to system tables are done properly. The general approach to figuring out what a wizard actually does is to start SQL Profiler before running the wizard. Everything that the wizard does is captured by SQL Profiler. I used this approach when writing the article.

Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper

Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!