January 3, 2011 at 9:16 am
On a regular basis, we restore production database to testing servers. To do this we must have no connections to the target database. There are GUI applications and Management Studio connections that can exist when the restores are scheduled. A Stored Procedure was created to KILL all SPIDs that are connected to the database. Since we have moved to SQL Server 2008 SP1 CU9 this process sometimes does not work. It was originally created on SQL Server 2000 SP3a. Are there any known issues with the Kill command? Is there a better way to do this?
The code is below to create a Dynamic SQL Statement to KILL the SPIDS.
SELECT @spidstr=coalesce(@spidstr,',' )+'kill '+convert(varchar, spid)+ '; '
FROM master..sysprocesses WHERE dbid=db_id(@DBName)
and status <>'background'
It results in this statement that is then executed.
kill 58; kill 59; kill 60; kill 69;
We have tried moving the execution of the stored procedure closer to the RESTORE command and have run sp_who2 to see if there are any connections. There are ususally none. Sometimes the application re-connects.
Thanks for any help,
Steve
January 3, 2011 at 9:22 am
Are you doing the kill and the restore in a single script with a single connection? If so, you could set the database to single-user mode as part of that. That'll prevent new connections and reconnections.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 3, 2011 at 9:25 am
Well we tried the single user mode. We are using Lite Speed and it tries to open another connects and fails. So we abandon that method.
Steve
January 3, 2011 at 9:31 am
You might try restricted_user mode, which only lets Db_owner/sysadmin in. That might work for you.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
January 3, 2011 at 9:39 am
It is an old app and the main user id that connects is sysadmin (I know bad design).
January 3, 2011 at 9:41 am
Sorry its not sa. It is another user id that has SYSADMIN rights.
January 3, 2011 at 3:02 pm
In this case, you have no choice unless you stop the app explicitly. (Otherwise, the app will keep trying to connect the database.)
January 3, 2011 at 3:17 pm
Well we tried the single user mode. We are using Lite Speed and it tries to open another connects and fails. So we abandon that method.
Steve
I use Litespeed and restore a copy of live to our report server every night. What I found that works very well is:
ALTER DATABASE {database} SET OFFLINE WITH ROLLBACK IMMEDIATE;
Execute master.dbo.xp_restore_database
@database = '{database}'
,@filename = '{file name}'
,@with = 'MOVE "logical name" TO "new location"'
,@with = 'MOVE "logical name" TO "new location"'
,@with = 'REPLACE'
,@with = 'STATS = 10'
,@with = 'NORECOVERY';
RESTORE DATABASE {database} WITH RECOVERY;
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
January 3, 2011 at 4:17 pm
I used this in a job step prior to the lightspeed restore and it works fine restoring from prod to dev.
DECLARE @DatabaseName nvarchar(50)
DECLARE @SPId int
DECLARE @userid varchar(100)
declare @command varchar(200)
SET @DatabaseName = N'DBName'
SET @userid = N'UserName'
--SET @DatabaseName = DB_NAME()
DECLARE my_cursor CURSOR FAST_FORWARD FOR
SELECT SPId FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
and loginame = @userid
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @SPId
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Command = 'KILL '+CAST(@SPId AS VARCHAR)+'; '
exec (@Command)
FETCH NEXT FROM my_cursor INTO @SPId
END
CLOSE my_cursor
DEALLOCATE my_cursor
SELECT * FROM MASTER..SysProcesses
January 3, 2011 at 7:04 pm
willtwc (1/3/2011)
I used this in a job step prior to the lightspeed restore and it works fine restoring from prod to dev.DECLARE @DatabaseName nvarchar(50)
DECLARE @SPId int
DECLARE @userid varchar(100)
declare @command varchar(200)
SET @DatabaseName = N'DBName'
SET @userid = N'UserName'
--SET @DatabaseName = DB_NAME()
DECLARE my_cursor CURSOR FAST_FORWARD FOR
SELECT SPId FROM MASTER..SysProcesses
WHERE DBId = DB_ID(@DatabaseName) AND SPId <> @@SPId
and loginame = @userid
OPEN my_cursor
FETCH NEXT FROM my_cursor INTO @SPId
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Command = 'KILL '+CAST(@SPId AS VARCHAR)+'; '
exec (@Command)
FETCH NEXT FROM my_cursor INTO @SPId
END
CLOSE my_cursor
DEALLOCATE my_cursor
SELECT * FROM MASTER..SysProcesses
Note, this method has the potential of allowing another process to connect while you are killing the SPID's. I prefer using a method that will not allow that to happen - which setting the database offline does.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply