December 23, 2014 at 8:12 am
Hi,
I am working with database restore. It is showing database is in use. Instead of single_user mode, I use the below query. It is giving error
USE [master]
GO
DECLARE @dbName SYSNAME
DECLARE @sqlCmd VARCHAR(MAX)
SET @sqlCmd = ''
SET @dbName = 'TestDB' -- Change database name here
SELECT @sqlCmd = @sqlCmd + 'KILL ' + CAST(session_id AS VARCHAR) +
CHAR(13)
FROM sys.dm_exec_sessions
WHERE DB_NAME(database_id) = @dbName
PRINT @sqlCmd
EXEC (@sqlCmd)
December 23, 2014 at 8:48 am
You don't say what error it is giving you.
I use this SP to kill connections when I restore Prod to test:
Use this as the first step in the restore job: exec sp_killallprocessindb [DBName Here]
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_killallprocessindb] Script Date: 12/23/2014 10:45:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[sp_killallprocessindb]
@dbname varchar(100)
as
if db_id(@dbname) = null
begin
print 'database dose not exist'
end
else
begin
declare @spid varchar(30)
declare tmpcursor cursor for
select 'kill ' + convert(varchar, spid) as spid
from master..sysprocesses
where db_name(dbid) = @dbname
and spid <> @@spid
and dbid <> 0
open tmpcursor
fetch next from tmpcursor
into @spid
while @@fetch_status = 0
begin
exec (@spid)
fetch next from tmpcursor
into @spid
end
close tmpcursor
deallocate tmpcursor
end
GO
December 23, 2014 at 8:49 am
ramana3327 (12/23/2014)
Hi,I am working with database restore. It is showing database is in use. Instead of single_user mode, I use the below query. It is giving error
USE [master]
GO
DECLARE @dbName SYSNAME
DECLARE @sqlCmd VARCHAR(MAX)
SET @sqlCmd = ''
SET @dbName = 'TestDB' -- Change database name here
SELECT @sqlCmd = @sqlCmd + 'KILL ' + CAST(session_id AS VARCHAR) +
CHAR(13)
FROM sys.dm_exec_sessions
WHERE DB_NAME(database_id) = @dbName
PRINT @sqlCmd
EXEC (@sqlCmd)
Did you look at the error? It'll give you a hint as to what's wrong with your SELECT statement.
December 23, 2014 at 9:07 am
KILL is never a verified way to do this. A session(s) could immediately restart itself and start using the db again.
If you don't want to use "SINGLE_USER WITH ROLLBACK IMMEDIATE" -- which does seem to pause / lock up sometimes -- then just drop the db if you're planning to restore over it anyway:
DROP DATABASE <db_name>
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 23, 2014 at 9:11 am
They way I do my refresh db jobs is the first step of the job is the kill step, second step is restore. They happen back to back and I have never had a db in use using that method.
December 23, 2014 at 9:14 am
I do it exactly the way Scott described. I switch to the target database, then alter it to single user with rollback immediate. Switch to a different database and drop the target. I'm then free to restore.
use [target];
alter database [target] set single_user with rollback immediate;
use [dba];
drop database [target];
December 23, 2014 at 9:34 am
Markus (12/23/2014)
They way I do my refresh db jobs is the first step of the job is the kill step, second step is restore. They happen back to back and I have never had a db in use using that method.
You're lucky: that is not guaranteed. Not only can a killed task in some cases reinitiate itself, but a new task can come in between generating the KILL and executing it. The more users you have, the more likely that is.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 24, 2014 at 12:02 pm
I have had the DROP fail because their were existing connections. The SINGLE USER thing seems to work the best in most cases.
One other caution is to watch your own login as being a source of problems. One of our people kept having problems running a restore. From my desk I could do it every time. Turns out that the database being restored was his default database. Adding
USE master
GO to the top of the script fixed that.
ATBCharles Kincaid
December 24, 2014 at 6:30 pm
I usually use the below code before restore:
alter database Test
set offline with rollback immediate
December 25, 2014 at 3:16 am
alter database [DB] set single_user with rollback immediate;
Thanks.
December 26, 2014 at 2:21 pm
declare tmpcursor cursor for
select 'kill ' + convert(varchar, spid) as spid
from master..sysprocesses
where db_name(dbid) = @dbname
and spid <> @@spid
and dbid <> 0
Markus out of curiosity, is there a reason to not apply this to only those processes with a spid > 49? The lower ones are system processes. I realise that set single user is the better alternative but I was merely wondering.
----------------------------------------------------
December 26, 2014 at 10:37 pm
I do it this way:
1. Set it offline to clear the connections
2. Bring it back online.
3. Drop the database.
Dropping the database ensures that there can be no connections, since there is no database to connect to. I bring the database back online before dropping it because dropping it while it is offline will not delete the data and log files. In theory, someone could connect after the database is set online but before it is dropped, but I have never seen it happen when all the commands are executed as a single batch.
use [master];
alter database [MyDatabase] set offline with rollback immediate;
alter database [MyDatabase] set online with rollback immediate;
drop database [MyDatabase];
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply