Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    39

    Unanswered: Trying to get exclusive access to a DB for restore.

    I have created a SQL Agent job that is supposed to essentially duplicate a production database to another database. The code I am using is:

    step1
    __________________________________________________ ______
    declare @sql varchar(8000)
    set @sql=''
    select @sql=@sql+'kill '+cast(spid as varchar)+char(13)+char(10)
    from sysprocesses where dbid=12

    --Print (@SQL)
    exec(@sql)

    step2
    __________________________________________________ ________

    RESTORE DATABASE HIWDYNARPT FROM PRDBACKUP
    WITH REPLACE
    __________________________________________________ ______

    This works when I test it during the day, however when it runs at night I get the following error in the job log:

    Database in use. The system administrator must have exclusive use of the database to run the restore operation. [SQLSTATE 42000] (Error 3101) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

    I'm not sure why this happens because I have killed all open threads in step 1, and then create my own new thread in step two. Maybe someone else is initiating a new thread to quickly between the steps???

    Anyway, I am trying to use:
    __________________________________________________ __
    ALTER DATABASE HIWDYNARPT
    RESTRICTED_USER
    WITH ROLLBACK IMMEDIATE
    __________________________________________________ ____

    ...as an alternative to the T-SQL killing PID's, but SQL 7.0 SP4 does not seem to support restricted user like 2000. It keeps giving me a syntax error. Does anyone have any suggestions?

    If I bring step 1 and step 2 together, separated by "GO", could this fix the problem?

    Thanks in advance!

    Ryan Hunt

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Trying to get exclusive access to a DB for restore.

    Which database did you execute your script in? Make sure it's executed in Master.

  3. #3
    Join Date
    Jan 2004
    Posts
    8

    Re: Trying to get exclusive access to a DB for restore.

    try to create a loop for step 1, some process might take longer to
    kill, and maybe by the time it's killed, new connection has been established.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    All you need...

    Code:
    ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    And do it from master

    And do the resore in the same sproc...
    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
    Jan 2004
    Location
    Boston
    Posts
    58
    It could be a rollback transaction. I suggest using "waitfor delay" to check the connections again to make sure it's clean.

  6. #6
    Join Date
    Sep 2003
    Location
    Mumbai, India
    Posts
    36
    You may choose to put in the following code before the RESTORE statement after replacing you database name at the appropriate place. It works for us everyday before a restore:

    declare @l_spid varchar(4)
    ,@l_hostname varchar(20)

    declare kill_cursor scroll cursor
    for
    select convert(varchar(4), spid), hostname from master..sysprocesses with (nolock)
    where db_name(dbid) = 'Activity_Map'

    open kill_cursor
    select @@cursor_rows

    fetch next from kill_cursor into
    @l_spid
    ,@l_hostname
    while (@@fetch_status = 0 )
    begin
    select @l_hostname Killed
    exec ( 'kill ' + @l_spid)
    fetch next from kill_cursor into
    @l_spid
    ,@l_hostname
    end
    close kill_cursor
    deallocate kill_cursor

Posting Permissions

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