ALTER INDEX command fails during Maintenance Plan - SQL Server 2005

Hi,

During the execution of maintenance plan, it rebuilds the indexes on the tables.  An ALTER INDEX command is executed, but the command stalled and hung the maintenance job.  Error that was reported in the log said it failed (see the error message below).

I am not sure what to do with this error or how to fix the error.  Appears to be a problem with the index [a215_ix1_a]  being used for the table [a255].

What could I do to resolve the error?

Rebuild Index (TOMLINSON)
Rebuild index on Local server connection
Databases: pbtf_ArcSDE
Object: Tables and views
Original amount of free space
Task start: 2010-08-16T10:17:13.
Task end: 2010-08-16T12:18:07.
Failed:(-1073548784) Executing the query "ALTER INDEX [a215_ix1_a] ON [cf_pbtf].[a255] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )
" failed with the following error: "A severe error occurred on the current command.  The results, if any, should be discarded.
A severe error occurred on the current command.  The results, if any, should be discarded.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Open in new window

gtrappAsked:
Who is Participating?
 
dba2dbaConnect With a Mentor Commented:
The error message you see is a generic error. I have received similar error with a backup failure due to space issues.

Did you check the log space in the database while the index rebuild is executing, please check it using dbcc sqlperf while the job runs and ensure that the log has enough space for index rebuild.

You can also use an output file in the maintenance plan job properties, it might log additional details when it fails. Also, you could try running the alter index command in the error in Management Studio, to see if you get any errors.

Thanks.
0
 
lundnakConnect With a Mentor Commented:
Did you receive any errors in the SQL Server error log at around 12:18:07.  If so, could you post them?
0
 
gtrappAuthor Commented:
No errors in the SQL Server log and nothing in the Event View.

Is there something that I could do with the index or the table?
0
 
gtrappAuthor Commented:
I checked the log file size during the execution of the job and the size showed the usage was only 2%. 1.5 GB was available.

What I did to solve the problem was restart the ArcSDE service. Then, the backup completed successfully.
0
 
gtrappAuthor Commented:
I restarted the service and clearned out locks on tables and objects in the database and that cleared the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.