SQL Server – Take database offline hangs

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

 

2 Responses to SQL Server – Take database offline hangs

  1. Colin Caesar says:

    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

  2. Colin Caesar says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: