Solved

Stop one database from SQL SERVER instance and memory allocation

Posted on 2011-03-09
6
656 Views
Last Modified: 2012-05-11
Hi,

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?

Thanks,

0
Comment
Question by:mystudent
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 9

Accepted Solution

by:
Michael Knight earned 250 total points
ID: 35089969
yes,

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:

ALTER DATABASE database-name SET OFFLINE WITH ROLLBACK IMMEDIATE

------------------------

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

Author Comment

by:mystudent
ID: 35090068
Thanks.

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?
0
 
LVL 14

Assisted Solution

by:Daniel_PL
Daniel_PL earned 250 total points
ID: 35093356
You can use Resouce Governor if your edition is Enterprise.
http://www.sqlservercentral.com/articles/Resource+Governor/64034/
http://msdn.microsoft.com/en-us/library/bb933866%28v=SQL.100%29.aspx
http://blogs.technet.com/b/andrew/archive/2008/02/06/sql-server-2008-resource-governor-in-action.aspx
http://www.mssqltips.com/tip.asp?tip=1720
etc.
If you prefer to install another instance please note that you need to correctly set up instances because they will compete for resources.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:mystudent
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"?
0
 
LVL 14

Assisted Solution

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

Author Closing Comment

by:mystudent
ID: 35100665
Thanks all.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
get_systemdrive info from tsql? 1 26
Add a step to a system backup job 6 55
Enabled trace flag 4135 or 4199 - SQL SERVER 2 34
SQL- GROUP BY 4 53
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

710 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