SQL Server Performance

Corrupt Distribution Database

Discussion in 'SQL Server 2005 Replication' started by melvinlusk, Dec 30, 2008.

  1. melvinlusk Member

    I recently tried upgrading our production SQL 2005 servers to SQL 2005 service pack 2. The environment has 6 servers setup with transactional replication:1) a two-node active/passive cluster (publisher)2) a two-node active/passive reporting cluster (subscriber)3) a two-node active/passive distribution cluster (distributor)
    I first tried to install SP2 on the active node of our distribution cluster, and the service pack install failed because it detected corruption in the Distribution database. I ran DBCC CHECKDB to find the cause of corruption, but after running for a few minutes it returns this error:Msg 8967, Level 16, State 216, Line 1An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.DBCC results for 'distribution'.DBCC results for 'sys.sysrowsetcolumns'.There are 1239 rows in 10 pages for object "sys.sysrowsetcolumns".DBCC results for 'sys.sysrowsets'.There are 170 rows in 1 pages for object "sys.sysrowsets".DBCC results for 'sysallocunits'.There are 189 rows in 2 pages for object "sysallocunits".DBCC results for 'sys.sysfiles1'.There are 2 rows in 1 pages for object "sys.sysfiles1".DBCC results for 'sys.syshobtcolumns'.There are 1239 rows in 10 pages for object "sys.syshobtcolumns".DBCC results for 'sys.syshobts'.There are 170 rows in 1 pages for object "sys.syshobts".DBCC results for 'sys.sysftinds'.There are 0 rows in 0 pages for object "sys.sysftinds".DBCC results for 'sys.sysserefs'.There are 189 rows in 1 pages for object "sys.sysserefs".DBCC results for 'sys.sysowners'.There are 15 rows in 1 pages for object "sys.sysowners".DBCC results for 'sys.sysprivs'.There are 122 rows in 1 pages for object "sys.sysprivs".DBCC results for 'sys.sysschobjs'.There are 208 rows in 6 pages for object "sys.sysschobjs".DBCC results for 'sys.syscolpars'.There are 1470 rows in 26 pages for object "sys.syscolpars".DBCC results for 'sys.sysnsobjs'.There are 1 rows in 1 pages for object "sys.sysnsobjs".DBCC results for 'sys.syscerts'.There are 0 rows in 0 pages for object "sys.syscerts".DBCC results for 'sys.sysxprops'.There are 1 rows in 1 pages for object "sys.sysxprops".DBCC results for 'sys.sysscalartypes'.There are 27 rows in 1 pages for object "sys.sysscalartypes".DBCC results for 'sys.systypedsubobjs'.There are 0 rows in 0 pages for object "sys.systypedsubobjs".DBCC results for 'sys.sysidxstats'.There are 375 rows in 8 pages for object "sys.sysidxstats".DBCC results for 'sys.sysiscols'.There are 568 rows in 4 pages for object "sys.sysiscols".DBCC results for 'sys.sysbinobjs'.There are 23 rows in 1 pages for object "sys.sysbinobjs".DBCC results for 'sys.sysobjvalues'.There are 474 rows in 94 pages for object "sys.sysobjvalues".DBCC results for 'sys.sysclsobjs'.There are 15 rows in 1 pages for object "sys.sysclsobjs".DBCC results for 'sys.sysrowsetrefs'.There are 0 rows in 0 pages for object "sys.sysrowsetrefs".DBCC results for 'sys.sysremsvcbinds'.There are 0 rows in 0 pages for object "sys.sysremsvcbinds".DBCC results for 'sys.sysxmitqueue'.There are 0 rows in 0 pages for object "sys.sysxmitqueue".DBCC results for 'sys.sysrts'.There are 1 rows in 1 pages for object "sys.sysrts".DBCC results for 'sys.sysconvgroup'.There are 0 rows in 0 pages for object "sys.sysconvgroup".DBCC results for 'sys.sysdesend'.There are 0 rows in 0 pages for object "sys.sysdesend".DBCC results for 'sys.sysdercv'.There are 0 rows in 0 pages for object "sys.sysdercv".DBCC results for 'sys.syssingleobjrefs'.There are 138 rows in 1 pages for object "sys.syssingleobjrefs".DBCC results for 'sys.sysmultiobjrefs'.There are 452 rows in 3 pages for object "sys.sysmultiobjrefs".DBCC results for 'sys.sysdbfiles'.There are 2 rows in 1 pages for object "sys.sysdbfiles".DBCC results for 'sys.sysguidrefs'.There are 0 rows in 0 pages for object "sys.sysguidrefs".DBCC results for 'sys.sysqnames'.There are 91 rows in 1 pages for object "sys.sysqnames".DBCC results for 'sys.sysxmlcomponent'.There are 93 rows in 1 pages for object "sys.sysxmlcomponent".DBCC results for 'sys.sysxmlfacet'.There are 97 rows in 1 pages for object "sys.sysxmlfacet".DBCC results for 'sys.sysxmlplacement'.There are 17 rows in 1 pages for object "sys.sysxmlplacement".DBCC results for 'sys.sysobjkeycrypts'.There are 0 rows in 0 pages for object "sys.sysobjkeycrypts".DBCC results for 'sys.sysasymkeys'.There are 0 rows in 0 pages for object "sys.sysasymkeys".DBCC results for 'sys.syssqlguides'.There are 0 rows in 0 pages for object "sys.syssqlguides".DBCC results for 'sys.sysbinsubobjs'.There are 0 rows in 0 pages for object "sys.sysbinsubobjs".DBCC results for 'MStracer_history'.There are 0 rows in 1 pages for object "MStracer_history".DBCC results for 'MSreplication_monitordata'.There are 57 rows in 3 pages for object "MSreplication_monitordata".DBCC results for 'MSarticles'.There are 11857 rows in 267 pages for object "MSarticles".DBCC results for 'MSsync_states'.There are 0 rows in 1 pages for object "MSsync_states".DBCC results for 'MSsubscriptions'.There are 27872 rows in 568 pages for object "MSsubscriptions".DBCC results for 'MSmerge_subscriptions'.There are 0 rows in 0 pages for object "MSmerge_subscriptions".DBCC results for 'MSrepl_transactions'.There are 48654205 rows in 296572 pages for object "MSrepl_transactions".CHECKDB found 0 allocation errors and 0 consistency errors in database 'distribution'.
    I find it unusual that it throws the “internal error” but still list 0 allocation and consistency errors. I also find the number of rows (48654205) in MSrepl_transactions to be quite high, as well as the overall DB size (120 GB).
    I’m somewhat of a rookie when it comes to replication. I’d like to be able to fix the corruption before having to rebuild our replication setup from scratch.
    I did try to run the DBCC CHECKDB on Distribution while in single-user mode, and it didn’t throw the error, but I ran out of time to complete it during our maintenance window.
    Any ideas on where I should start?
  2. ndinakar Member

    Check if your subscriber is up to date with publisher.. I havent tried this but perhaps you can stop the agents,install the service packs (and do the reboots) and let the agents catch up...
  3. melvinlusk Member

    Everything is up to date.
    Unfortunately I will have to wait a few weeks until my next maintenance window to try this again.
    I didn't have to stop replication when installing the service pack in my testing environment, which is setup almost identical to production (with the exception of clusters).
  4. satya Moderator

    I suggest to correct these DBCC inconsistency on the table where it is reported or stop replication recreate the table and use DTS or SSIS to handle the data back to newly created table.
  5. melvinlusk Member

    For what it's worth, I think I figured this out. The Distribution Clean Up job was setup to run under the previous DBA's Active Directory account. When he left, his account was disabled, so the job has been failing to run. This would explain why the Distribution database is 130GB+.
    I kicked the job off after changing the job owner, I'm sure it will take awhile to run. I'm hoping this will fix the corruption issues, or at least get the database down to a manageable size.
  6. melvinlusk Member

    Just so everyone has closure......
    I ended up having to rebuild the replication setup from scratch. Since this allowed me to start with a fresh Distribution DB, I was able to install SP2 without incident.
    Thanks for everyone's help. After doing this several times I'm quite a bit more familiar with replication now [:)]

Share This Page