Database Corruption - Possible False Positive

Question

Post reply

Database Corruption - Possible False Positive

Viewing 12 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I executed the following Statement to identify the extended index:

SELECT

OBJECT_NAME(PARENT_OBJECT_ID) AS table_object,

name,

create_date

FROM sys.internal_tables it

WHERE INTERNAL_TYPE = 207

AND OBJECT_NAME(PARENT_OBJECT_ID) = 'Postal'

It returns the following:

table_objectname create_date

Postal extended_index_1227567857_384000 2016-08-08 15:35:41.563

In an attempt to drop the Index I executed the following:

DROP INDEX extended_index_1227567857_384000 ON Postal;

I get the following error:

Msg 3701, Level 11, State 7, Line 119

Cannot drop the index 'Postal.extended_index_1227567857_384000', because it does not exist or you do not have permission.

I'm sysadmin so what is wrong with my syntax?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

I cheated and went into SSMS and I found the name to be different so I executed the following and it worked:

DROP INDEX [SpatialIndex-20160728-162910] ON Postal;

I'm not sure why the name is different?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Because spacial indexes work by creating hidden tables. CheckDB picks up the hidden table name, but you can't directly affect that table.

And did you recreate the index? If so, is it without error now?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

I dropped and created it in the test environment for now.

Thanks. 🙂

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

GilaMonster (8/11/2016)
ThomasRushton (8/11/2016)
Have you tried dropping & recreating the index on that view?
as in, drop the spacial index and recreate it.

It is not a view. It is a table.

Yes I dropped the spatial index and recreated it.

I still get the same error.

I noticed a post on the web that says to set the compatibility level to a later version.

The compatibility level is set to SQL Server 2005 on a SQL Server 2012 instance which fails.

I created the index and ran DBCC with no errors on a SQL Server 2014 with a compatibility level of SQL Server 2008.

I would like to change the compatibility level to a higher version but I was told that it was not tested and not to change it. :w00t:

Is there a way to install the 2008 upgrade wizard? I do not think so?

How can I best identify Deprecated Features in this scenario?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

GilaMonster (8/11/2016)
Welsh Corgi (8/11/2016)
I run the following command in a test environment which contains the same index and I do not get any errors:
DBCC CHECKDB(PrismData)
WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS, TABLERESULTS
Hmm.
Why would you expect to get the same corruption on a different database?

Test is the same database name and structure as the Production Database.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

GilaMonster (8/11/2016)
Because spacial indexes work by creating hidden tables. CheckDB picks up the hidden table name, but you can't directly affect that table.
And did you recreate the index? If so, is it without error now?

I get the same error.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

If I change the compatibility level from 2005 to 2008 and we experience issues chaning baco to 2005 is an option.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 9

Since you're on 2014, open a case with product support.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

GilaMonster (8/17/2016)
Since you're on 2014, open a case with product support.

Actually that Server is 2012 but that is still supported.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 11

What do you mean play with the schema?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 12

Welsh Corgi (8/20/2016)
What do you mean play with the schema?

It's a spam-type post (pushing DB recovery tools), talking about filtered index problems, hence not relevant to your errors (spatial indexes)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass