2

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?

jlp
  • 141
  • 1
  • 5

2 Answers2

1

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.

Chopper3
  • 101,299
  • 9
  • 108
  • 239
  • 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
1

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

DaniSQL
  • 1,107
  • 7
  • 12