How to force a SQL Server 2008 database to go Offline
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
Extreme wait-time when taking a SQL Server database offline
After some additional searching (new search terms inspired by gbn's answer and u07ch's comment on KMike's answer) I found this, which completed successfully in 2 seconds:
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE
(Update)
When this still fails with the following error, you can fix it as inspired by this blog post:
ALTER DATABASE failed because a lock could not be placed on database 'dbname' Try again later.
you can run the following command to find out who is keeping a lock on your database:
EXEC sp_who2
And use whatever SPID
you find in the following command:
KILL <SPID>
Then run the ALTER DATABASE
command again. It should now work.
how to stop the process take table offline in sql server managment studio
the solution was this:
check the process running with
EXEC sp_who2
search for the one that was an alter table in my case the 78, i get info about the spid command with:
DBCC INPUTBUFFER(78)
once i was sure that was the process i kill it with:
KILL 78
SQL Script to take a Microsoft Sql database online or offline?
ALTER DATABASE database-name SET OFFLINE
If you run the ALTER DATABASE command whilst users or processes are connected, but you do not wish the command to be blocked, you can execute the statement with the NO_WAIT option. This causes the command to fail with an error.
ALTER DATABASE database-name SET OFFLINE WITH NO_WAIT
Corresponding online:
ALTER DATABASE database-name SET ONLINE
How to stop a single database in an sql instance?
You can set it Offline in SSMS.
Right click -> Task -> Take Offline
You can set database to Restricted User Mode:
ALTER DATABASE database-name SET RESTRICTED_USER
but I'm not sure this works on SQL Server 2008
SQL Server 2008 R2 Stuck in Single User Mode
In first run following query in master database
exec sp_who
If you can't find the culprit, try
SELECT request_session_id FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID('YourDatabase')
Then kill all process that use your database with following query:
KILL spid
Then run following query:
USE Master
ALTER DATABASE YourDatabase SET MULTI_USER
Script to kill all connections to a database (More than RESTRICTED_USER ROLLBACK)
Updated
For MS SQL Server 2012 and above
USE [master];
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'
FROM sys.dm_exec_sessions
WHERE database_id = db_id('MyDB')
EXEC(@kill);
For MS SQL Server 2000, 2005, 2008
USE master;
DECLARE @kill varchar(8000); SET @kill = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('MyDB')
EXEC(@kill);
Database is not accessible after 'taking it offline' process failed
Try following steps.
- Restart the SQL server service using
services.msc
console. - Now connect to your server using SQL Server Management Studio.
Run following command in query analyzer
ALTER DATABASE `YOURDATABASE_NAME`
SET SINGLE_USER WITH ROLLBACK IMMEDIATENow Right-click the database name, point to Tasks, and then click Detach. The Detach Database dialog box appears
OR
5. Run your command to Relocate the secondary database.
Set the database mode to multi user again
ALTER DATABASE `YOURDATABASE_NAME` SET MULTI_USER
Hope this helps.
SQL Server 2008 restore option is disabled when database goes offline
You cannot backup a database that is offline.
MSDN Reference
Related Topics
How to Enforce Set-Like Uniqueness Between Multiple Columns
How to Find the Size of an Array in Postgresql
Pass Multiple Sets or Arrays of Values to a Function
Delete Oldest Records from Database
How to Make Shiny Reactivity Work with SQL Database
Why Does SQL Server Return 0 for 1/2
Query Grants for a Table in Postgres
T-Sql: Separate String into Multiple Columns
Select Top Distinct Results Ordered by Frequency
How to Show Blank Record in SQL If Duplicate Rows Exists
How to Get a Hash of an Entire Table in Postgresql
Multiple Counts Within a Single SQL Query
Ansi_Nulls and Quoted_Identifier Killed Things. What Are They For
For Each Quarter Between Two Dates, Add Rows Quarter by Quarter in SQL Server
Inserting Data into a Temporary Table
Remove Trailing Spaces and Update in Columns in SQL Server
SQL Server 2008 Open Master Key Error Upon Physical Server Change Over