Post reply

Can't Restore DB Because of Active Connections

  • August 28, 2012 at 3:09 pm

    #260709

    I have a DB I would like to restore. There are other active connections on the DB. I tried using sp_who2 to find those connections and manually kill them. Unfortunately, this did not solve the problem. I am now running the following code to set the DB to single user mode

    alter database mydb

    set single_user

    with rollback immediate

    This code runs for several hours and does not complete. Not sure if something is holding this command up. Is there another way to restore my DB?

  • August 28, 2012 at 3:17 pm

    #1531112

    you want to make sure that YOU are in the database, so that when it is set to single user, YOU are the single user. otherwise, like if you rant that form master, some other process could become the sole connection that can connect.

    USE mydb;

    Go

    alter database mydb

    set single_user

    with rollback immediate

    RESTORE database mydb ....

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 28, 2012 at 3:34 pm

    #1531119

    i tried adding the use statement. It looks like the same problem as before. The script runs endlessly on

  • August 28, 2012 at 4:39 pm

    #1531138

    guerillaunit (8/28/2012)

    i tried adding the use statement. It looks like the same problem as before. The script runs endlessly on

    Check to see if there are any other processes in RollBack while this is going on.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 28, 2012 at 8:35 pm

    #1531185

    Try to find all connections to your database:

    select d.name,

    p.*

    from sys.databases d join sys.sysprocesses p

    on d.database_id = p.dbid

    where spid >50

    and d.name = 'MyDb'

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

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