Hi Mates,
Today we are going to see some interesting cases of database which is being corrupted and what will happen to it if it is involved in ALWAYS ON.
Note: For the below scenario I made use of Paul Randal's backup file where the corruption has been made.Also I restored the databases which are already corrupted and joined them to Always ON.
Case 1: Corruption at Non Clustered Index Level
Error Message after running checkdb:
CHECKDB found 0 allocation errors and 26 consistency errors in table 'Customers' (object ID 453576654).
CHECKDB found 0 allocation errors and 26 consistency errors in database 'DemoNCIndex'
So to overcome this I ran Alter index CustomerName on customers rebuild
however the error still persists. As it didn't work I went with repair_allow_data_loss.
I have seen so many people in interviews saying the database must be made emergency. Do you know when we should do this? Certainly not when the database is in ONLINE state. Hence my next steps are keeping the db in single_user and running repair.
Again this is not a normal database this is involved in ALWAYS ON so I need to remove it to perform repair otherwise you will get the below error message
Msg 1468, Level 16, State 1, Line 17
The operation cannot be performed on database "DemoNCIndex" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 17
ALTER DATABASE statement failed.
After running the repair my checkdb operation got succeeded. Now I am adding it back to ALWAYS ON. As expected now if you run checkdb it would get succeeded on the secondary server too as the logs from primary would get transferred.
If you have gone through Microsoft articles you might have come across Automatic page repair where the corruption at pages would get fixed automatically. Well what does it mean? In the above case it didn't come in to picture as I restored the corrupted backup file on 2 replicas.
To understand and reproduce it I have done the below steps
Instead of using corrupted database backups we our selves can introduce corruption using DBCC WRITEPAGE which should be used only for testing purpose. If you want to apply for next job then you can run this on your servers.
To use this we need to run dbcc ind('DemoNCIndex','customers',-1) where
DemoNCIndex is the database Name
customers is the table Name and -1 to view the page information for all the indexes.
Keep in Mind to run DBCC Writepage the database must be in single user mode so I removed my DB from ALWAYS ON and ran the below command
DBCC WRITEPAGE (N'DemoNCIndex', 1, 203, 4000, 1, 0x45, 1);
1 is the file id
203 is the page id of my non clustered index and for the rest of parameters you can make use of link.
once I made the corruption and added the database to ALWAYS ON my checkdb failed on primary for the first time. However after a few seconds there were no errors as the corrupted page was auto fixed from secondary replica where I didn't run DBCC WRITEPAGE.
Below is the result of MSDB..suspectpage results on my primary replica.
I hope this would make you understand how to create corruption and how automatic page repair would work.
Today we are going to see some interesting cases of database which is being corrupted and what will happen to it if it is involved in ALWAYS ON.
Note: For the below scenario I made use of Paul Randal's backup file where the corruption has been made.Also I restored the databases which are already corrupted and joined them to Always ON.
Case 1: Corruption at Non Clustered Index Level
Error Message after running checkdb:
CHECKDB found 0 allocation errors and 26 consistency errors in table 'Customers' (object ID 453576654).
CHECKDB found 0 allocation errors and 26 consistency errors in database 'DemoNCIndex'
So to overcome this I ran Alter index CustomerName on customers rebuild
however the error still persists. As it didn't work I went with repair_allow_data_loss.
I have seen so many people in interviews saying the database must be made emergency. Do you know when we should do this? Certainly not when the database is in ONLINE state. Hence my next steps are keeping the db in single_user and running repair.
Again this is not a normal database this is involved in ALWAYS ON so I need to remove it to perform repair otherwise you will get the below error message
Msg 1468, Level 16, State 1, Line 17
The operation cannot be performed on database "DemoNCIndex" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 17
ALTER DATABASE statement failed.
After running the repair my checkdb operation got succeeded. Now I am adding it back to ALWAYS ON. As expected now if you run checkdb it would get succeeded on the secondary server too as the logs from primary would get transferred.
If you have gone through Microsoft articles you might have come across Automatic page repair where the corruption at pages would get fixed automatically. Well what does it mean? In the above case it didn't come in to picture as I restored the corrupted backup file on 2 replicas.
To understand and reproduce it I have done the below steps
Instead of using corrupted database backups we our selves can introduce corruption using DBCC WRITEPAGE which should be used only for testing purpose. If you want to apply for next job then you can run this on your servers.
To use this we need to run dbcc ind('DemoNCIndex','customers',-1) where
DemoNCIndex is the database Name
customers is the table Name and -1 to view the page information for all the indexes.
Keep in Mind to run DBCC Writepage the database must be in single user mode so I removed my DB from ALWAYS ON and ran the below command
DBCC WRITEPAGE (N'DemoNCIndex', 1, 203, 4000, 1, 0x45, 1);
1 is the file id
203 is the page id of my non clustered index and for the rest of parameters you can make use of link.
once I made the corruption and added the database to ALWAYS ON my checkdb failed on primary for the first time. However after a few seconds there were no errors as the corrupted page was auto fixed from secondary replica where I didn't run DBCC WRITEPAGE.
Below is the result of MSDB..suspectpage results on my primary replica.
I hope this would make you understand how to create corruption and how automatic page repair would work.
Comments