cannot delete index

Question

Post reply

cannot delete index

espanolanthony

SSCarpal Tunnel

Points: 4204
More actions
August 21, 2010 at 7:58 am

#141549

I have a problem deleting one of the statistics in the table.
when i script
if exists (select * from sys.stats where name = N'inx_add_1' and object_id = object_id(N'[dbo].[Call_Details_]'))
DROP STATISTICS [dbo].[Call_Details_].[inx_add_1]
then get an error below
Msg 3739, Level 11, State 1, Line 3
Cannot DROP the index 'dbo.Call_Details_.inx_add_1' because it is not a statistics collection.
any idea why is this happening, and when i right click on the stats name the "script statistics as" is grayed out by default. any idea friends....

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

espanolanthony

Is the statistic you are attempting to drop the statistic maintained on an Index, that appears to be the problem ?

The DROP STATISTICS command is used to drop statistics, but it is not possible to drop statistics that are a byproduct of an index. Such statistics are removed only when the index is dropped

Source of above is:

http://blogs.technet.com/b/rob/archive/2008/05/16/sql-server-statistics.aspx

Another source:

http://technet.microsoft.com/en-us/library/ms175075.aspx

Statistics on indexes cannot be dropped by using DROP STATISTICS. Statistics remain as long as the index exists.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

espanolanthony (8/21/2010)
Msg 3739, Level 11, State 1, Line 3
Cannot DROP the index 'dbo.Call_Details_.inx_add_1' because it is not a statistics collection.

As the error says, that's an index, not a statistics collection. All indexes have associated statistics (and hence appear in sys.stats), but not all stats are indexes.

If you want to drop that, you have to drop the index itself.

DROP INDEX inx_add_1 ON dbo.Call_Details_

Before you do that, make sure that you have a good reason for dropping the index.

I'm curious, why are you dropping statistics?

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

Mohan Kumar SSCertifiable Points: 5365 More actions · Answer 3

Error indicating that it is an index. Try dropping it using DROP INDEX.....

[font="Verdana"]--www.sqlvillage.com[/size][/font]