SQL Server Performance Forum – Threads Archive

data corruption … how now ?

Hi there, Running SQL 2000 SP 3a on Windows 2003, on it’s own instance. I get the following message
"[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation. Connection Broken" when a select statment is run against a database. At first it seemed that it might be index releated, since I was able to recreate all the indexes under new names, while the old indexes still existed. When I tired rebuilding indexes, I got an error :
"Server: Msg 3624, Level 20, State 1, Line 1 Location: recbase.cpp:1378
Expression: m_offBeginVar < m_SizeRec
SPID: 61
Process ID: 1560 Connection Broken
" Dropping indexes to try and recreate hasn’t given me any joy – I can’t create the indexes any more. Doing a DBCC chec table gave me
"DBCC results for ‘AcctTrans’.
There are 1041869 rows in 29977 pages for object ‘AcctTrans’.
CHECKTABLE found 0 allocation errors and 38 consistency errors in table ‘AcctTrans’ (object ID 802817922).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (VP4000.dbo.AcctTrans )." Due to lovely way the database was set up, the recovery mode is simple, and wouldn’t you guess it, all the post month-end backups failed. So recovery is an issueThere is no DB RI, so while I could repair and lose the 38 rows, I have no real way of tracking what will then be broken. Any thoughts ? Otherwise I will need to tell the guys that they have lost their 38 rows, and ask them if they can programmatically determine the missing data, and recreate it.

Enable the single user database option.
Execute the DBCC CHECKTABLE statement for the referred table, and specify the REPAIR_REBUILD option. Ensure the current backup is worked fine, and if possible export data using DTS. If you see any system dump on SQL error log as referred in this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;330307 then adopt the hotfix referred. 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.

Hi Satya, thanx for the prompt response. You reckon we should try the REPAIR_REBUILD … that should not lose any data, even though the report of a straight CHECKTABLE says that REPAIR_ALLOW_DATA_LOSS is the minimum repair level ? We have made a backup now, but due to the lack of transaction logging (due to simple mode), I can’t try and recover to a point in time after the month end. Since this is production, I’d like to try and make sure before I do anything. Thanx again

The REPAIR REBUILD option will take care of data FAST and includes time-consuming repairs such as rebuilding indexes. These repairs can be done without risk of data loss. As it looks inconsistency on this table alone, try to export out the data to another copy of the table in other server or database.
Script out the table creation with indexes/PK etc.
Drop the table and recreate with above script.
Then import data from exported-copy table. And another suggestion is if a production database recovery model is SIMPLE then ensure backups are performed in a short interval of time and copy it to a safe location such as DR server. 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.

I would first restore the database on another server and run both REPAIR REBUILD and if that does not work out then REPAIR_ALLOW_DATA_LOSS option to see how much data is getting lost. Then decide the plan of action…. BTE as far as I know 38 consistency errors does not necessarily mean 38 rows. It could be 38 pages – index / data. Peers, please correct me if I am mistaken. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

FIRST thing I would do is to take a dump of the database, so you can at least get back to this point… Brett :cool:

Yes Gaurav, it refers to index pages not rows. 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.

]]>