SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 statistics stream is corrupt
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 08/23/2010 :  19:59:41  Show Profile  Visit jezemine's Homepage  Reply with Quote
Hi folks,

I have a rather large db (a few TB) that has a corruption problem. I don't have a backup as I don't have the disk space for it! The database is used to store only calculation results only. It can all be re-generated from the source data in a few days (which is backed up!)

Anyway when I do this:

select top 10 * from sys.key_constraints

I get this:

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

I was thinking to fix this by simply dropping the stats on each table underlying the sys.key_constraints view, and rebuilding them. But I don't know what tables those might be. Anyone know?

I can't script the view as that option seems not to be available in SSMS for system views.

Otherwise the database works fine in that I can select from any user table or view, or run any proc, without errors. it's only the system view above that's giving me grief.

Further none of the other dbs on the same server are exhibiting this.

Finally, DBCC CHECKDB fails on this db with this:

Msg 8930, Level 16, State 3, Line 1
Database error: Database 23 has inconsistent metadata. This error cannot be repaired and prevents further DBCC processing. Please restore from a backup.

The data in this db can be re-generated fairly easily in a few days so may end up doing that and just trashing the thing.



elsasoft.org

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 08/24/2010 :  01:27:03  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
The tables underlying that view will be system tables, hence you won't be allowed to drop the stats, you won't be able to see the table. You can use sp_helptext on the system views to see what the tables are.

I'd suggest regenerate. I'd suggest restore if you had a backup.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/24/2010 :  02:11:23  Show Profile  Reply with Quote
And you'll need one eye on what caused the corruption Jesse - don't want it coming back again ...
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 08/24/2010 :  14:48:35  Show Profile  Visit jezemine's Homepage  Reply with Quote
what caused it was populating a large table (4 * 10^9 rows). something I've done many, many times without problems...

In any case I am past this now. Thanks for the tip on sp_helptext - that told me the table that is horked is sys.sysidxstats which is a system base table. you can't even see these without connecting with DAC - no way am I going there!

I did find it ironic that sysidxstats has a corrupt statistics stream.




elsasoft.org
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 08/24/2010 :  17:38:13  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
quote:
Originally posted by jezemine

what caused it was populating a large table (4 * 10^9 rows). something I've done many, many times without problems...


That's what you were doing when the corruption occurred, but it's highly unlikely to be the cause. If you believe it is, call Customer Support and log a bug. The most common cause is IO subsystem glitches. Check your RAID/SAN/system logs, see if there's anything abnormal around the time that this happened.

My personal favourite WTF corruption is when sys.objects references an object_id that isn't in sys.objects. That one's typically caused by people messing with (directly updating) the system tables

--
Gail Shaw
SQL Server MVP

Edited by - GilaMonster on 08/24/2010 17:39:43
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 08/24/2010 :  22:59:34  Show Profile  Visit jezemine's Homepage  Reply with Quote
I'll ask ops check the SAN logs. I did have them attach a new 1tb LUN to this server recently that this db has a data file on, likely it's the root cause.


elsasoft.org
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000