Post reply

URGENT - In Single User can not run REPAIR

  • June 25, 2004 at 9:45 pm

    #102080

    I'm the DBA by default getting errors that request a repair be ran.  I went to the Database properties and put it in single user mode.  I went to query analyzer and tried to run:  DBCC CHECKDB('DB1', 'REPAIR_REBUILD' ) ... Now it tells me:  Server: Msg 924, Level 14, State 1, Line 1

    Database 'DB1' is already open and can only have one user at a time. 

    What am I doing wrong.    Please help.

    Anguishing in Ohio : <

     

  • June 26, 2004 at 2:58 am

    #512073

    Try this :

    ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    This should put you in single mode, provided that you have sysadmin access. Please keep in mind this will only work in sql server 2000. In sql server 7.0 you will have to manually kill all the processes on your database using

    KILL

    Hope this helps you.

    Regards.

  • June 27, 2004 at 10:37 am

    #512097

    Three Questions before I take the dive in the deep end of hte pool:

    1.  In the an Analyzer session when I do what you recommend do I immediately run this in the same session:

    DBCC CHECKDB('DB1', 'REPAIR_REBUILD' )

    2.  All the errors are 'Table error: Object ID 1183343280, Index ID 37. Keys out of order on page' on the same table ... all index.  Can I do a Reindex to fix somehow on just the table with issues?  What would the command line syntax be?

    3.  Once I run 'ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE' and my REPAI_REBUILD or REINDEX, can I take it out of single user mode inproperties for the Database or should I run another type of ALTER command?

    Thank you so much for the help this weekend ... I need to have the problem fixed by monday. 

  • June 27, 2004 at 10:45 am

    #512098

    ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    ERRORS when trying to run alter in SQL Query Analyzer session:===> Server: Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'SET'.

    Server: Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near 'IMMEDIATE'.

     

    Any ideas what I am doing wrong?  The next command I think I want to run is:  DBCC CHECKTABLE('TABLE_WITH_ISSUE',REPAIR_REBUILD) ... once I can get into single user mode gracefully.

    Thanks again for her help this weekend!  Very nice of you.

  • June 28, 2004 at 7:54 am

    #512177

    Missed something small but important, the database name.

    ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    Also, don't forget to separate the commands with a "GO" if you are trying to run them all at once.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • June 28, 2004 at 8:44 am

    #512205

    Got It Running!  Thanks

  • June 28, 2004 at 10:33 am

    #512229

    I'm guessing, but do I go back into multi user mode with:

    ALTER DATABASE DBase SET MULTI_USER

     

    Do I need 'WITH ROLLBACK IMMEDIATE ' or any other clause?

  • June 28, 2004 at 10:51 am

    #512232

    No. Just go multi-user.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply