How to Force a SQL Server 2008 Database to Go Offline

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.

  1. Restart the SQL server service using services.msc console.
  2. Now connect to your server using SQL Server Management Studio.
  3. Run following command in query analyzer

      ALTER DATABASE `YOURDATABASE_NAME`  
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  4. Now 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.

  1. 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



Leave a reply



Submit