DBCC CHECKDB Mirroring

Question

DBCC CHECKDB Mirroring

Chaitanya Kiran 841

We have mirroring configured. If I run DBCC CHECKDB command on principal database, will it get replicated to mirror database?

0 comments

Answer accepted by question author

Robbie Varn 351

No, it does not get replicated and this is because it (the database/s) are on a separate instance on a totally different storage system. You have to run it on the Secondary on the databases there. If the database is mirrored, you won't be able to connect to it to run it because it will be inaccessible. So, some people take snapshots and restore it, and then run it on the restored snapshot. Then, there are opinions that you only need to run dbcc checkdb on the Primary databases and that suffices. We have checkdb set up to run on the Primary and Secondary servers. That way, if the database(s) are failed over to the Secondary, you are sure to get dbcc checkdb run against the active database. I hope this helps.

Chaitanya Kiran 841 Reputation points

2022-02-18T18:00:13.337+00:00

Thanks for replying. If I shrink log file by running DBCC Shrinkfile command, will the mirror database's log file also shrink?
Robbie Varn 351 Reputation points

2022-02-18T18:28:03.613+00:00

Yes, if you shrink the log file on the Primary, the Secondary's log file should also be shrunk. You won't be able to see this if you tested this due to the secondary database being inaccessible.
Ronen Ariely 15,221 Reputation points

2022-02-19T05:12:03.58+00:00

Hi,

Before you start, check the following post about Why you should not shrink your data files

https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
Chaitanya Kiran 841 Reputation points

2022-03-05T00:55:08.667+00:00

Hi Robbie,

So, it doesnt get replicated in AlwaysOn AG and log shipping also? Am I correct?
Erland Sommarskog 134.6K Reputation points MVP Volunteer Moderator

2022-03-05T11:11:53.367+00:00

If you by "it" refers to DBCC CHECKDB, the question is not really meaningful. A plain DBCC CHECKDB is a read operation, and read operations are not replicated.

But if you throw in any of the REPAIR options it's a different matter. Now DBCC is performing writing work, and this work is, I assume, logged. But here things start to get interesting. The idea with AG mirroring and log shipping is that the databases are identical on page level. But when you have corruption, this does not have to be the case. So I will need to confess that I uncertain what happens here. Then again, the operations must be mirrored one way or another, not the least in the cases when REPAIR_ALLOW_DATA_LOSS decides to throw away data.
Chaitanya Kiran 841 Reputation points

2022-03-06T14:21:04.033+00:00

So,in simple English, you are saying that DBCC CHeCKDB does not get replicated in AG and log shipping also, correct?
Erland Sommarskog 134.6K Reputation points MVP Volunteer Moderator

2022-03-06T14:43:31.827+00:00

Yes. And the reason it is not replicated is that, as long as you are not using any REPAIR option, there is nothing to replicated, as it is a read operation.

1 additional answer

Your answer

Chaitanya Kiran 841 Reputation points

2022-02-18T18:00:13.337+00:00

Thanks for replying. If I shrink log file by running DBCC Shrinkfile command, will the mirror database's log file also shrink?
Robbie Varn 351 Reputation points

2022-02-18T18:28:03.613+00:00

Yes, if you shrink the log file on the Primary, the Secondary's log file should also be shrunk. You won't be able to see this if you tested this due to the secondary database being inaccessible.
Ronen Ariely 15,221 Reputation points

2022-02-19T05:12:03.58+00:00

Hi,

Before you start, check the following post about Why you should not shrink your data files

https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/
Chaitanya Kiran 841 Reputation points

2022-03-05T00:55:08.667+00:00

Hi Robbie,

So, it doesnt get replicated in AlwaysOn AG and log shipping also? Am I correct?
Erland Sommarskog 134.6K Reputation points MVP Volunteer Moderator

2022-03-05T11:11:53.367+00:00

If you by "it" refers to DBCC CHECKDB, the question is not really meaningful. A plain DBCC CHECKDB is a read operation, and read operations are not replicated.

But if you throw in any of the REPAIR options it's a different matter. Now DBCC is performing writing work, and this work is, I assume, logged. But here things start to get interesting. The idea with AG mirroring and log shipping is that the databases are identical on page level. But when you have corruption, this does not have to be the case. So I will need to confess that I uncertain what happens here. Then again, the operations must be mirrored one way or another, not the least in the cases when REPAIR_ALLOW_DATA_LOSS decides to throw away data.
Chaitanya Kiran 841 Reputation points

2022-03-06T14:21:04.033+00:00

So,in simple English, you are saying that DBCC CHeCKDB does not get replicated in AG and log shipping also, correct?
Erland Sommarskog 134.6K Reputation points MVP Volunteer Moderator

2022-03-06T14:43:31.827+00:00

Yes. And the reason it is not replicated is that, as long as you are not using any REPAIR option, there is nothing to replicated, as it is a read operation.

Answer 1

Ronen Ariely 15,221

Hi,

We have mirroring configured. If I run DBCC CHECKDB command on principal database, will it get replicated to mirror database?

No

The command is run per database

Mirroring is implemented on a per-database basis and based on transaction log. The mirroring is accomplished by sending a stream of active transaction log records to the mirror server. DBCC CHECKDB does not change the data and does not write transaction which should be mirror

https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server

Erland Sommarskog 134.6K Reputation points MVP Volunteer Moderator

2022-02-18T22:40:55.493+00:00

A point that follows from what Ronen and Robbie says, but which still is good to spell out. You can have corruption on the primary, but not have it on the secondary or vice versa.

Share via

DBCC CHECKDB Mirroring

1 additional answer

Your answer