Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Corrupted Database Expand / Collapse
Author
Message
Posted Friday, February 21, 2014 12:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 10:30 AM
Points: 238, Visits: 317
I need help on fixing checkdb error.

Here's the output of checkdb:

Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID -1627389056, index ID 0, page ID (1:611861). The PageId in the page header = (14131:894586624).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:611862). The PageId in the page header = (0:0).
Server: Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID 0, page ID (1:611863). The PageId in the page header = (0:0).
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 226099846, index ID 0: Page (1:611860) could not be processed. See other errors for details.
Server: Msg 8939, Level 16, State 1, Line 1
Table error: Object ID 226099846, index ID 0, page (1:611860). Test (IS_ON (BUF_IOERR, bp->bstat) && bp->berrcode) failed. Values are 2057 and -1.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 226099846, index ID 0: Page (1:611861) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 226099846, index ID 0: Page (1:611862) could not be processed. See other errors for details.
Server: Msg 8928, Level 16, State 1, Line 1
Object ID 226099846, index ID 0: Page (1:611863) could not be processed. See other errors for details.
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1627389056)' (object ID -1627389056).
CHECKDB found 0 allocation errors and 2 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 5 consistency errors in table 'REQ_ITEM' (object ID 226099846).
CHECKDB found 0 allocation errors and 8 consistency errors in database 'PMM'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PMM ).



It's running on SQL 2000 SP3.
Can I repair these errors with 'REPAIR_REBUILD'?

Thanks in advance,
David.

Post #1392105

GilaMonster
Posted Monday, December 03, 2012 12:51 PM



SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 40,830, Visits: 33,767
David Kang (12/3/2012)
--------------------------------------------------------------------------------
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (PMM ).


Since repair allow data loss is the minimum repair level necessary, running checkDB with any lesser repair level will be a waste of time. Minimum repair level is exactly that.

You will lose data. Four pages of data from the table REQ_ITEM.

I assume there's no good backup available...


--------------------------------------------------------------------------------
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability


We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Post #1392119

David Kang
Posted Monday, December 03, 2012 1:10 PM

SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 1:59 PM
Points: 30, Visits: 134
Gail! Thanks for quick response.

So, can I run page level restore for this? or restore the whole DB to temp location then extract the 'REQ_ITEM' table?

Thanks again,
David.

Post #1392123

GilaMonster
Posted Monday, December 03, 2012 3:33 PM



SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:05 AM
Points: 40,830, Visits: 33,767
If you have a clean backup (one without any corruption) and an unbroken chain of log backups up to present, you can do a page restore. Or you can restore the full backup over the DB followed by all log backups to bring the DB back to current time with no corruption (take a tail-log backup first).

If the DB is not in full recovery or you don't have the necessary log backups, then you're looking at either losing data by restoring to the last good backup or by repairing. You can, in that case, try to repair then restore a copy of the DB elsewhere and sync in the missing data.


--------------------------------------------------------------------------------
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability


We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Post #1392167

jonmichel72
Posted Wednesday, December 05, 2012 12:15 AM

Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 07, 2013 5:14 AM
Points: 4, Visits: 2
Now a Days, when you search on the Google, many companies providing a data recovery services as well as recovery software. In such types of software one is recoveryfix for sql repair software, this company offering a free trial version software, with the help of this trial version you can see the preview of recoverable data.

Thanks

Post #1392810

opc.three
Posted Wednesday, December 05, 2012 11:13 PM



SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:56 PM
Points: 7,069, Visits: 12,512
Since you're on SQL 2000 you do not have the option of doing page restores. As Gail outlined, you can restore from backup that do not include the corruption (including tail-log) to bring the database to current or look into bringing up a parallel copy of the database that does not include the corruption to recover the data manually, or do the repair allowing for data loss.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy


Post #1393328

David Kang
Posted Monday, December 10, 2012 1:15 PM

SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 1:59 PM
Points: 30, Visits: 134
Thanks both.

After I ran the checkDB with allow data loss, there were 100 rows are missing on REQ_ITEM table.
Since it was only affected single table, I was restore from last good backup and re-inserted missing data back to REQ_ITEM table.

Thanks again,
David.


Post #1394766

bubby
Posted Yesterday @ 4:27 PM

SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:25 PM
Points: 210, Visits: 288
Hello Help will be appreciated,

When I try to run repair_allow_data_loss in the particular database it says no stored procedure,

This is the result,

