Error 2768: statistics for index ...

  • Good Morning.  I am receiving a ton of errors with error code 2768: Statistics for index {index_name}.  I am trying to find out what the error means so that I can fix it. 

    Thanks

     

     

  • Error 2768

    Can you post a sample error message?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Error: 2768, Severity: 10, State: 1

    Statistics for INDEX 'IXNCSdSystemActivity'..

    Error: 2768, Severity: 10, State: 1

    Statistics for collection 'hind_1865773704_5A_1A_3A_6A'..

  • Anything interesting you left out in the dots?

    Hm, I've never come across this error before and the Google groups doesn't seem to be a big help, too. Is this the only error number or are there also other ones?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • I can't see anything after the dots, either.  I just get a whole bunch of these messages and a whole bunch of the following error:

     

    Error 21010:Removed 'x' replicated transactions consisting of 'x' statements in 0 secs.

    'x' = different numbers

     

  • the type of index that you are seeing is a seodo index created by the indextuning wizard, andthese should be deleted as part of the index tuning wizard process. I fear that the wizard died a death before cleaning these away.

    Use this script (scalped from this site) to remedy the situation and all should then be fine.

     

    run this in the base you are having an issue with, and if it prints output change (uncomment) the print statement to execute as commented in the script:

     

    DECLARE @strSQL nvarchar(1024)

    DECLARE @objid int

    DECLARE @indid tinyint

    DECLARE ITW_Stats CURSOR FOR SELECT id, indid FROM sysindexes WHERE name LIKE 'hind_%' ORDER BY name

    OPEN ITW_Stats

    FETCH NEXT FROM ITW_Stats INTO @objid, @indid

    WHILE (@@FETCH_STATUS <> -1)

    BEGIN

    SELECT @strSQL = (SELECT case when INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1

    then 'drop statistics [' else 'drop index [' end + OBJECT_NAME(i.id) + '].[' + i.name + ']'

    FROM sysindexes i join sysobjects o on i.id = o.id

    WHERE i.id = @objid and i.indid = @indid AND

    (INDEXPROPERTY(i.id, i.name, 'IsHypothetical') = 1 OR

    (INDEXPROPERTY(i.id, i.name, 'IsStatistics') = 1 AND

    INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics') = 0)))

    --Change this to exec (@strSQL) to remove them

    print @strSQL

    FETCH NEXT FROM ITW_Stats INTO @objid, @indid

    END

    CLOSE ITW_Stats

    DEALLOCATE ITW_Stats

  • Now, one thing is very strange here.

    You say, you get a Severity: 10 while sysmessages report a severity level of 15. Anyway, the first level is informational, while the second is repairable by the user. What action did you perform when you got these errors? Any kind of DBCC commands? Which version are you on?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

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