July 5, 2011 at 5:35 am
I wonder if anyone can help with an issue that we are having with when trying to run a job that restores a database each evening.
We are running the following query:
USE [master]
GO
Alter login [login1] DISABLE
GO
ALTER DATABASE [DB1] SET READ_WRITE WITH NO_WAIT
GO
ALTER DATABASE [DB1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [DB1] FROM
DISK = N'I:\DB1\DB1.bak' WITH FILE = 1,
MOVE N'DB1_Data' TO 'N:\DB1.mdf',
MOVE N'DB1_Log' TO 'O:\DB1_log.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
USE [master]
GO
ALTER DATABASE [DB1] SET MULTI_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [DB1] SET READ_ONLY WITH NO_WAIT
GO
Alter login [login1] enable
GO
We identified that login1 was causing some issues when trying to put the database into single user mode, however now it looks like other ID's are doing the same thing. As this is a job that runs at midnight we can't manually kill the sessions as needed.
Does anyone know a way around this issue? I have done some research and lots of people are having a similar issue, and some say that SET SINGLE_USER WITH ROLLBACK IMMEDIATE should be fine and will disconnect all sessions.
Thanks in advance for any advice.
July 5, 2011 at 7:07 am
I have a similar issue where I need to restore a few databases and kill any users that might be in there first. I wrote this stored procedure that works well for me, maybe it will work for you:
create PROCEDURE [dbo].[KillAllUserConnectionsForOneDB]
@dbName as varchar(50)
AS
declare @sqlCmd varchar(1000)
declare @PID int
declare killCurs cursor for select spid from sys.sysprocesses
where spid > 50 and dbid = (select dbid from sys.sysdatabases where name = @dbName)
open killCurs
fetch next from killCurs into @PID
while @@fetch_status <> -1
BEGIN
set @sqlCmd = 'kill ' + convert(varchar(8),@PID)
execute(@sqlCmd)
--print @sqlCmd
fetch next from killCurs into @PID
END
close killCurs
deallocate killCurs
The probability of survival is inversely proportional to the angle of arrival.
July 6, 2011 at 4:04 am
Hi Sturner thanks alot for this I will give it a go.....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply