up vote 2 down vote favorite
share [g+] share [fb]

I have Microsoft SQL Server Standard Edition (64-bit) running on Windows Server 2008 R2 Standard (64-bit) with 32GB RAM. It's only database server that runs on this machine. Generally it works fine but I sometimes I get 'could not open a connection to SQL Server' so I am trying to eliminate potential causes one by one. I started with server memory:

In my database server properties Maximum server memory (in MB) is set to 2147483647 which I suppose means that SQL server dynamically takes how much memory it needs,

though sqlserver.exe process consumes only about 7 700 000 KB. Does database need only that amount or maybe there is some limit set elsewhere?

link|improve this question
feedback

2 Answers

up vote 1 down vote

Server 2008 R2 Standard can only manage 32GB of memory, SQL Server 2008 Standard can only manage 64GB of memory - so basically the OS is your limit.

If that '2147483647' number is indeed in MB then, well, that's rather a lot (2PB in fact), if it's in bytes then it's only 2GB.

link|improve this answer
As far as I know 2147483647 does nt mean 2PB but 'maximum' – jlp Apr 28 '11 at 13:06
That's correct - Most MS SQL installations show that value by default and it basically means 'use as much as you like'. Try dialling it back to a portion (3/4ths) of the total RAM in the machine. It may help. – Chris Thorpe Apr 28 '11 at 13:14
feedback
up vote 1 down vote

Here is a suggested Max Memory Settings for SQL Server 2005/2008 for starting. But do test yourself too.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.