December 15, 2009 at 4:42 pm
Just an overview of server, it has 32 gb RAM runs on 64 bit 2003 OS with 64 bit sql 2005. I see that the page file is maxed out like using 32 gb . Also sql jobs are failing with the error:
The system reports 99 percent memory load. There are 34353471488 bytes of physical memory with 329920512 bytes free. There are 4294836224 bytes of virtual memory with 3985584128 bytes free. The paging file has 35499278336 bytes with 8781824 bytes free..
I have set up a perfmon log but that doesnt seem to give much info about the memory usage. I have also set the min and max memory to 2 and 16 gb respectively. The page file is reduced after bouncing the server but gradually uses all of it again. I am scared the jobs might fail again. Can someone please direct me what can be done now. Thanks in advance.
December 15, 2009 at 5:21 pm
Do you have min and max memory settings set in SQL? If not, I would set it to make sure that you leave enough for the OS. If the max is too large, it will start taking up that memory as it needs it (in SQL 2005). If it is not actively using all of it and the OS needs some SQL will release memory, but that can cause errors sometimes while SQL is releasing the memory.
Joie Andrew
"Since 1982"
December 16, 2009 at 3:45 am
Just an overview of server, it has 32 gb RAM runs on 64 bit 2003 OS with 64 bit sql 2005. I see that the page file is maxed out like using 32 gb . Also sql jobs are failing with the error:
Hi,
Can u post the error details
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 16, 2009 at 9:01 am
thanks everyone for the reply. I have two instances running on it and i have set 2 and 16 gb min/max for one and 2 and 8 gb min/max for the other. I see there is decrease in page file usage and available memory has increased. I have also enabled AWE. I will monitor for few more days and post it here. Muthu i have the error posted in my initial post , please take a look. Thanks,
December 16, 2009 at 12:04 pm
You don't need AWE for a 64-bit system. It is meant for 32-bit systems with the /PAE switch enabled. To clarify, did you just now set the min/max memory values, or were they always set to the values you posted?
Joie Andrew
"Since 1982"
December 17, 2009 at 5:35 am
Muthu i have the error posted in my initial post , please take a look. Thanks,
Just an overview of server, it has 32 gb RAM runs on 64 bit 2003 OS with 64 bit sql 2005. I see that the page file is maxed out like using 32 gb . Also sql jobs are failing with the error:
Hi,
I couldn't find any error massage.
I have also enabled AWE. I will monitor for few more days and post it here.
Joie Andrew already point out Awe not needed for 64-Bit
Additionally,
Did u enable the "Lock pages in memory" or not .
Can u post the sql server edition (@@version)
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 18, 2009 at 3:05 am
In 64-bit Windows, SQL Server thinks the total amount of memory available is the total of the physical memory plus the size of the page file. Therefore if you leave the SQL max memory setting at its default value then SQL will try to use all of the 'memory', even though it will result in a shedload of paging to disk.
It is very important in 64-bit SQL Server to set the maximum memory value. You need to allow enough memory for the operating system plus any non-OS tasks that run regularly. This includes anti-virus, SQL backups, SSIS, as well as other SQL instances.
Your last post says you now have max memory values set that will use 24 GB out of the 32 GB you have on the box. This is a good start, but you may want to experiment increasing the total memory allocated to SQL until you get up to about 28GB, as you may have some unused memory available. If you do this, then increase by 1GB intervals and only make 1 change per day until you start to see memory pressure. When you think you are starting to over-allocate memory than reduce your max memory by 500MB or 1GB so things are running at their optimum.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 18, 2009 at 5:44 am
On our 128gb ram 64bit SQL 2005 servers we set the page file to 0 - stops windows trying any funny business.
December 18, 2009 at 5:54 am
There are a few SQL experts and some PSS engineers who suggest setting the pagefile to zero on a 64-bit box that has memory in double-digit GBs. The rationale is that a well-tuned system should never need the page file or the CPU and I-O cost that comes with managing it.
I don't use a page file on my 12GB home machine and have had no problems, but I would like to see some official Microsoft advice on this subject.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 18, 2009 at 9:48 am
What you won't get if you set the page file on the C: drive to 0 is a memory dump in case of system failure.
Joie Andrew
"Since 1982"
December 18, 2009 at 12:54 pm
iqtedar (12/16/2009)
thanks everyone for the reply. I have two instances running on it and i have set 2 and 16 gb min/max for one and 2 and 8 gb min/max for the other. I see there is decrease in page file usage and available memory has increased. I have also enabled AWE. I will monitor for few more days and post it here. Muthu i have the error posted in my initial post , please take a look. Thanks,
What are the actual values you have entered for min/max? Whatever number you enter, note that the number is in MB's and not KB's. The number you want to set for 2GB is 2048.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 20, 2009 at 6:40 pm
thanks Jeff..Yup i realized the memory in in Mb.s
December 20, 2009 at 6:49 pm
iqtedar (12/20/2009)
thanks Jeff..Yup i realized the memory in in Mb.s
So, was that the problem and is it now resolved? Or, are you still having the issue?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 21, 2009 at 8:48 am
well i do not see the memory issue after making changes but i still see the page file size shows like 21 gb out of 34 gb from task manager i am assuming the stats shown in task manager is not the actual used by sql server....will update..thanks
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply