Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    101

    read-only and Warm standby status

    Hi,
    I have one database with read-only status. I tried to change it back to normal by doing this..
    EXEC sp_dboption 'test', 'read only', 'FALSE' by doing this i'm getting this
    Changes to the state or options of database 'test' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
    And when i tried on Enterprise Manager it is saying
    Error 5063 database 'test' is in warm Standby.
    I cross checked and confirmed that it is not in single-user mode and no one is connected to the database!!
    Any help is greately appreciated.
    thanks
    ss

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311
    Try the following script in Master to confirm there's no connections first.

    select * from sysprocesses where db_name(dbid) = 'test'

    If there is, kill it.

  3. #3
    Join Date
    Apr 2004
    Posts
    101
    I tried that one, there were no connections!!!
    ss

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Restore database 'yourdb' with RECOVERY

  6. #6
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Run this first:

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    /****** Object: Stored Procedure dbo.uspKillDBConnections Script Date: 5/4/2004 9:38:26 PM ******/
    ALTER proc uspKillDBConnections -- uspKillDBConnections 'pubs'
    (
    @dbName sysname
    )
    as
    declare @sqlStatement varchar(255)
    declare @spid int
    declare curUsers cursor for
    select spid
    from sysprocesses
    where dbid = db_id(@dbName)
    while (select count(*) from sysprocesses where dbid = db_id(@dbName)) > 0
    begin
    waitfor delay '00:00:03'
    open curUsers

    fetch next from curUsers into @spid

    while (@@fetch_status <> -1)
    begin
    select @sqlStatement = 'kill ' + cast(@spid as varchar)
    exec(@sqlStatement)

    fetch next from curUsers into @spid
    end

    close curUsers
    end
    deallocate curUsers

    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO



    Then run your EXEC sp_dboption 'test', 'read only', 'FALSE' statement.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •