How to kick users off before restoring database for logshipping

Question

How to kick users off before restoring database for logshipping

Kenny Yen

Mr or Mrs. 500

Points: 559
More actions
July 15, 2005 at 8:16 am

#165002

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

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

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

Phil Parkin SSC Guru Points: 246961 More actions · Answer 1

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

Kenny Yen Mr or Mrs. 500 Points: 559 More actions · Answer 2

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

Phil Parkin SSC Guru Points: 246961 More actions · Answer 3

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

Kenny Yen Mr or Mrs. 500 Points: 559 More actions · Answer 4

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

ND-215452 SSCrazy Points: 2037 More actions · Answer 5

--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

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

GO

Nikhil Khimani SSC Enthusiast Points: 174 More actions · Answer 6

THis might do the trick as well ...

ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

go

Kenny Yen Mr or Mrs. 500 Points: 559 More actions · Answer 7

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

ann ramos Right there with Babe Points: 791 More actions · Answer 8

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