Corruption in a database after server break-down

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Data Corruption Issues

Corruption in a database after server break-down

Author

Topic

erikk
Starting Member

2 Posts

Posted - 2009-02-17 : 18:01:26

At a customer site (SBS 2003), they had a complete server break-down. I managed to transfer active directory and all user files to new server (HP ProLiant ML350G5). However what slipped my attention was that the backup of the SQL database, that I restored was corrupted.

Below is the output from DBCC CHECKDB. I checked the eventlog and it has got errors in it right since we put in the new server (mid-January)... Which probably means there is no opportunity of restoring from a good backup!

Can anyone give any advise on how to proceed from here on?

Thanks a lot in advance,
Erik

------------
DBCC CHECKDB

Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 562949950799872 (type Unknown), page ID (1:30447) contains an incorrect page ID in its page header. The PageId in the page header = (118:7274605).
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 8, index ID 0, partition ID 524288, alloc unit ID 524288 (type In-row data), page (65535:0). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29362185 and -4.
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysfiles1' (object ID 8).
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 162099618, index ID 3, partition ID 72057594178109440, alloc unit ID 72057594183090176 (type In-row data). Page (1:10514) is missing a reference from previous page (1:30447). Possible chain linkage problem.
Msg 8928, Level 16, State 1, Line 1
Object ID 162099618, index ID 3, partition ID 72057594178109440, alloc unit ID 72057594183090176 (type In-row data): Page (1:30447) could not be processed. See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 162099618, index ID 3, partition ID 72057594178109440, alloc unit ID 72057594183090176 (type In-row data). Page (1:30447) was not seen in the scan although its parent (1:54102) and previous (1:34490) refer to it. Check any previous errors.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'KREPOSTUDLIGN' (object ID 162099618).
Msg 8928, Level 16, State 1, Line 1
Object ID 418100530, index ID 0, partition ID 72057594082557952, alloc unit ID 72057594087538688 (type In-row data): Page (1:30449) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 418100530, index ID 0, partition ID 72057594082557952, alloc unit ID 72057594087538688 (type In-row data), page (1:30449). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29362185 and -4.
Msg 8928, Level 16, State 1, Line 1
Object ID 418100530, index ID 0, partition ID 72057594082557952, alloc unit ID 72057594087538688 (type In-row data): Page (1:30450) could not be processed. See other errors for details.
CHECKDB found 0 allocation errors and 3 consistency errors in table 'FINPOST' (object ID 418100530).
CHECKDB found 0 allocation errors and 8 consistency errors in database 'C5Database'.

--------
EVENTLOG
Event Type: Error
Event Source: MSSQL$SQLEXPRESS
Event Category: (2)
Event ID: 824
Date: 17-02-2009
Time: 14:54:22
User: EMJ\rudolf
Computer: SERVER
Description:
SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:30447; actual 118:7274605). It occurred during a read of page (1:30447) in database ID 7 at offset 0x0000000edde000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\C5Database.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 38 03 00 00 18 00 00 00 8.......
0008: 12 00 00 00 53 00 45 00 ....S.E.
0010: 52 00 56 00 45 00 52 00 R.V.E.R.
0018: 5c 00 53 00 51 00 4c 00 \.S.Q.L.
0020: 45 00 58 00 50 00 52 00 E.X.P.R.
0028: 45 00 53 00 53 00 00 00 E.S.S...
0030: 0b 00 00 00 43 00 35 00 ....C.5.
0038: 44 00 61 00 74 00 61 00 D.a.t.a.
0040: 62 00 61 00 73 00 65 00 b.a.s.e.
0048: 00 00 ..

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2009-02-18 : 14:36:01

Given that all your backups contain this corruption, restoring from a backup is out of the question. You've really only got one option here - extract the data into a new database. Repair isn't an option as the sysfiles1 system table is corrupt and cannot be repaired by DBCC.

Hope this helps.

Paul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)
SQL Server MVP, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code
Author & Instructor of Microsoft Certified Master - Database course

erikk
Starting Member

2 Posts

Posted - 2009-02-20 : 06:51:38

Thanks a lot, Paul, for your reply. Alas I didn't see that yesterday.

I do have an application specific problem (this is an accounting application): when I am running an export from the application some rows in the affected tables are not exported. However I was wondering whether it would be possible to export the tables from the SQL Server Management Studio one by one, whereafter a new database is created and alle tables are imported again (this time again via SQL Server Management Studio)? - taking into consideration the errors that DBCC CHECKDB gives?

Regards,
Erik

paulrandal
Yak with Vast SQL Skills

899 Posts

Posted - 2009-02-23 : 20:32:18

Yup - that should work. Good luck!

Paul S. Randal, Managing Director, SQLskills.com (www.SQLskills.com/blogs/paul)
SQL Server MVP, Contributing Editor of TechNet Magazine
Author of SQL 2005 DBCC CHECKDB/repair code
Author & Instructor of Microsoft Certified Master - Database course

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources