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

Dealing with a database corruption on a merge replication subscriber Expand / Collapse
Author
Message
Posted Monday, July 30, 2012 1:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:20 AM
Points: 124, Visits: 153
Merge replication is being used to provide inter-site high availability for a 7*24 database with 99.999% availability requirements (we have had no downtime in 2 years). In the event that our primary data centre encounters a major outage we failover to our secondary data centre where the subscriber becomes our primary database server, with any changes replicated to the publisher at the primary data centre when the outage is resolved.

As a result of an error identified by Symantec BackupExec, a DBCC CHECKDB was run on the subscriber databases. It revealed a data corruption with a minimum repair level of REPAIR_ALLOW_DATA_LOSS. The table in question, including indexes, is approximately 6GB and is an article in a publication comprising a number of larger tables.

We have a full Backup Exec database backup prior to the corruption (which occurred 4 days ago!). As the corruption only affects a single page (comprising 37 rows) we could consider recovering the page but it is far from obvious how to achieve that with Backup Exec.

As well as the potential for data loss and referential implications with REPAIR_ALLOW_DATA_LOSS, it does not seem to be viable given the issues concerning replication documented here: http://msdn.microsoft.com/en-us/library/ms176064(v=sql.100).aspx: merge replication may not replicate the changes as triggers will not be executed as a result of changes due to CHECKDB; we would have to quiesce replication and stop all at activity at the publisher, which is not feasible given the implications for our service.

We have considered removing the table from the publisher publication; dropping the table at the subscriber, creating a new publication with the table at the publisher and relying on replication to refresh the secondary but this could leave the corrupt page at the subscriber.

So, the question is, how can we resolve the corruption with minimal disruption at the publisher and minimal impact on our high availability architecture.
Post #1337502
Posted Tuesday, July 31, 2012 7:48 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 20, 2014 1:29 PM
Points: 1,593, Visits: 1,489
What about SQL Server backups? Symantec BackupExec is not a replacement for creating SQL Server backups. In order to do a page restore, you will need regulr backups including all log backups since the page you restore.

What exactly is corrupted? Can you post the error message including the pages that are corrupted? It is important to know what kind of page it is first. Not all pages can be repaired or recovered.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1337902
Posted Tuesday, July 31, 2012 7:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:20 AM
Points: 124, Visits: 153
We are using the backup agent for SQL Server with Backup Exec which provides for SQL backup/recover, including transaction logs. However, it does not appear to support page level restore.

The DBCC CHECKDB showed the following:

DBCC results for '<table name>'.
Msg 8928, Level 16, State 1, Line 1
Object ID 482100758, index ID 0, partition ID 72057594045202432, alloc unit ID 72057594047168512 (type In-row data): Page (1:1600834) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 482100758, index ID 0, partition ID 72057594045202432, alloc unit ID 72057594047168512 (type In-row data), page (1:1600834). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 29493257 and -4.
There are 7464070 rows in 192799 pages for object "tblAuthServerLogsLive".
CHECKDB found 0 allocation errors and 2 consistency errors in table 'tblAuthServerLogsLive' (object ID 482100758).

DBCC TRACEON (3604, -1)
GO
DBCC PAGE('database', 1, <page>, 3)
GO

shows that it is the data pages that are corrupt: "Metadata: IndexId = 0"

PAGE HEADER:

Page @0x000000009E6A8000

m_pageId = (1:1600834) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x4 m_level = 0 m_flagBits = 0x2208
m_objId (AllocUnitId.idObj) = 141 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594047168512
Metadata: PartitionId = 72057594045202432 Metadata: IndexId = 0
Metadata: ObjectId = 482100758 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 46 m_slotCnt = 73 m_freeCnt = 170
m_freeData = 7876 m_reservedCnt = 0 m_lsn = (10675:655871:253)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = -1425631195
Post #1337909
Posted Tuesday, July 31, 2012 8:16 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 20, 2014 1:29 PM
Points: 1,593, Visits: 1,489
If you can't do a page level restore, that leaves you with 2 options. You can restore the whole database and renitialize replication to it. Or you try running DBCC CheckDB with repair and then manually syncing the data that is missing.

Running repair will delete the bad page and fix up any links to it. So you will need to re-insert any records on that page.




My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1337921
Posted Tuesday, July 31, 2012 10:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:20 AM
Points: 124, Visits: 153
Thanks again. Given the impact of reinitialising the replication we are proposing to run the repair and then use sp_addtabletocontents to push the missing data from the publisher to the subscriber.
Post #1338030
Posted Thursday, August 02, 2012 8:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:20 AM
Points: 124, Visits: 153
Resolved the corruption as follows:

Stop the merge agent jobs for the publications:
Set database to single user mode: ALTER DATABASE database SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Ran DBCC CHECKDB to repair the corrupt page: DBCC CHECK DB (‘database’, REPAIR_ALLOW_DATA_LOSS)
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS
Set database to multi-user: ALTER DATABASE database SET MULTI_USER; GO
Restarted the merge agent jobs
Confirmed replication running correctly in Replication Monitor
Used sp_addtabletocontents with a filter to repopulate the data that had been lost due to the repair
Ran DBCC CHECKDB to confirm that the database now in a good state
Post #1339215
Posted Thursday, August 02, 2012 9:20 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 20, 2014 1:29 PM
Points: 1,593, Visits: 1,489
Well done!



My blog: SQL Soldier
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #1339279
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse