October 21, 2009 at 3:34 pm
Hello forum members
I'm having this problem. We usually create Maintenance Plans manual way at job (please don't ask me why, I just follow orders) but there is an application that generates over of 400 databases and maybe more next weeks. I need to setup Reindex and Integrity jobs for those which DO NOT HAVE one yet and also, I would like to have control of the scheduling date.
How can I accomplish this?
Thanks in advance
October 22, 2009 at 3:44 pm
I wouldn't use database maintenance plans for something like this. Instead, I'd install custom database maintenance scripts into one of the system databases and use one or more scheduled jobs to conduct the maintenance. On my production servers, I have scripts that rebuild / reorganize indexes and update the statistics as well as one that performs the database consistency checks. I then run them from a scheduled job against all of the databases on the instance. The code checks to confirm the databases are on line and available first so that it doesn't generate errors if a database is off line or in the middle of a restore.
Depending on the size of your databases, you may want to split them into multiple jobs.
The scripts I use are modified ones written by Andrew Kelly and MS support. You can likely Google for them and find the same ones. There are also dozens of others out there you can use.
October 22, 2009 at 11:40 pm
You create a stored procedure for reindexing by defining the database_name variable/cursor. It will take the database name from sys.databases.
Create a job and use this sored procedure to schedule at your desire date & time.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply