SQL Server Performance Forum – Threads Archive

NDF Files and errors

Hello, I am getting the following error when running a select statement against a DB:
———————————————————————————-
DMS-F-BUGCHECK, An internal system failure has occurred during operation
‘fetch’.
I/O error (bad page ID) detected during read at offset 0x0000002ad3c000 in file
‘D

rogram FilesMicrosoft SQL ServerMSSQLDataabcxyzDB.ndf’.
DMS-E-GENERAL, A general exception has occurred during operation ‘fetch’.
General SQL Server error: Check messages from the SQL Server.
DMS-E-GENERAL, A general exception has occurred during operation ‘fetch’.
—————————————————————————————- Can anyone please help me with this one? First, I’m not sure how the NDF files got created. Second, the DB is not corrupt or suspect. I am lost with this one. Thanks.

Run DBCC CHECKDB and DBCC CHECKALLOC.
If any give errors, DBCC CHECKDB (‘database’, REPAIR_REBUILD)
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Luis, Thanks, I did find errors, however I can’t run the fix because I can’t put the DB into Single user mode (Error 5070). Do you know how to boot everyone off or reset all connections so I can put into single user? Thanks.

Luis, Sorry, but I was locking myself out using QA. I closed QA, then put the DB into single user mode, but then I cannot get back into QA to run the utility. I get a login failure error message and I have no idea why. I am using the sa account and all valid logins. Any suggestions as to a workaround to run the "DBCC CHECKDB (‘database’, REPAIR_REBUILD)" or how to get into QA? Thanks.

I was finally able to get into QA and run DBCC CHECKDB (‘database’, REPAIR_REBUILD). Here are some of the errors I got:
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID -1226571726, index ID 33024, page ID (4:87710). The PageId in the page header = (1:1453203060).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID -1226571726, index ID 33024, page ID (4:87711). The PageId in the page header = (1:1453268596).
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed. DBCC results for ‘table1’.
The repair level on the DBCC statement caused this repair to be bypassed.
The repair level on the DBCC statement caused this repair to be bypassed. Server: Msg 8928, Level 16, State 2, Line 1
Object ID 877246180, index ID 0: Page (4:87710) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 877246180, index ID 0: Page (4:87711) could not be processed. See other errors for details.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DB repair_rebuild).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
—————————- Any help is greatly appreciated. Thanks.

Sorry for all the posts. I ran the DBCC CHECKDB with repair_allow_data_loss and everything seems back to normal and I don’t get the error anymore. My last major question is: "How do I know if I lost any data using the allow data loss option?" I need to know this to make the decision of if I should restore a tape backup or not. Is there anyway I can know what data was lost? Thanks.

Check the object IDs indicated in the DBCC CHECKDB results in order to verify which data is affected. Also if you restore the database from tape ensure to check consistency before deploying it on production. A set of steps for data consistency checks:

quote:Greg Robidoux and Jeremy Kadlec

Issue DBCC CHECKDB to determine all errors in a specific database
Issue DBCC CHECKCATALOG to determine any schema related issues in a specific database
Issue DBCC CHECKTABLE to determine the errors on the specific table
Create another table with the same DDL as the tables with an issue and add a suffix of ‘_New’
Issue SELECT statements with a WHERE clause utilizing a range against the original table to determine which can be read
It may be necessary to fine to this query to a single row and then to particular columns to determine which data is not retrievable
Insert the data via a SELECT statement into the ‘_New’ table
Issue DBCC CHECKTABLE to verify that the new table is error free
Attempt to salvage the remainder of the data from query a previous backup
Backup the database
Rename the original table to have a suffix of ‘_Old’
Rename the ‘_New’ table to have no suffix
Drop the ‘_Old’ table
Test the application to ensure it performs as expected
Issue DBCC CHECKDB to ensure no allocation or consistency issues in the entire database
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