There are 41752 rows in 1155 pages for object "cfgWFLogs".
CHECKDB found 0 allocation errors and 69 consistency errors in table 'cfgWFLogs' (object ID 1810105489).
DBCC results for 'cfgInvLoadConflictPolicies'.
There are 12 rows in 1 pages for object "cfgInvLoadConflictPolicies".
DBCC results for 'cfgLCObjectHistory'.
There are 845 rows in 11 pages for object "cfgLCObjectHistory".
DBCC results for 'Ticket_Closure_Code'.
There are 39 rows in 1 pages for object "Ticket_Closure_Code".
DBCC results for 'System_Images'.
There are 1 rows in 1 pages for object "System_Images".
DBCC results for 'cfgInvLoadConflicts'.
There are 61 rows in 2 pages for object "cfgInvLoadConflicts".
DBCC results for 'Status'.
There are 163 rows in 3 pages for object "Status".
DBCC results for 'cfgSecurityRoleViews'.
There are 363 rows in 3 pages for object "cfgSecurityRoleViews".
DBCC results for 'Ticket_Medium'.
There are 5 rows in 1 pages for object "Ticket_Medium".
DBCC results for 'cfgLCEmails'.
There are 79 rows in 3 pages for object "cfgLCEmails".
DBCC results for 'cfgInvLoadConflictMembers'.
There are 61 rows in 1 pages for object "cfgInvLoadConflictMembers".
DBCC results for 'sys.ifts_comp_fragment_1675153013_779'.
There are 731 rows in 4 pages for object "sys.ifts_comp_fragment_1675153013_779".
DBCC results for 'Ticket_Priority'.
There are 16 rows in 1 pages for object "Ticket_Priority".
DBCC results for 'Brands'.
There are 668 rows in 8 pages for object "Brands".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'cfgSPID'.
There are 0 rows in 1 pages for object "cfgSPID".
DBCC results for 'cfgCustDepends'.
There are 85 rows in 1 pages for object "cfgCustDepends".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'SLA'.
There are 4 rows in 1 pages for object "SLA".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
DBCC results for 'Person_Logins'.
There are 914 rows in 8 pages for object "Person_Logins".
DBCC results for 'sys.filestream_tombstone_2073058421'.
There are 0 rows in 0 pages for object "sys.filestream_tombstone_2073058421".
DBCC results for 'PO'.
There are 67 rows in 8 pages for object "PO".
DBCC results for 'HTML_Preview'.
There are 23 rows in 1 pages for object "HTML_Preview".
DBCC results for 'sys.syscommittab'.
There are 0 rows in 0 pages for object "sys.syscommittab".
DBCC results for 'Work_Calendar_Items'.
There are 7 rows in 1 pages for object "Work_Calendar_Items".
DBCC results for 'Solution_Ratings'.
There are 116 rows in 2 pages for object "Solution_Ratings".
DBCC results for 'sys.ifts_comp_fragment_510624862_769'.
There are 683 rows in 4 pages for object "sys.ifts_comp_fragment_510624862_769".
DBCC results for 'Email_Types'.
There are 2 rows in 1 pages for object "Email_Types".
DBCC results for 'sys.ifts_comp_fragment_510624862_770'.
There are 19 rows in 1 pages for object "sys.ifts_comp_fragment_510624862_770".
DBCC results for 'cfgMCJobOptions'.
There are 9 rows in 1 pages for object "cfgMCJobOptions".
DBCC results for 'Work_Calendars'.
There are 3 rows in 1 pages for object "Work_Calendars".
CHECKDB found 0 allocation errors and 1464 consistency errors in database 'AlloyNav'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (AlloyNav).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Post #1543849
Posted Friday, February 21, 2014 12:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
Errr.... a copy-paste of an entire thread? Is there a problem/question here?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1543851
Posted Friday, February 21, 2014 5:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 10:30 AM
Points: 238, Visits: 317
Actually I have a corruption in one of the database, when I run DBCC CHECKTABLE I get this,

CHECKDB found 0 allocation errors and 1464 consistency errors in database 'Alloy'.

When I try to run repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (Alloy), I get a message with no stored procedure available for Repair,

I do not have any good backup, any idea please
Post #1543941
Posted Friday, February 21, 2014 9:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 10:25 PM
Points: 33,051, Visits: 15,160
try to bcp out the data from Alloy. If it doesn't work, try copying data out in ranges and see if you can get data before/after the corruption. Sometimes that works.

Call Microsoft CSS if you have doubts and get help. It's worth the cost. Or maybe it isn't if the data doesn't matter.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1544076
Posted Friday, February 21, 2014 9:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
Post the exact output of your attempt to repair


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1544078
Posted Friday, February 21, 2014 9:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 10:30 AM
Points: 238, Visits: 317
I backup the database, restored as different name, trying to Run the below statement before I try to run repair script, query is taking for ever to finish, any idea please,

ALTER DATABASE (db_name) SET EMERGENCY;
GO
ALTER DATABASE (db_name) SET SINGLE_USER;
Post #1544091
Posted Friday, February 21, 2014 11:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 10:30 AM
Points: 238, Visits: 317
any help I am willing to repair it with loosing data even
Post #1544133
Posted Friday, February 21, 2014 12:56 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:54 PM
Points: 42,434, Visits: 35,488
ALTER DATABASE (db_name) SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Otherwise it waits for existing connections to disconnect and it's willing to wait forever.
And unless the DB was suspect, you don't need or want emergency mode.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1544166
Posted Monday, February 24, 2014 8:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 10:30 AM
Points: 238, Visits: 317
Thanks, this query helped me to change it to single user mode,

Regards,

Bubby
Post #1544560
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse