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

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

1 Answer 1

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

You must log in to answer this question.