July 2, 2002 at 10:35 am
I need to performe a restore to a database everyday 2:00 in the morning which I made a job. sometimes, the job failed because of some user connections in the database.
This is a headache. It is impossible to login into the system to kill users at that time, and "sp_dboption, 'single user', true", doesn't work also because of the user connections.
Any ideas???
Thanks.
July 2, 2002 at 10:39 am
I created a sproc that creates a cursor from sp_who2 (or you can use sysprocesses) and kills each connection.
I usually run this before a restore a couple times. Some connections take a few seconds to kill.
Steve Jones
July 2, 2002 at 2:51 pm
When you make a stored procedure with a cursor to kill each connection, do you pass a parameter to the kill command?
I recently tried to do something similar but the kill command didn't seem to work with a parameter instead of a spid. (ex kill @proc_id) I didn't know how to get around that.
July 2, 2002 at 4:26 pm
select @cmd = 'kill ' + @spid
exec(@cmd)
Steve Jones
July 3, 2002 at 3:10 am
ALTER DATABASE 'database'
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
I think this is supposed to kill all connections. Am I wrong?
July 3, 2002 at 5:06 am
uddim,
That doesn't work for SQL7.
I use this as a step before my restore step in the job that copies production to dev each morning:
use master
declare @i int, @spid int, @strSQL varchar(255)
select @i = 1, @spid = 0
SET NOCOUNT ON
while @i <> 0 BEGIN
select @spid = max(spid) from master..sysprocesses where dbid = (select dbid from master.dbo.sysdatabases where name = 'MY-DATABASE-NAME')
IF @spid IS NULL or @spid < 7
SELECT @i = 0
else begin
SELECT @strSQL = 'KILL ' + convert(varchar(10),@spid)
PRINT 'Killing ' + convert(varchar(10),@spid)
EXEC (@strSQL)
end
END
Edited by - Nick Beagley on 07/04/2002 06:44:51 AM
July 4, 2002 at 5:39 am
I have a vbscript that does this. You would have to schedule it to run but it should do the trick. Let me know if you are interested.
Bruce Szabo, MCSE+I, MCDBA, MCSD
Bruce Szabo, MCSE+I, MCDBA, MCSD
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply