How to kick users off before restoring database for logshipping

  • Dear DBAs,

        I got the following error in logshipping.

        [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3101: [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be obtained because the database is in use.

    [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.

        I manually kill the process, then it works. How can I prevent it ? Does any one know how to setup an option to kick off the users before restoring the database?

        Thanks

    Kenneth

     

  • Yes - do you want to warn them first or just torpedo them?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Hi Phil,

        I don't have to warn them. I just want to kill the processes before restoring database in log shipping. Please let me know how to set it up.

        Thanks

    Kenny

  • Check out this link for some ideas:

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=169&messageid=146618

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Thanks Phil,

        I found the way to kill the users before restoring the database in log shipping. In Database Maintence Plans -> select Log Shipping tab -> click edit -> Initialize, then put check mark on option 'Terminate Users in databases (Recommended)'

        I will find it out whether it works.

    Kamta   

     

  • --kill all processes within a database

    --- run in master db

    create proc sp__killprocesses

    @dbname  varchar(50),  -- destination database

    @object  varchar(50)     = NULL

    as

    declare @spid int

    declare @killsmt varchar (20)

    if @object is not NULL

    begin

     select @object = @dbname + '.dbo.' + @object

     --print @object

     select @spid = max(spid)

     from master..sysprocesses p

     where dbid = db_id(@dbname)

      and exists

      (

       select  * from master..syslockinfo

       where  rsc_dbid = db_id(@dbname)

       and    rsc_objid = object_id(@object)

       and  req_spid = p.spid

     &nbsp

     while @spid > 0

     begin

      select @killsmt = 'KILL ' + CONVERT(varchar, @spid)

      select @killsmt

      exec (@killsmt)

      waitfor delay '00:00:01'

      select @spid = isnull(max(spid),0)

      from master..sysprocesses p

      where dbid = db_id(@dbname)

      and exists

      (

       select  * from master..syslockinfo

       where  rsc_dbid = db_id(@dbname)

       and    rsc_objid = object_id(@object)

       and  req_spid = p.spid

     &nbsp

     end

    end

    else

    begin

     select @spid = max(spid)

     from master..sysprocesses

     where dbid = db_id(@dbname)

     while @spid > 0

     begin

      select @killsmt = 'KILL ' + CONVERT(varchar, @spid)

      select @killsmt

      exec (@killsmt)

      waitfor delay '00:00:01'

      select @spid = isnull(max(spid),0) from master..sysprocesses where dbid = db_id(@dbname)

     end

    end

    GO

  • THis might do the trick as well ...

     

    ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    go

  • Thanks DBA frindeds

           For the Logshipping case, I would perfer to use 'Terminate Users in databases (Recommended)'. It actully kill the processes before restoring the database. And  it's been working fine since I opened the question. You may  use this option if you do log shipping.

          Thanks a again

    Kenny

     

     

     

  • i'm using a standard edition of ms sql.  the terminate users option for log shipping works only if you're using the enterprise edition.

    thanks for the t-sql code that shows me how to do it. =)

    ann

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

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