SQL Server – DB Maintenance Commands

Posted: May 9, 2012 in SQLServer

Change DB to single/multi user mode

use master;
go
alter database <DB NAME> set single_user with rollback immediate;
alter database <DB NAME> set multi_user;
go

Take the Database Offline/Online
ALTER DATABASE <DB Name> SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [myDB] SET ONLINE
GO

In case if above commands are not working bcos the DB is in use then you can find which process is using the DB and kill those processes using …

EXEC sp_who2 — To see which processes are using the DB
KILL (<SPID>) — Give the process id (SPID) to Kill it

One other way to kill all the active processes is – Detach by selecting Drop Connections checkbox and Attach it again.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s