by Stian

Long wait-time when taking SQL Server database offline

dbofflineWhen trying to take a SQL Server database offline, you might find yourself waiting, waiting and waiting because some process is hugging the database. This is how you fix it. Run:

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE

In some cases you might get an error saying:

ALTER DATABASE failed because a lock could not be placed on database 'dbname' Try again later.

After you get this error, run

EXEC sp_who2

Look for the database in the result and find the SPID for that connection, then run

KILL <SPID>

That should do the trick!

Tags:
  • http://blog.degree.no/bloggere/ Andreas

    I’ve made a procedure to drop all connections, which gets the SPID by database name:


    DECLARE @dbname sysname

    SET @dbname = 'My_Crazy_Database'

    DECLARE @spid int
    SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname)
    WHILE @spid IS NOT NULL
    BEGIN
    EXECUTE ('KILL ' + @spid)
    SELECT @spid = min(spid) from master.dbo.sysprocesses where dbid = db_id(@dbname) AND spid > @spid
    END

  • Pingback: С миру по нитке, №8 | Things I think about()

  • Anonymous

    it works for me, thanks!

  • Anonymous

    hi will you please help me to create a splash form and how may i apply timer on that form if you can i am very thankfull to you

  • Anonymous

    nice, thanks

  • William Astarita

    Getting “Cannot Use Kill to kill your own process

  • Vijay Parmar

    Please close sql server management studio, Simple!