Memory problems

Hi,

I'm seeing some errors in the SQL server error logs:

-Downgrading backup log buffers from 1024K to 64K -AppDomain 14 (MVXEDU.JBNET\dbryan[runtime].13) is marked for unload due to memory pressure. -Not enough memory was available for trace when trying to run profiler

My server is Win 2003 R2 Enterprise Edition SP1, in Task Manager I see: Physical Memory (k) Total: 33549308 Avail:30361340 System Cache:7235528

I ran some System Monitor counters in two 15 min intervals and get the following:

Memory: Avail Bytes 3.1050E+010 3.1041E+010 Memory: Pages/Sec 1.592 1.355 Process: Working Set 2639784438 2641416110 SQL Server: Buffer Cache Hit Ratio 99.853 99.846 SQL Server: Buffer Manager Total Pages 194472 194472 SQL Server: Memory Manager Total Server Memory (KB) 1555776 1555776 In the server properties, we do not have AWE enabled and min server memory = 0, max server memory = 2147483647, Index creation Memory = 0 and min memory per query = 1024 (which I think is all the defaults)

I'm new to SQL and Windows so can anyone take a look at my numbers and tell me if they look okay or if anything is giving an inidication of the memory issues I'm having. They look okay to me, but as I said I am new to this so I would love some extra opinions!

Thanks,
Isabelle

What version of SQL Server are you running?

Code Snippet

SELECT @.@.VERSION

Also run

Code Snippet

xp_msver

and post the values from Index 7, 8, 16 and 19

|||

What version of SQL Server are you running?

-

Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)

Oct 14 2005 00:33:37

Copyright (c) 1988-2005 Microsoft Corporation

Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

xp_msver

7 FileDescription NULL SQL Server Windows NT

8 FileVersion NULL 2005.090.1399.00

16 ProcessorCount 16 16

19 PhysicalMemory 32763 32763 (34354491392)

Thanks!

Isabelle

|||

One thing I notice is that you are running the RTM build of SQL Server 2005 (Build 1399), along with SP1 of Windows Server 2003.

If you can, I would really try to get SP2 for SQL Server 2005 installed (Build 3042 or higher), along with SP2 for Windows Server 2003. SP2 for SQL Server 2005 has a lot of fixes related to memory management that may help some.

Since you have 32GB of RAM, you would be much better off running the 64-bit version of Windows Server 2003 along with the 64-bit version of SQL Server 2005. On 32-bit SQL Server 2005, even with AWE enabled, only certain things can use that extra memory above 4GB. Pretty much any CPU made in the last 18-24 months will support 64-bit.

|||

You are running the 32bit verion of SQL on a 32bit OS is appears.

You need to enable AWE in order to use more than 2 Gigs of memory. You also need to add the /3GB and /PAE flags to the boot.ini and reboot Windows in order to address over 2 Gigs of Memory per process.

You should limit your memory to 24572 Megs. (Microsoft's new thing is 75% of memory for SQL, 25% for Windows). You can increase that number as needed up to 30715 but no higher than that.

Start there and report back if the issue is still happening.

|||Great! Thanks for the info. I will look into making those changes and see what happens. I am planning on applying the SQL 2005 SP2 to this server this Sunday, so hopefully that will help too.