What SQL and SP are you using? Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Sql 2000 5.0 SP4 Here is a listing of all the errors it fixed. Please let me know if you think there was data loss. Thanks: —————————————————————————————
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID -1226571726, index ID 33024, page ID (4:87710). The PageId in the page header = (1:1453203060).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID -1226571726, index ID 33024, page ID (4:87711). The PageId in the page header = (1:1453268596).
The error has been repaired.
The error has been repaired.
CHECKDB found 0 allocation errors and 2 consistency errors in table ‘(Object ID -1226571726)’ (object ID -1226571726).
CHECKDB fixed 0 allocation errors and 2 consistency errors in table ‘(Object ID -1226571726)’ (object ID -1226571726).
Table error: Object ID 877246180, index ID 3 will be rebuilt.
Non-Clustered index successfully restored for object XZD
CHECKDB found 0 allocation errors and 4 consistency errors in database DB.
CHECKDB fixed 0 allocation errors and 4 consistency errors in database DB.
——————————————————————-
What is a consistency error?

"How do I know if I lost any data using the allow data loss option?" I need to know this to make the decision of if I should restore a tape backup or not. Is there anyway I can know what data was lost? You never know if repair_allow_data_loss will fix all problems or not.
After repair_allow_data_loss (only work from sp2, tha’s why my question), if you run again DBCHECK (no options) or CHECKALLOC is there any error? Reading your last post, I think all was fixed but run DBCHECK again. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Luis, We ran the command and everything checked out okay. Thanks for the help. I am still a little hazy about the "allow data loss" command. I guess data is lost, but not rows of data in the tables, just index, header and consistency information, right? In what instincances will this command actually delete data from the table? What would I look for in the log to see if the command needs to delete corrupt data? Thanks.

Allow data loss is the last chance to fix those kind of errors. You are right about index, header, etc.
I don’t know when this command delete data from table, but if that occurs, you will know after running looking messages.
I that case, is better restore database. I know that kind of problems (like yours) occurs when you (or some proccess) interrup a shrink database, for example. Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Does anyone have a sample of text that is generated from the DBCC CHECKDB log results file that they can post here as a sample of what it would look like if data needed to be deleted from the table? This would help a great deal so I know in the future that it is okay to repair my DB unless I see this text indicating that I will lose Fact or Dimension critical data. Thanks.

Have you tried the steps defined above to check consistency?
From the DBCC results above I don’t think data is touched.
I never had any data loss while running DBCC with ALLOW_DATA_LOSS option. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya, Yes, everything turned out great. It fixed the issue and the CHECK DB returned no errors. Thanks for the input, it is good to know that you have never experienced data loss with that command and I will keep this in mind. You can see to a beginner DBA that running an "allow data loss" command can be scapry, especially if you aren;t sure what the heck that little globe is doing when running the procedure in QA. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Glad that you resolved the issue -as a separate note there may be cases when you would not be able to switch the database into the single user mode .
In cases like this consider doing a "swap"
1) identify the table that has the problem (the error message contains the page number so you need to turn trace flag 3604 on and do a DBCC page (db_name,file_number,page_number) to get more info. IN the middle of the output of DBCC page you would see m_obj- this is the objectid that has issues. object_name function on (m_obj) will give you a table name)
2) export the data you need into separate table, i.e.
select * into soon_to_be_fixed_table from bad_bad_corrupted_table with (nolock) where <conditions>
(say you only need last X days to do immediate processing, the rest could be inserted later).
2) "Swap" the tables
sp_rename bad_bad_corrupted_table, bad_bad_look_into_later
sp_rename soon_to_be_fixed , bad_bad_corrupted_table
3) Build the indexes on the newly created table
4) Apply the changes that have occured on original table between when you started select .. into and finished sp_rename you are done, now you can slowly insert the rows for the previous days , do investigation on the original table without affecting current OLTP processing,etc. the problem with DBCC CHECKDB is that it
a) requires single user mode. (Good reason to look forward towards SQL Server 2005).
b) very resouce intensive and wasteful – why check allocation of all the tables in the databases in the middle of processing when only one reported errors? such checking can be done later at time of minimal activity.. Simas
P.S. Check out Inside SQL Server 2000 by Karen Delaney -she writes at length about DBCC page statement and data return (plus it is an excellent reference).

True, it will be very helpful to refer Kalen’s book and books online for information on these DBCCs. Rweinstein, you will be used to this soon once you are well versed with the components & processess used in SQL.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>