June 1, 2011 at 6:38 am
I set up a Maintenance Plan Task to reindex all five of my databases every Sunday at 7PM. The process starts with "Back Up Database Task", then proceeds to "Rebuild Index Task", and then finishes with a "Maintenance Cleanup Task" to delete backed up files older than 1 week.
When I go into the Log File Viewer, I see nothing but failures for each attempt that was made with the message: The job failed. The Job was invoked by Schedule 3 (Reindexing Subplan_1). The last step to run was step 1. The databases are backed up, but I believe this means the reindexing is not taking place. My question is, where should I look to find out why this task is failing and what can I do about it.
June 1, 2011 at 6:44 am
there should be a text file produced in the LOG directory of the path sql was installed to.
---------------------------------------------------------------------
June 1, 2011 at 2:23 pm
Open the Maintenance folder in SSMS, right-click on the maintenance plan and select View History. This history will break out each task and show you the status of each task.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 2, 2011 at 7:45 am
Ok. So I am in the right place and found the error:
"failed with the following error: "Online index operation cannot be performed for index 'HQ_SQL_PK' because the index contains column 'SQL' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml."
And from what I can tell from the previously posted topic on this error,
"You cannot do an online rebuild of a clustered index if the table contains any LOB data (text, ntext, image, varchar(max), nvarchar(max), varbinary(max))"
http://www.sqlservercentral.com/Forums/Topic588375-146-1.aspx
Thank you for the help!
June 2, 2011 at 7:48 am
jennigirl (6/2/2011)
Ok. So I am in the right place and found the error:"failed with the following error: "Online index operation cannot be performed for index 'HQ_SQL_PK' because the index contains column 'SQL' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml."
And from what I can tell from the previously posted topic on this error,
"You cannot do an online rebuild of a clustered index if the table contains any LOB data (text, ntext, image, varchar(max), nvarchar(max), varbinary(max))"
http://www.sqlservercentral.com/Forums/Topic588375-146-1.aspx
Thank you for the help!
Here's a fully tested script that won't havebugs like that one!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply