Inssufficient System Memory Error - Can you look at my settings?

[bmacbmac]

Hi,

We are getting the error: There is insufficient system memory to run this query when backing up even a small database with less than 100 rows.

Processes in task manager show memory at 2.88gb being used. SQL using about 2gb of that.

This seems to happen about every two days until we restart the server.

Would someone be able to review my settings below and see if there is any glaring change I should make?

Windows 2003 Server
4 processors
4gb RAM (although system properties shows 3.25gb)

We have approximately 45 workstations using an application on SQL.

SQL 2000 standard SP4
SQL Settings:
Min Memory: 0
Max Memory: 3250 (out of 3327)
Worker Threads: 50
Boost SQL Priority turned on
Minimum query plan for parallel execution: 5


Thanks!

 

[ptheriault]

I would set the max memory to 2 GB.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[aalmeida]

bmacbmac

I would like to aski you a few questions if don't mind?
Is there any other application besides SQL Server running on this windows server?

Have you set /PAE and /3GB switch on the windows 2003 startup?

Have you checked the size available for the windows server paging file?

Let us know the answer to these questions and we will take it from there.

AL Almeida
Senior DBA

http://www.ajmedia.net/

"May all those that come behind us, find us faithfull"

 

[mrdenny]

Your max memory setting won't matter. SQL Server 2000 Standard only supports 2 Gigs of RAM. As you are not using more than 2 Gigs of RAM you should not have AWE enabled.

How large are your databases? How many transactions per second are you running? How many concurrent sessions?, concurrent queries? Check the SQL Server Memory Manager:Memory Grants Pending counter and see what it's running at when you are getting the error. Zero is good, one is bad, more than one is VERY bad.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog