Post reply

The database DbName is not accessible

  • November 15, 2012 at 1:40 pm

    #270308

    From SSMS, I right clicked on a db and selected Tasks, Take Offline. A dialog poped up and I clicked close. Now when I try to access the db through SSMS, I get the message 'The database dbName is not accessible (ObjectExplorer)'.

    In the SQL Server log, I see the message: Setting the database option OFFLINE to ON for database DbName at the time when I right clicked to take offline, but no other error messages.

    It's acting as if it couldn't complete the steps to take the db offline.

    What should I do next?

    Thanks,

    Rob

    Edit: One other thing, the option to Bring Online from SSMS is greyed out as well.

  • November 15, 2012 at 1:47 pm

    #1559518

    Okay, SSMS has come back and I can access that database -- but I never got a message saying it couldn't take the database offline. Hmm. Is a better option to do something like:

    ALTER DATABASE [DbName] SET OFFLINE WITH

    ROLLBACK AFTER 30 SECONDS

    Hmmmm,

    Rob

  • November 15, 2012 at 1:57 pm

    #1559524

    I usually say WITH ROLLBACK IMMEDIATE, that terminates connections and starts the rolback straight way. Depends how important what's currently running against the DB is.

    Still, that only starts the rollback, so if there's a long running transaction it could take quite some time to complete going offline.

    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

  • November 15, 2012 at 1:59 pm

    #1559526

    Thanks Gail; that did the trick (rather than the SSMS interface).

    ALTER DATABASE DbName

    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

    and then

    ALTER DATABASE DbName

    SET MULTI_USER

    GO

    Thanks,

    Rob

Viewing 4 posts - 1 through 3 (of 3 total)

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