March 3, 2009 at 8:44 am
HI All,
I have a SQL 2005 64 bit sp2 cu7 server that has 20 GB of ram. Usually it runs fine, and uses about 15 GB of physical ram for SQL Server and only a small portion of the page file 7 - 8 %. I'm trying to track down the exact time but this is the second time the server releases mass memory and reports that it is using 100% of the page file but shows that it has 15+ GB of physical memory left to consume . (Confirmed with perfmon counters). Total memory used is only a few GB on the entire server. Reboot resolves the issue and normal memory usage will continue. I see many event log entries saying significant portions of memory are paged out for SQL Server. Anyone seen something like this? Or tips on troubleshooting direction?
March 3, 2009 at 10:00 am
do you put a cap on the amount of memory allowed for SQL in order to leave a fair wack for the OS? I recall seeing these errors not so long ago and a cap (max server memory) sorted it.
thanks
SQL_EXPAT
March 3, 2009 at 10:05 am
We put a cap at about 14 GB leaving about 6 GB left of the 20 total.
March 3, 2009 at 3:43 pm
sounds like the working set is being trimmed, have you set lock pages in memory for the SQL service account.?
---------------------------------------------------------------------
March 3, 2009 at 3:48 pm
Isn't Lock pages in memory Enterprise Edition only? Also, isn't it strange though that it has 13 GB left on the system to allocate, but the page file says it is at 100%. I dont' see any pages per sec either.
March 3, 2009 at 4:01 pm
also see http://support.microsoft.com/kb/918483 - it appears lock pages in memory only applicable for enterprise edition
you didn't say what version you had!
---------------------------------------------------------------------
March 3, 2009 at 4:01 pm
Yes it is an Enterprise Edition issue only.
Are you copying large files in or out of it ?
I have found that to be the culprit on my servers so I had to use a process that do not fill up the system file cache and trim SQL memory.
* Noel
March 3, 2009 at 4:05 pm
No, on this server it is lightly used, no large copies of files.
Sorry, forgot to mention it is Standard Edition.
March 3, 2009 at 4:10 pm
Intersting I don't see any more messages that the working set is being trimmed today, the available MB is 13000, but the page file % usage is still 100%. I dont' see any pages/sec
March 3, 2009 at 4:22 pm
SQL Dude (3/3/2009)
No, on this server it is lightly used, no large copies of files.Sorry, forgot to mention it is Standard Edition.
Not Even Backup Files ?
* Noel
March 3, 2009 at 4:24 pm
Backups only occur early in the morning and the largest database is only a few GB in size. There are about 7 databases on the server.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply