Single User Mode in Mirrored Database

  • 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.

  • 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.

  • Why do you want to lose data (repair mostly deletes stuff!)?

    What does this return?

    DBCC CHECKDB ('dbName') WITH ALL_ERRORMSGS, NO_INFOMSGS

  • 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.

  • 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.

  • 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?

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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 ?

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ^ 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. 😎


    - Craig Farrell

    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

  • 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

  • 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

  • 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