What to Do When Distribution Database is Corrupt in SQL Server

dbcc-checkdbreplicationsql server

The below error message has been received from one of our SQL Server databases:

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x6847f47b; actual: 0x15160a19). It occurred during a read of page (1:934295) in database ID 5 at offset 0x000001c832e000 in file 'C:\XYZ.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.

Obviously I have to run DBCC CHECKDB on the corrupt database. Before doing that, I tried to find out what the database name was (using database id) and what object the error was related to, using and the page number that was given in the error message.

So I understood that the database name was distribution and the table name was MSrepl_errors.

I'm not much familiar with SQL Server replication features, so would appreciate if someone can please help me with the below questions:

  • In order to investigate and/or possibly fix the issue, I have to first execute DBCC CHECKDB; however, CHECKDB cannot be run without first putting the database in single user mode, is this correct?

  • Regarding the fact that the distribution database is not used by end users, do I still have to put it in the single user mode? And, if yes, what will be the impact on the replication jobs?

Best Answer

However, CHECKDB cannot be run without first putting the database in single user mode. Is this correct?

It shouldn't need to be in single user in order to run checkdb. You can run DBCC CHECKDB('distribution') WITH NO_INFOMSGS, ALL_ERRORMSGS to check it.

Since we don't know where the current issue is, it's hard to say what/if any impact to the current topology would be. We'd really need to know more, feel free to update the question with the output from CHECKDB.

Please do note that system databases, which the distribution database is considered, are not supported to have REPAIR_ALLOW_DATA_LOSS run on them. In fact, it should only be run in the most devastating of situations and under duress. Choosing to do this to a system database will immediately become an unsupported configuration.

Related Question