November 15, 2011 at 1:03 pm
If you're willing to buy a book, the entire procedure is nearly laid out in SQL Server MVP Deep Dives 2 (http://www.manning.com/delaney), chapter 8. I do recommend you try the procedure out on a dev box first. It's not hard, but there are a few little things that can catch you out.
If a book isn't an option, I can summarise it, but not today.
As for auto repair, not sure why. Could be that the checkDB was the first indication that a page was damaged and it's been subsequently fixed without anyone realising. SQL has to notice the page is damaged before it can ask the mirror for a copy. Might be worth checking that it is still damaged. Or query the table so that you definitely get an 824 error, then see if it gets auto repaired.
The other possibility, which is terrifying, is that the backup used to create the mirror already had the damage in, so the mirror's page is also corrupt. That's not a nice thought
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2011 at 1:04 pm
SQLback (11/15/2011)
This is one of the messages when I ran CheckTablerepair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (TableName).
Since it is just 4 consistency errors I am hoping it will just delete 4 records ..
You probably have a lot more than 4 records on this one page that is corrupted.
What version of SQL are you on? I believe that 2008 Enterprise can do a page repair from the other server in the mirror, though I've never attempted it.
Edit: and I see that others have mentioned this. Time to join Craig watching how this gets fixed.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 15, 2011 at 1:23 pm
Thanks for the suggestions Gail.
The other possibility, which is terrifying, is that the backup used to create the mirror already had the damage in, so the mirror's page is also corrupt. That's not a nice thought
I think the mirror is fine. We run integrity checks on this database every week and the errors seem to appear just this week. We had a bad disk on this server that was replaced last week which I think could be the reason for the errors.
As for auto repair, not sure why. Could be that the checkDB was the first indication that a page was damaged and it's been subsequently fixed without anyone realising. SQL has to notice the page is damaged before it can ask the mirror for a copy. Might be worth checking that it is still damaged.
This is a pretty big table , so I am not sure if I can query it to get the error. I am going to run dbcc checktable on this table and see if it reports errors again.
November 15, 2011 at 1:51 pm
Can you snapshot the mirror db and run checkdb there to see if the same errors occur?
November 15, 2011 at 1:57 pm
SQLback (11/15/2011)
I saw the following message in the logs indicating DB Mirroring did try to fix the page. May be it can only fix certain types of pages.Database mirroring successfully repaired physical page (980:373889) in database "WebEntity" by obtaining a copy from the partner.
Page 980:373889????? Eeep!!!!!
This DB doesn't have 980 files does it? Assuming it doesn't, please run a full database consistency check as soon as possible and I'll defer any further discussions of fixing it until that's complete.
DBCC CHECKDB (<Database Name>) WITH NO_INFOMSGS, ALL_ERRORMSGS
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2011 at 2:22 pm
It does have 980 files. Hence we run CheckDB on tables periodcially over the week.
As for auto repair, not sure why. Could be that the checkDB was the first indication that a page was damaged and it's been subsequently fixed without anyone realising. SQL has to notice the page is damaged before it can ask the mirror for a copy. Might be worth checking that it is still damaged. Or query the table so that you definitely get an 824 error, then see if it gets auto repaired.
You were right. Auto repair did fix it. I just ran DBCC CheckTable on the tables with the issue and it didnt report any errors this time.
It was confusing because in the log it said there were errors right after a message that the page has been repaired. Also, the output of the DBCC didnt report the page has been fixed. I guess the auto repair and DBCC are two independent processes.
Thanks for your help Gail. it saved us a lot of time and effort.
November 15, 2011 at 2:57 pm
SQLback (11/15/2011)
It was confusing because in the log it said there were errors right after a message that the page has been repaired.
The message in the log wasn't that the page had been repaired. It said 'requesting page from mirror'. Hence it's starting a repair. No guarantee how long before it's finished. The mirror won't necessarily sent the page immediately.
Also, the output of the DBCC didnt report the page has been fixed. I guess the auto repair and DBCC are two independent processes.
Yes. The auto repair is part of mirroring. CheckTable was just the catalyst to find that the page was bad
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2011 at 2:59 pm
SQLback (11/15/2011)
It does have 980 files. Hence we run CheckDB on tables periodcially over the week.
Btw, you do also run CheckAlloc an CheckCatalog along with CheckTable don't you?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 15, 2011 at 3:22 pm
The message in the log wasn't that the page had been repaired. It said 'requesting page from mirror'. Hence it's starting a repair. No guarantee how long before it's finished. The mirror won't necessarily sent the page immediately.
Doesnt this message indicate that page has been repaired ?
Database mirroring successfully repaired physical page (980:373889) in database "WebEntity" by obtaining a copy from the partner.
November 15, 2011 at 3:25 pm
Btw, you do also run CheckAlloc an CheckCatalog along with CheckTable don't you?
Thanks for bringing that up because I dont see that in our process. I am going to check with my Sr DBA about this. The whole reason we went with CheckTable was because CheckDB was timing out on this database. I am hoping that CheckAlloc and CheckCatalog wont cause time outs.
November 15, 2011 at 3:54 pm
Evil Kraig F (11/15/2011)
^ Gail is our recovery and restoration god, with the ears of the people who built the system when she's not sure. Trust her, she's your best bet to get yourself out of this without making the problem worse.I'm going to hang out in the corner and watch. 😎
Godess is a little more accurate :hehe:.
November 15, 2011 at 3:54 pm
Ninja's_RGR'us (11/15/2011)
Called in help.
Note to self, it's possible to call for too much help. 😀
Thanks yall.
November 16, 2011 at 12:12 am
SQLback (11/15/2011)
Thanks for bringing that up because I dont see that in our process. I am going to check with my Sr DBA about this. The whole reason we went with CheckTable was because CheckDB was timing out on this database. I am hoping that CheckAlloc and CheckCatalog wont cause time outs.
You need to run them, no matter what.
CheckDB is a combination of CheckAlloc, CheckCatalog and CheckTable on all tables. You can split CheckDB up, but then you need to run all the components.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply