4

My system configuration is like this

4 GB Ram and 2 instances are running on the box

1.SQL 2000 Server 2.SQL 2008 server

Max Memory configuration for servers is 1500 for SQL Server 2000 and 1500 SQL Server 2008

Issue:

One database is going to suspect mode always, if I run sp_who2 I see the transaction roll back transaction is not completed still running

If I restart the system database is working,

Question: database suspect occurs due to insufficient memory ?

  1. Please suggest me the Max memory configuration for both servers

  2. Please suggest me when I install a SQL Server 2008 servers do I have to Add account in Lock Pages In memory.

  3. Please suggest me how to find out memory issues key counters to look and preferable values..

Please let me know.plzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

Improve this question
1
  • Is it x86 or x64 (both SQL and OS)? Commented Jun 23, 2010 at 20:28

1 Answer 1

Reset to default
1

check this blog by Glen Berry Suggested Max Memory Settings for SQL Server 2005/2008

Here is an excerpt:

These settings are for x64, on a dedicated database server, only running the DB engine, (which is the ideal situation).

Physical RAM MaxServerMem Setting 2GB 1500 4GB 3200 6GB 4800 8GB 6400 12GB 10000 16GB 13500 24GB 21500 32GB 29000 48GB 44000 64GB 60000 72GB 68000 96GB 92000 128GB 124000 >

If you are running other SQL Server components, such as SSIS or Full Text Search, you will want to allocate less memory for the SQL Server Buffer Pool. You also want to pay close attention to how much memory is still available in Task Manager. This is how much RAM should be available in Task Manager while you are under load (on Windows Server 2003):

Physical RAM Target Avail RAM in Task Manager < 4GB
512MB – 1GB 4-32GB
1GB – 2GB 32-128GB
2GB – 4GB

128GB > 4GB

Improve this answer

You must log in to answer this question.