How to force a SQL Server 2008 database to go Offline

Question

How do I force my Database to go Offline, without regard to what or who is already using it?

I tried:

ALTER DATABASE database-name SET OFFLINE;

But it's still hanging after 7 min.

I want this because I need to test the scenario.

If it's even possible?

score 201 · Accepted Answer · answered Jun 09 '10 at 12:21

201

Go offline

USE master
GO
ALTER DATABASE YourDatabaseName
SET OFFLINE WITH ROLLBACK IMMEDIATE
GO

Go online

USE master
GO
ALTER DATABASE YourDatabaseName
SET ONLINE
GO

answered Jun 09 '10 at 12:21

abatishchev

8

@radbyx: MSDN says to use `master` when operating DB's state – abatishchev Jun 10 '10 at 15:36
17

@radbyx: If you USE MY_DATABASE, then ALTER DATABASE MY_DATABASE SET OFFLINE will fail, *because* you're using it! Yes, I just got stung by that... – TarkaDaal Mar 29 '12 at 12:04
10

Didnt work for me: Msg 5061, Level 16, State 1, Line 1 ALTER DATABASE failed because a lock could not be placed on database 'MyDatabase'. Try again later. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed. – Andez Jul 09 '12 at 11:02
6

I've seen occurences where you still need to use an 'sp_who2 active'/'sp_whoisactive' to see some stubborn connections from scheduled job machines or middletier stuff and run KILL with the SPID number to get rid of them – Chris Wood Jun 25 '14 at 14:57

score 28 · Answer 2 · answered Jun 09 '10 at 12:19

28

You need to use WITH ROLLBACK IMMEDIATE to boot other conections out with no regards to what or who is is already using it.

Or use WITH NO_WAIT to not hang and not kill existing connections. See http://www.blackwasp.co.uk/SQLOffline.aspx for details

answered Jun 09 '10 at 12:19

Martin Smith

2 Answers2