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.
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Service Statictic 11 30
Columnstore Indexes - real-time operational analytics 1 13
Sql Query Datatype 2 19
Getting max record but maybe not use Group BY 2 16
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 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

16 Experts available now in Live!

Get 1:1 Help Now