2

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

3
1

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'

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .