September 11, 2011 at 7:40 am
SQL:
ALTER INDEX [index_name] ON [dbo].[table_name] REBUILD WITH (FILLFACTOR = 90);
Exception:
Location: statutil.cpp:2719
Expression: m_fInitialized && m_statBlob.CbSize() && iKey >= -1 && iKey < m_statBlob.GetHeader()->GetKeyCount()
SPID: 52
Process ID: 3680
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
I can SELECT all rows from the table with no issues and I am able to rebuild all nonclustered indexes on the table save for two.
The index definitions of the two that will not rebuild and fail with the above exception:
CREATE NONCLUSTERED INDEX [index_name_1] ON [dbo].[table_name]
(
[col_A] ASC, -- VARCHAR(8)
[col_B] ASC, -- VARCHAR(8)
[col_C] ASC, -- VARCHAR(48)
[col_D] ASC, -- VARCHAR(32)
[col_E] ASC, -- VARCHAR(32)
[col_F] ASC -- INT
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [index_name_2] ON [dbo].[table_name]
(
[col_A] ASC, -- VARCHAR(8) * COMMON
[col_B] ASC, -- VARCHAR(8) * COMMON
[col_G] ASC, --VARCHAR(16)
[col_C] ASC -- VARCHAR(48) * COMMON
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 90) ON [PRIMARY]
GO
Some particulars about the server:
BuildClrVersion v2.0.50727
Collation SQL_Latin1_General_CP1_CI_AS
ComparisonStyle 196609
Edition Enterprise Edition (64-bit)
EditionID 1804890536
EditionIDDescription Enterprise
EngineEdition 3
EngineEditionDescription Enterprise
IsClustered 1
IsFullTextInstalled 1
IsIntegratedSecurityOnly 0
IsSingleUser 0
LCID 1033
ProductVersion 9.00.4053.00
ProductLevel SP3
SqlCharSet 1
SqlCharSetName iso_1
SqlSortOrder 52
SqlSortOrderName nocase_iso
I can copy the data into a new table and build all indexes on the new table so I was leaning towards this being a corruption issue...however DBCC CHECKTABLE returns no error messages.
DBCC results for 'table_name'.
There are 10871 rows in 154 pages for object "table_name".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Any ideas as to what I can try to determine the root cause and get these indexes rebuilt?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 11, 2011 at 9:11 am
CheckDB?
If it too comes back clean, make sure you're on the latest service pack and call CSS.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2011 at 11:02 am
Thanks Gail.
CHECKDB ran clean on the 7th. I may run it again tonight just to see.
These databases are on their way to a 2008R2 instance in the coming weeks so applying the Service Pack is not a measure I will likely take at this time.
I do have a backup of the DB and can presumably recreate the issue from a restore.
In an attempt to fix the issue I:
1. Created a new table with the same schema.
2. Inserted the data from the problem table into the new table.
3. Added all indexes to the new table successfully!
4. Renamed the problem table with a _old suffix.
5. Renamed the new table with the problem tables original name.
After this was done, as a "let's just see" I ran the REBUILD statements against the original table with the _old suffix and it worked fine :ermm:
So in the spirit of not changing anything unnecessarily I dropped the new table and renamed the _old table to its original name. It is the strangest thing I have seen happen with SQL Server in a long while.
I may try restoring the backup and recreating the issue. If I can do recreate it I will try dropping all other tables in the database to get the database to a manageable size, one that I can share.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 11, 2011 at 11:08 am
Looking at the error, it appears to be something to do with the statistics (which checkDB doesn't check). Can't tell more than that, CSS can debug system asserts cause they have source-code access.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2011 at 9:53 am
GilaMonster (9/11/2011)
Looking at the error, it appears to be something to do with the statistics (which checkDB doesn't check). Can't tell more than that, CSS can debug system asserts cause they have source-code access.
Good to know, thanks.
In thinking more about the statistics angle, I ran an index rebuild job (which also updates column statistics if data was touched since the last update) after I got the new table and indexes in place. I am wondering if that job updated statistics on the _old table thereby resolving the assertion failure issue.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 12, 2011 at 2:27 pm
If it was a rebuild-everything job, quite likely.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply