March 1, 2007 at 4:24 am
Hi, guys! First of all, excuse for my English.
I am having problems with a x64 sql server 2005 9.0.2153 which has a plan with the following steps: Rebuild Index, Update Statics, Shrink DB, Check DB Integrity and finally Clean Up History. The plan is always launched automatically the same day at the same hour and it always fails on the Rebuild Index and Check DB Integrity steps.
I'm always getting the same error message in both steps: "An exception occurred while executing a Transact-SQL statement or batch." and I can get no more information but this at the log file summary.
Any ideas about what's happening? Thank you in advance
March 1, 2007 at 5:40 am
Pepe,
there can be lots of different reasons why your plan fails, but since the consistency check is one of the failing steps I would start with running DBCC CHECKDB from a query window first.
If it works without reporting any errors, when you can look further. Reindex in a maintenace plan can fail if you selected online reindexing, but some of the indexes don't support online operations.
Markus
[font="Verdana"]Markus Bohse[/font]
March 1, 2007 at 7:20 am
Hi Markus,
You are right. I've not written too many details so as not to confuse people but, for instance, at the 1st step, the rebuild index, I've got these options:
REBUILD WITH ( FILLFACTOR = 90, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
I don't know if this is enough information but there's not much more to explain, I think. I hope this is useful for you to understand me and my problem.
Thank you anyway!
March 2, 2007 at 11:07 am
Try running the Rebuild index as a separate procedure away from the other opimization steps. That is recommended anyways. I schedule the rebuild about a half hour before the other optimizations once a week. It seems to solve the problem. Also depending on the version you have it is best to not try to keep the database online while doing the rebuild of the index. When I tried it that way it always failed.
March 5, 2007 at 7:26 am
Verify that the seperate parts are working before putting them together
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply