?
Solved

Stop one database from SQL SERVER instance and memory allocation

Posted on 2011-03-09
6
Medium Priority
?
663 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 1000 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 1000 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

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 1000 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

719 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