November 15, 2011 at 11:20 am
When I tried to set a database which is mirrored to single user mode using the following statement
ALTER DATABASE [DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
I get the following message
The operation cannot be performed on database "DBName" because it is involved in a database mirroring session.
Can a database involved in mirroring be set to single user mode ? I am trying to set it to single user mode because I want to run DBCC CheckTable with REPAIR_ALLOW_DATA_LOSS option on a table with consistency errors.
November 15, 2011 at 11:25 am
I even tried to pause the mirror and that didnt work. I am trying to find another way to do that without breaking the mirror. This is a huge database (3 TB) and it will take some time to set it mirror again.
November 15, 2011 at 11:26 am
Why do you want to lose data (repair mostly deletes stuff!)?
What does this return?
DBCC CHECKDB ('dbName') WITH ALL_ERRORMSGS, NO_INFOMSGS
November 15, 2011 at 11:30 am
This is one of the messages when I ran CheckTable
repair_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 ..
Here is the full list of messages
Changed database context to 'WebEntity'.
DBCC results for 'adserver.prod_daily'.
Msg 8928, Level 16, State 1, Server CSIADQAT12, Line 1
Object ID 1687677060, index ID 1, partition ID 72057595042594816, alloc unit ID 72057595054063616 (type In-row data): Page (158:2723) could not be processed. See other errors for details.
Msg 8939, Level 16, State 98, Server CSIADQAT12, Line 1
Table error: Object ID 1687677060, index ID 1, partition ID 72057595042594816, alloc unit ID 72057595054063616 (type In-row data), page (158:2723). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 12716041 and -4.
Msg 8976, Level 16, State 1, Server CSIADQAT12, Line 1
Table error: Object ID 1687677060, index ID 1, partition ID 72057595042594816, alloc unit ID 72057595054063616 (type In-row data). Page (158:2723) was not seen in the scan although its parent (158:2751) and previous (158:2722) refer to it. Check any previ
ous errors.
Msg 8978, Level 16, State 1, Server CSIADQAT12, Line 1
Table error: Object ID 1687677060, index ID 1, partition ID 72057595042594816, alloc unit ID 72057595054063616 (type In-row data). Page (158:2724) is missing a reference from previous page (158:2723). Possible chain linkage problem.
There are 142081074 rows in 428199 pages for object "adserver.prod_daily".
CHECKTABLE found 0 allocation errors and 4 consistency errors in table 'adserver.prod_daily' (object ID 1687677060).
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (WebEntity.adserver.prod_daily).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
November 15, 2011 at 11:33 am
Called in help.
Repair garantees you'll lose data.
Do you have access to a full backup and all the tlogs backups?
This could be repairable without data loss that way.
November 15, 2011 at 11:34 am
First, please debug the consistency errors, and potentially call MS bfore you run this. If it cannot fix linkages, it will drop pages and remove data. Not what you want to do, necessarily. You might end up with the need to restore.
Second, don't have a mirror handy, but can you use RESTRICTED_USER instead?
November 15, 2011 at 11:36 am
Thanks Ninja. We are hoping that since it is 4 consistency errors it will not affect a lot of records. This is a huge database (around 3 TB) so we decided to go with repair data loss option than restore from a backup.
November 15, 2011 at 11:51 am
If you have full and log backups you can do page restores. If you've got Enterprise edition it'll be online with the DB completely accessible the entire time.
One page is damaged. That's all.
p.s. Don't call MS. All they'll tell you is to restore from backup.
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 11:52 am
Steve Jones - SSC Editor (11/15/2011)
First, please debug the consistency errors, and potentially call MS bfore you run this. If it cannot fix linkages, it will drop pages and remove data. Not what you want to do, necessarily. You might end up with the need to restore.Second, don't have a mirror handy, but can you use RESTRICTED_USER instead?
It's not the linkages that are bad here. A single page is toast. Single user is required for CheckDB, restricted_user is not restricted enough.
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 12:04 pm
Second, don't have a mirror handy, but can you use RESTRICTED_USER instead?
We cannot use RESTRICTED_USER , database has to be in single user mode to run dbcc checkdb with REPAIR_ALLOW_DATA_LOSS.
Thanks all for your suggestions.We will definitely consider restoring from the backup.
If our product team is ok with minimal loss (one page as Gila pointed out ) does anyone know if you can set a database to single user mode (to run dbcc check db with REPAIR_ALLOW_DATA_LOSS) on a database involved in a mirror ?
November 15, 2011 at 12:06 pm
You cannot. You'd have to break the mirror, set single user, repair and then recreate the mirror from scratch.
That's why I'm suggesting page restores.
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 12:11 pm
^ 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. 😎
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2011 at 12:47 pm
Thanks Gail. We do have Enterprise Edition so Page Restore does make sense. We have never done this in our company. Can you please share any good resources that talk about this situation.
Thanks,
Reddy
November 15, 2011 at 12:56 pm
I am surprised that an automatic page repair wasn't performed, since this database is mirrored.
http://msdn.microsoft.com/en-us/library/bb677167.aspx
Gail, any idea on why this wouldn't have fixed the problem already?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 15, 2011 at 1:01 pm
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.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply