Stop one database from SQL SERVER instance and memory allocation

Posted on 2011-03-09
Last Modified: 2012-05-11

What is the proper way to only shutdown one database within SQL Server instance which contains other user databases?

Does alter database dbname offline considered as a (or the) way to shutdown a database?

Is there any way to allocate specific amount of memory by databases instead of sharing one big memory pool with other databases under the same instance?


Question by:mystudent
  • 3
  • 2

Accepted Solution

michaelaknight earned 250 total points
ID: 35089969

ALTER DATABASE database-name SET OFFLINE    (notice SET)    

will attempt to take the named database off-line immediately. If a user or a background process is currently connected to the database the command cannot be completed. In this situation, the ALTER DATABASE statement will be blocked and will wait until all connections are closed.

The ROLLBACK clause can be used to immediately disconnect users or can be provided with a number of seconds to pause before taking the database off-line. To force the immediate disconnection of general users and processes and the rolling back of their transactions, use the following command:



The Memory configuration is set at server level and it can only be set to a max value permitting other non-sql processes to run using a fixed amount of remaining RAM.

Author Comment

ID: 35090068

Is there an option to abort the database with waiting for session rollback?

For the memory question, so if one database acts abnormally and consumes most of the memory, other database under the same instance will suffer in performance. To avoid this situation,   one should separate databases by using multiple name instances. Is it correct or are there better ways of handling it?
LVL 14

Assisted Solution

Daniel_PL earned 250 total points
ID: 35093356
You can use Resouce Governor if your edition is Enterprise.
If you prefer to install another instance please note that you need to correctly set up instances because they will compete for resources.
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.


Author Comment

ID: 35099805
Thanks for the link on the resource Governor. They are very helpful.

My another previous question about shutdown database was to " Is there any way to abort the database WITHOUT waiting for session rollback, similar to what Oracle does "shutdown abort"?
LVL 14

Assisted Solution

Daniel_PL earned 250 total points
ID: 35099827
I don't see adequate option in SQL Server, only WITH ROLLBACK IMMEDIATE.

Author Closing Comment

ID: 35100665
Thanks all.

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now