Suspect data in SQL 2005 DB - SQL Server Forums

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

SQL Server 2005 Forums

SQL Server Administration (2005)

Suspect data in SQL 2005 DB

Author

Topic

adminscott
Starting Member

13 Posts

Posted - 2008-08-18 : 08:21:52

We have been having various errors throughout the day about being unable to connect to the DB and that there was data corruption in tables.

I have run script:

Use PureH2o

go

dbcc checkdb with data_purity

go

got results:

Msg 8909, Level 16, State 1, Line 2
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 2533275048869888 (type Unknown), page ID (1:190691) contains an incorrect page ID in its page header. The PageId in the page header = (0:-2105462822).
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
DBCC results for 'sys.sysrowsetcolumns'.
There are 1610 rows in 15 pages for object "sys.sysrowsetcolumns".
Msg 8928, Level 16, State 1, Line 2
Object ID 308196148, index ID 5, partition ID 72057594038779904, alloc unit ID 72057594043105280 (type In-row data): Page (1:190691) could not be processed. See other errors for details.
There are 130398 rows in 6897 pages for object "tblMessageLog".
CHECKDB found 0 allocation errors and 1 consistency errors in table 'tblMessageLog' (object ID 308196148).
Msg 8951, Level 16, State 1, Line 2
Table error: table 'tbl Client' (ID 606625204). Data row does not have a matching index row in the index 'AgeGroupID' (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 2
Data row (1:13169:10) identified by (HEAP RID = (1:13169:10)) with index values 'AgeGroupID = 6 and HEAP RID = (1:13169:10)'.
Msg 8951, Level 16, State 1, Line 2
Table error: table 'tbl Client' (ID 606625204). Data row does not have a matching index row in the index 'AgeGroupID' (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 2
Data row (1:13169:12) identified by (HEAP RID = (1:13169:12)) with index values 'AgeGroupID = 6 and HEAP RID = (1:13169:12)'.
Msg 8951, Level 16, State 1, Line 2
Msg 8955, Level 16, State 1, Line 2
Data row (1:213701:10) identified by (HEAP RID = (1:213701:10)) with index values 'AgeGroupID = 10 and HEAP RID = (1:213701:10)'.
Msg 8986, Level 16, State 1, Line 2
Too many errors found (201) for object ID 606625204. To see all error messages rerun the statement using "WITH ALL_ERRORMSGS".
There are 796456 rows in 32311 pages for object "tbl Client".
CHECKDB found 0 allocation errors and 5876 consistency errors in table 'tbl Client' (object ID 606625204).
CHECKDB found 0 allocation errors and 5878 consistency errors in database 'PureH2o'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PureH2o).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

this is just a snippet as there are so many rows affected - i can no longer get access to the DB as it is set as suspect.

Any help would be great

adminscott
Starting Member

13 Posts

Posted - 2008-08-18 : 08:48:30

i am now attempting to restore from backup but an unable to do so due to locks on the DB. i have also attempted to take the DB offline but canmnot for the same reason

mcrowley
Aged Yak Warrior

771 Posts

Posted - 2008-08-18 : 13:29:44

Find whatever application is logged into the database, and shut that application down. The application won't be able to work while you are restoring the database, after all.

Or, you can issue


alter database databasename set restricted_user with rollback immediate

in a query window.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources