Alter Index Rebuild fails with Severe Error - by SQLKnitter

Status : 

  Not Reproducible<br /><br />
		The product team could not reproduce this item with the description and steps provided.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


5
0
Sign in
to vote
ID 507494 Comments
Status Closed Workarounds
Type Bug Repros 4
Opened 11/2/2009 3:03:40 AM
Access Restriction Public

Description

On 

Task: Reorganize index on Local server connection
Databases: All user databases
Object: Tables and views
Error: -1073548784
Message: Executing the query "ALTER INDEX [PageUrlID_FK] ON [dbo].[WebParts] REO..." failed with the following error: "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.

DBCC CheckDB suggests no problems with the database.  The Index does not have page locks.

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)   Jul  9 2008 14:17:44   Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1) 
Sign in to post a comment.
Posted by 7846 on 7/11/2016 at 3:33 AM
We experienced the same issue recently. We have materialized view with clustered index and two additional unique indexes on it.

Those two unique indexes were disabled first.
Then mass data changes were made to underlying view tables.
Then statement

alter index all on <ViewName> rebuild

failed with error: "A severe error occurred on the current command. The results, if any, should be discarded."

Above "alter index all ... rebuild" statement became possible only after rebuilding clustered index of the view.

We were not able to reproduce it on some sample data.

Microsoft SQL Server 2014 - 12.0.4213.0 (X64) Jun 9 2015 12:06:16 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Posted by DaveCarpenter on 6/7/2012 at 5:01 AM
We’re experiencing the same issue with reorganizing the clustered index on a large table (4.4TB). When we run the alter index reorganize statement we get the following error

A severe error occurred on the current command. The results, if any, should be discarded.

If we specify the LOB_COMPACTION = ON option the index is reorganized without an error.


We’ve managed to replicate this both in our Production and Development environments on the following versions and we’ve ran the DBCC Checktable statement with no problems.


Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Sep 21 2011 22:45:45 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Microsoft SQL Server 2008 (SP1) - 10.0.2775.0 (X64) Apr 30 2010 14:31:04 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)


Posted by SQLKnitter on 1/23/2012 at 7:31 AM
Hi Sunil

This error has just recurred!

The database contains personal info - can I get a secure FTP to send it to please? We're now running SQL 2008 SP2 10.0.4000.0

Posted by Sunil [MSFT] on 12/15/2009 at 5:31 PM
Holly, for now I will close it. if this error happens, please re-activate this connect request or open a new one and attach the appropriate filess.

thanks
Sunil
Posted by SQLKnitter on 12/15/2009 at 9:11 AM
Hi Sunil

Sorry - I totally missed your comments

It's a sporadic issue, and it happens when a scheduled maintenance plan runs on our Sharepoint database server. Hasn't happened for the past month (which is a good thing, as I've been out of office).

When it does recur, what do you want, and where should I send it?

Thanks

Posted by Sunil [MSFT] on 12/11/2009 at 10:18 AM
Holly, I am resolving this as we can't repro it. Please feel free to re-activate it when you have a repro as it will help us in identifying the issue.

Thanks
Sunil
Posted by Sunil [MSFT] on 11/15/2009 at 12:17 PM
Holly: Any updates on this? We will need a physical dump or a repro of the scenario to help us investigate

thanks
Sunil

Posted by Sunil [MSFT] on 11/9/2009 at 11:17 AM
Thanks for reporting this issue. Is it possible to get a repro the backup of the database? This will help us repro the issue

thanks
Sunil
Posted by magasvs on 11/2/2009 at 9:53 AM
I have the same problem with LOB_COMPACTION = OFF on SQL Server 2005 (9.0.4035), but for the large databases only (250 GB, 30 data files by 8Gb). The problem could be reproduced by running query in SQL Server Management Studio: ALTER INDEX [PK_TArchive] ON [dbo].[TArchive] REORGANIZE.