0

Is it necessary to do a database integrity check (DBCC check) on mirrored databases?

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/automatic-page-repair-availability-groups-database-mirroring?view=sql-server-2017

Automatic page repair is supported by database mirroring and by Always On availability groups. After certain types of errors corrupt a page, making it unreadable, a database mirroring partner (principal or mirror) or an availability replica (primary or secondary) attempts to automatically recover the page.

What is you best practice?

  • Mirrored databases are in restoring state so you wouldn't be able to run dbcc checkdb on them even if you wanted that. – Denis Rubashkin Jul 23 at 11:43
  • Yes, right, but I mean principal database (which is mirrored) , not a mirror one. I am also not sure if there is any risk if mirroring does not repair pages in some cases (File header page (page ID 0), Page 9 (the database boot page)... – TheLast10sat Jul 23 at 12:06
1

No, that's one of the main benefits of mirroring is the page repair; it checks for corruption and then tries to address it if it finds any.

1

Q1

Is it necessary to do a database integrity check (DBCC check) on mirrored databases?

Comment reply by Denis Rubashkin

Mirrored databases are in restoring state so you wouldn't be able to run dbcc checkdb on them even if you wanted that.

Q2

Yes, right, but I mean principal database (which is mirrored) , not a mirror one

Yes, you should.


A few of many more reasons as to why

What if a page that is rarely used gets corrupted?

When do you want to find that out?

Consider a scenario when a disk suddenly became unresponsive when SQL Server was writing into it. This will obviously cause the data to become corrupt; however, this corruption will be detected only when we try to access the pages that had become corrupt; and this can be months after the corruption was actually introduced. Similarly, a bit flip while writing data to the disk subsystem, caused by a disk driver, can cause corruption, and this might be actually detected at a much later point in time.

Kernel Drivers and softwares like Antivirus, that have the ability to take Kernel Level Handle on files, are also known to cause database corruption issues. In case you have Antivirus Installed on the system, please ensure that you exclude the SQL Server Database Files from Antivirus scans.

Source

In short

You should always run integrity checks on your (principal) database(s). If the database is too big the next best thing is offloading the dbcc checkdb to a restored backup. More on that here

Corruption can still occur due to hardware failure, an application such as antivirus getting access to the pages and causing corruption, ....

I am also not sure if there is any risk if mirroring does not repair pages in some cases

Another reason why you should be doing integrity checks.

You could use ola hallengren's solution for running integrity checks.

0

Great reply from Randi Vertongen. So, my understanding about DBCC - It does majorly CHECKALLOC, CHECKCATALOG and CHECKTABLE. For a VLDB, if i backup and restore the DB to another system it could be success. However, i'm supposed to run DBCC on the production DB which also checks underlying storage? Pl Correct me, i'm thinking DBCC also does some checks on the allocated storage too along with validating integrity of GAM,SGAM,IAM pages. Or it does not perform any checks on the storage? My Q is, if i restore the DB to a non-prod server and DBCC it may run without any issues but my production system may have issues?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.