January 1, 2010 at 10:18 am
Hi everyone
I have a very strange problem. In a big database one of the NDF files is corrupted. Database is still working perfectly anyway. That NDF file has some data, which i don't need at all, because are very old.
I tried DBCC CHECKDB and DBCC CHECKTABLE (because the problem is in a single table), but both failed even with repair_allow_data_loss.
I can't delete those records, because database becomes suspect. I tried deleting them one by one via a cycle, but still it doesn't work. Anyway, with this last way, at least the database don't become suspect.
The strange thing is that i can read the data perfectly, but can't delete them...
Can somebody help me?
Thank you!
January 1, 2010 at 12:23 pm
Please run the following and post the full and complete output.
DBCC CheckDB(<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Do you have a clean backup of this database? A backup without corruption?
Take a look at this article. http://www.sqlservercentral.com/articles/65804/
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
January 3, 2010 at 10:40 am
Unfortunately it takes a lot of time, because db is more than 200GB, and DB is in live production, so i can't allow to do it...
During new year days i had time and tried different things (did a backup before). Nothing fixed the error, and unfortunately i didn't save the log of dbcc.
Anyway, i was able to find that a non clustered index was also corrupted, and i was unable to drop it (i tried dropping all indexes of that table).
The situation is as follows.
I have a big table with data partitioned horizontally (every month a different NDF). It was just by case that i found July 2008 corrupted. This happened when i wanted to delete old data of 2008.
As i told before: I can read all the data of July 2008, but i can't drop them (or some of them), because i get inconsistency error...
I have the impression that is index fault, but i don't have any idea how can i get rid of this index (which is not even used for performance at the moment)...
Till now i have only one idea: Exporting data to a new database (looking that i can read everything). But this requires too much time. Maximum time i can allow to have DB offline is 7 hours, and exporting 200GB data requires more time...
Any idea?
January 3, 2010 at 11:06 am
Renis Cerga. (1/3/2010)
Unfortunately it takes a lot of time, because db is more than 200GB, and DB is in live production, so i can't allow to do it...
You need to run it. It'll take a while, that's unfortunate but it does have to be run. There's no way to tell what's wrong and how to fix it without seeing the full and complete output of CheckDB. Ask your boss if you can run it during a maintenance period. It doesn't require that the DB be offline, though it will slow things down during the time it runs.
Any idea?
Without seeing the output of checkDB, no.
You didn't answer my question about backups. Do you have a clean one? One without corruption?
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply