April 11, 2006 at 8:00 am
Does anyone know if the stored procedures sp_add_maintenance_plan, sp_add_maintenance_plan_db, sp_add_maintenance_plan_job, etc. have been replaced or just thrown out in SQL Server 2005?
My company is trying to decrease time and increase accuracy in deploying a SQL Server application (along with SQL Server) see thread: http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=272456 and Microsoft has announced that these procedures are going to be removed in the future, which I figure might mean SQL Server 2005.
Thanks in advance, Brian
April 12, 2006 at 11:04 am
I checked SQL 2005 BOL and the procedures still exist with this warning:
Microsoft SQL Server 2005 supports the following system stored procedures that are used to set up core maintenance tasks that it must have to guarantee database performance. These stored procedures are used with database maintenance plans. This feature has been replaced with maintenance plans that do not use these stored procedures.
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
I'd re-create the plans if I were you 🙂
April 12, 2006 at 12:09 pm
Linda, thankyou for the information. I haven't downloaded 2005 yet, and forgot that I could look in MSDN. One question that wasn't easily answerable from the BOL, was how best to replace the maintenance plans. Specifically I was looking for a way to programmatically create the maintenance plans, but what I read in BOL inferred or implied that you must use a GUI, which is not an easily automated task.
Fortuanately Scott Coleman gave me an excellent answer in the other thread, and I figured I would quote it here for others to see:
The sqlmaint.exe utility and the msdb tables that support SQL 2000 maintenance plans are going away. SQL 2005 includes the stored procs you mention, but the tables are there only if SQL 2005 was installed as an upgrade. A new SQL 2005 installation won't have the tables that those stored procs use.
SQL 2005 maintenance plans are implemented in SSIS tasks. You can automate the creation of maintenance plans, but probably not in T-SQL. Look at the "Working with packages programmatically" topic under "SQL Server programming reference" - "Integration Services programming" in BOL. The quickest way to figure it out might be to create a maintenance plan using the wizard, then write code to load the package and explore the objects in it. This should give you enough information to write code to create new packages on other servers. You may need to throw in some SMO code to create SQL Agent jobs to run the maintenance packages.
Thanks again, Brian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply