SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Memory Management
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sandesh_moghe
Constraint Violating Yak Guru

India
310 Posts

Posted - 12/25/2006 :  19:34:22  Show Profile  Send sandesh_moghe an AOL message  Send sandesh_moghe a Yahoo! Message  Reply with Quote
Hello All,

I have two instances of SQL Server 2000.
Total Server Memory - 7.75 GB
Default instance is dynamically configured with 6GB as max memory.
Named instance is configured in the same way.

Let's say, due to heavy connections, default instance is occupying almost 5GB of memory. But now there are very less connections, hence default instance doesnt required that much of memory. I know that SQL Server doesn't return it back unless OS requires it.

Now my question is: If named instance require more memory, will default instance return some for named instance?

Thanks in advance.

-Sandesh

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/26/2006 :  13:19:30  Show Profile  Reply with Quote
My understanding is that if you have two instances of SQL Server installed you should NOT allocate memory to them such that the totally allocated to both exceeds the physical available memory.

Kristen
Go to Top of Page

sandesh_moghe
Constraint Violating Yak Guru

India
310 Posts

Posted - 12/26/2006 :  14:57:17  Show Profile  Send sandesh_moghe an AOL message  Send sandesh_moghe a Yahoo! Message  Reply with Quote
Thanks Kristen.
I know this is not good configuration.
But just wanted to know if i am having such configuration, will default instance free up some memory for named instance like- it releases for OS?

Thanks in advance.
Sandesh
Go to Top of Page

snSQL
Flowing Fount of Yak Knowledge

USA
1837 Posts

Posted - 12/26/2006 :  15:16:01  Show Profile  Reply with Quote
As long as you allow SQL Server to reduce memory when necessary it will balance between the instances. In your case you must not use "fixed memory" allocation (ie. don't set min server memory and max server memory to the same values). As long as you have min memory at a lower value than max memory, SQL Server will reduce down to the min value on either instance as necessary.

In 2000 Books Online, see "Dynamically Managing Memory Between Multiple Instances", link on MSDN here
2000 - http://msdn2.microsoft.com/en-us/library/aa224754(sql.80).aspx
In 2005 Books Online, "Server Memory Options/Running Multiple Instances of SQL Server", link on MSDN here
http://msdn2.microsoft.com/en-us/library/ms178067.aspx

Go to Top of Page

monty
Posting Yak Master

130 Posts

Posted - 12/26/2006 :  22:15:16  Show Profile  Reply with Quote
its not garunteed that the memory will be released only for the instance in case other applications are in need of memory then it will be difficult for things to happen like wat u want it to be, but if it is a stand alone box then yes it will be relesed for the needded instance, but still i will repeat wat others have said u need to think again about ur config

its me monty
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000