Hi!
Just wondering if I'm missing something super obvious here due to my lack of DBA knowledge.
Our UP_REBUILD_ALL_INDEXES job was not set up (long story...) so I'm trying to do it now. But when I try to execute the procedure I get this error:
Msg 2501, Level 16, State 45, Line 1Cannot find a table or object with the name "circle_info". Check the system catalog.
I don't know what circle_info is or why the procedure is trying to find it. Anyone else run into a similar problem that might be able to help me out here?
Thanks!
Sounds to me like someone has modified your copy of UP_REBUILD_ALL_INDEXES, and included a reference to a local table that no longer exists.
But there's a problem with UP_REBUILD_ALL_INDEXES anyway, which is that it...well... rebuilds all the indexes, just like it says on the box, even when they don't need to be rebuilt. That takes longer than needed, and takes the index offline while it's being rebuilt. That's not really a problem if you can have a downtime period while the operation completes, but if the system is expected to be up 24/7, as we mostly are now, it's a problem.
We were running this once a week and hitting problems with web orders and scheduled reporting in the middle of the night while it was running (and it was taking hours, which is another problem). So I did some research and made a new script called LP_MAINTAIN_INDEXES, which takes a more minimalist approach.
It assesses the index state first: If it's insignificantly fragmented it leaves it alone, if it's mildly fragmented, it does a defrag instead of a rebuild, which is not disruptive, and only if it's badly fragmented, does a rebuild, preferring online rebuild where possible, to avoid taking indexes offline at all.(that's only possible in Enterprise version SQL, I think, and there are about 14 indexes in Tess that can't do it),
We now run that script every night, to try to stop fragmentation building up to the point of needing a rebuild. It takes about 5 to 25 minutes each night, it averages 16 defrags and two online rebuilds per night, and it hasn't done an offline rebuild since January (and only 5 times ever, since November 2008).
I think some other people have done similar index management work. My version is posted here., if you'd like to have a look.
Ken
That was an awesome reply! Thanks, Ken.