12-28-04, 11:24 #1Registered User
- Join Date
- Sep 2003
Unanswered: SQL Server memory usage continue to increase
On the last Sunday 26/12/04 on 20:00 I got on one of our applications in the customer site this error:
"There is insufficient system memory to run this query."
Computer: IBM XSERIES_345, dual CPU 2.8 Intel xeo, 1,047,952 KB ram, windows 2000 server sp4.
Disk space: 4395 MB, Virtual memory: initial size 1536 MB, Max size 3072 MB. Registry size: current 14 MB, Max 90 MB.
SQL Server 2000 SP 3, two production databases (1) Data size 100 MB, log size 15 MB (2) Data size 500 MB, log size 125 MB
All the memory options are configured as default i.e. dynamically configure for memory and not changes in any sp_configure options.
SQL server is the only major application running on the machine.
On that evening the I've checked the Task Manager and found that SQL Server use 500 MB in Mem Usage and 1200 MB in VM size.
In the query analyzer I execute the select query which results the "…insufficient system memory…" error on both databases and on the one with more data it failed with this error, but on the other database it completed successfully.
I restart SQL Server service to solve it, and as for today 28/12/04 at 08:00 it use 590 MB in Mem Usage and 590 MB in VM size.
I have checked the system with Performance monitor and didn't find any problems I use the Memory: Pages/sec, Memory: Available Bytes, Physical Disk: % Disk Time, Processor: % Processor Time, SQL Server Buffer: Buffer Cache Hit Ratio.
We have 8 user connections for one of the database and 16 for the other one.
Our system runs with this configuration for 2 months, I backup the databases FULL, DIFF and LOG backup.
It's not real time system, and the load on the SQL is medium, but we use bad queries as update for all fields in one query (please don't ask…)
I know that SQL server is greedy with the memory and released memory only if other process needs it.
Any ideas why it happens?
12-28-04, 17:56 #2Registered User
- Join Date
- Aug 2003
Personally, I just set up a task to reboot the server once a week. SQL takes what it wants ... that's just MicroSoft .... so you have to take it back.
12-28-04, 18:49 #3Registered User
- Join Date
- Jul 2003
- San Antonio, TX
2GB of RAM is becoming a standard in low-budget environments, so the fact that you have over 1GB paged is not surprising."The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."