SQL Server – Take database offline hangs
05-Jun-1414 2 Comments
With thanks to following and other web searches:
http://pianaro.wordpress.com/2012/11/30/sql-server-database-take-offline-hangs/
Scenario
New SQL Server on a new Azure VM, with a few new issues to learn from. I attempted to take a database offline but it hung.
Solution
USE master
SELECT resource_type, resource_associated_entity_id,
request_status, request_mode,request_session_id,
resource_description
FROM sys.dm_tran_locks
--Find troublesome session and kill it.
--You cannot kill your own process, so you may need to start another session
KILL [SPID]
To take the database offline without waiting try this:
alter database DOTNETNUKE_EBC set offline with rollback immediate
To bring database online:
alter database DOTNETNUKE_EBC set online
End
Thanks for the “alter database DOTNETNUKE_EBC set offline with rollback immediate” statement. Killing all processes on a database doesn’t always allow you to take offline via the SSMS GUI
I use this procedure, which was posted elsewhere a long while ago (cannot find source to give credit, sorry) which is useful for closing down all connections to a database:
To use, run the statement:
exec kill2 ‘db=DatabaseNameGoesHere’
This is also useful if you want to restore over a database in development or testing, run it prior to the restore.
This is the procedure script:
USE [master]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[kill2]’) AND type in (N’P’, N’PC’))
DROP PROCEDURE [dbo].[kill2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[kill2] @param2 varchar(500)
as
–declare @param2 varchar(500)
declare @param varchar(500)
declare @startcount int
declare @killcmd varchar(100)
declare @endcount int
declare @spid int
declare @spid2 int
declare @tempvar varchar(100)
declare @tempvar2 varchar(100)
–set @param2 =’54’
set @param=REPLACE(@param2,’ ‘,”)
if CHARINDEX(‘-‘,@param) 0
begin
select @startcount= convert(int,SUBSTRING(@param,1,charindex(‘-‘,@param)-1))
select @endcount=convert(int,SUBSTRING(@param,charindex(‘-‘,@param)+1,(LEN(@param)-charindex(‘-‘,@param))))
print ‘Killing all SPIDs from ‘ + convert(varchar(100),@startcount)+’ to ‘ +convert(varchar(100),@endcount)
while @startcount 50)
if @spid = @startcount
begin
print ‘Killing ‘+convert(varchar(100),@startcount)
set @killcmd =’Kill ‘+convert(varchar(100),@startcount)
exec(@killcmd)
end
else
begin
Print ‘Cannot kill the SPID ‘ +convert(varchar(100),@startcount) + ‘ because it does not Exist’
end
set @startcount=@startcount + 1
end
end
if CHARINDEX(‘,’,@param) 0
begin
set @tempvar =@param
while charindex(‘,’,@tempvar ) 0
begin
SET @tempvar2=left(@tempvar,charindex(‘,’,@tempvar)-1)
set @spid=(select spid from master.dbo.sysprocesses where spid=CONVERT(varchar(100),@tempvar2) and spid>50)
if @spid = CONVERT(varchar(100),@tempvar2)
begin
print ‘Killing ‘+CONVERT(varchar(100),@tempvar2)
set @killcmd=’Kill ‘+CONVERT(varchar(100),@tempvar2)
exec (@killcmd)
end
else
begin
Print ‘Cannot kill the SPID ‘ +CONVERT(varchar(100),@tempvar2) + ‘ because it does not Exist’
end
set @tempvar =REPLACE(@tempvar,left(@tempvar,charindex(‘,’,@tempvar)),”)
end
set @spid=(select spid from master.dbo.sysprocesses where spid=CONVERT(varchar(100),@tempvar) and spid>50)
if @spid = CONVERT(varchar(100),@tempvar)
begin
print ‘Killing ‘+CONVERT(varchar(100),@tempvar)
set @killcmd=’Kill ‘+CONVERT(varchar(100),@tempvar)
exec (@killcmd)
end
else
begin
Print ‘Cannot kill the SPID ‘ +CONVERT(varchar(100),@tempvar) + ‘ because it does not Exist’
end
end
if CHARINDEX(‘=’,@param2) 0
begin
print ‘Killing all the SPIDs that are connected to the database ‘+RIGHT(@param2,(len(@param2)-3))
declare dbcursor
cursor forward_only for select SPID from master.dbo.sysprocesses where DB_NAME(dbid) = RIGHT(@param2,(len(@param2)-3))
open dbcursor
fetch dbcursor into @spid
while @@FETCH_STATUS =0
begin
set @spid2=(select spid from master.dbo.sysprocesses where spid=@spid and spid>50)
if @spid = @spid2 begin
print ‘Killing ‘+CONVERT(varchar(100),@spid2)
set @killcmd=’Kill ‘+CONVERT(varchar(100),@spid2)
exec (@killcmd)
end
else
begin
Print ‘Cannot kill the SPID ‘ +CONVERT(varchar(100),@spid2) + ‘ because it does not Exist’
end
fetch dbcursor into @spid
end
close dbcursor
deallocate dbcursor
end
if CHARINDEX(‘-‘,@param)=0 and CHARINDEX(‘,’,@param) = 0 and CHARINDEX(‘=’,@param)=0
begin
set @spid=(select spid from master.dbo.sysprocesses where spid=CONVERT(varchar(100),@param) and spid>50)
if @spid = CONVERT(varchar(100),@param)
begin
print ‘Killing ‘+CONVERT(varchar(100),@param)
set @killcmd=’Kill ‘+CONVERT(varchar(100),@param)
exec (@killcmd)
end
else
begin
Print ‘Cannot kill the SPID ‘ +CONVERT(varchar(100),@param) + ‘ because it does not Exist’
end
end