Sql-server – MSSQL 2014 “The provided statistics stream is corrupt” Error 9105

sql serverstatistics

I have a database that prevent to see user properties due to this error.
I've begin investigating and the problem comes from sys.asymmetric_keys and sys.symmetric_keys system views inside the database.
Both views uses the sys.sysobjvalues table that contains the following statistics:

Stats Name | Column Name
_WA_Sys_00000002_0000003C objid
_WA_Sys_00000003_0000003C subobjid
_WA_Sys_00000004_0000003C valnum
_WA_Sys_00000005_0000003C value
clst valclass, objid, subobjid, valnum

The failure appears on the column "value"
DBCC SHOW_STATISTICS ("sys.sysobjvalues",_WA_Sys_00000005_0000003C) that returns:

Name
_WA_Sys_00000005_0000003C
(1 row(s) affected)

All density | Avg.Length | Columns

0,004830 8 | 7,204199 | value
(1 row(s) affected)

Range_Hi_KEY
Msg 9105, Level 16, State 25, Line 43
The provided statistics stream is corrupt.

Connected using a DAC connection I've tried to delete it (permissions error) and update it without success. The [UPDATE STATISTICS sys.sysobjvalues WITH fullscan, columns] command fails with the "The provided statistics stream is corrupt " error but [UPDATE STAT.. WITH fullscan, index] works but the error continues.

DBCC CheckDB returns no errors for this table (sys.sysobjvalues).

The server version is: Microsoft SQL Server 2014 (SP2-CU1) (KB3178925)(Hypervisor)

Any idea on how could I repair this DB without a Restore (All my available restores have the error on it)

Many Thanks

Best Answer

The problem may be in the damaged statistics, and probably in the one that was created automatically

To search for problem statistics you can use:

EXEC sp_updatestats

exec will iterate over all the tables and statistics, and if there are problems, it will show which statistics, more precisely the table, is the problem.

To remove auto_created statistics, you can use the following script:

select 'drop statistics [' + OBJECT_NAME (OBJECT_ID) + '].[' + Name + ']' 
 from sys.stats 
where auto_created = 1 
  and OBJECT_NAME (OBJECT_ID) = 'DB PROBLEM TABLE NAME'

The script will generate rows for deleting auto_created statistics for the table "DB PROBLEM TABLE NAME" table

After running the generated lines, rerun 

EXEC sp_updatestats

to check for errors.

Not superfluous would be 

exec sp_msforeachtable N'UPDATE STATISTICS? WITH FULLSCAN'
Related Question