March 30, 2009 at 12:53 am
Hi All,
I got a memory leak problem as per below. Any way to solve the memory leak problem without upgade the sp? Currenlty the only way is restart the sql services.
SQL Server 2000 Standard edition with sp2.
2009-03-28 21:56:35.84 spid68WARNING: Failed to reserve contiguous memory of Size= 65536.
2009-03-28 21:56:35.87 spid68Dynamic Memory Manager: Stolen=1780 OS Reserved=1472 ...
2009-03-28 21:56:35.87 spid68Procedure Cache: TotalProcs=235 TotalPages=1228 InUsePages=33
2009-03-28 21:56:35.87 spid68Buffer Counts: Commited=188568 Target=208688 Hashed=179714...
2009-03-28 21:56:35.87 spid68Buffer Distribution: Stolen=552 Free=7074 Procedures=1228...
2009-03-28 21:56:35.87 spid68Query Memory Manager: Grants=0 Waiting=0 Maximum=155151 Available=155151
2009-03-28 21:56:35.87 spid68Global Memory Objects: Resource=1571 Locks=53 ...
2009-03-28 22:00:04.58 backupLog backed up: Database: dbPC, creation date(time): 2003/04/22(13:42:34), first
2009-03-28 22:15:01.53 spid73WARNING: Failed to reserve contiguous memory of Size= 65536.
2009-03-28 22:15:01.55 spid73Query Memory Manager: Grants=1 Waiting=0 Maximum=155604 Available=155533
2009-03-28 22:15:01.55 spid73Global Memory Objects: Resource=1571 Locks=116 ...
2009-03-28 22:15:01.55 spid73Dynamic Memory Manager: Stolen=2285 OS Reserved=1504 ...
2009-03-28 22:15:01.55 spid73Procedure Cache: TotalProcs=101 TotalPages=668 InUsePages=13
2009-03-28 22:15:01.55 spid73Buffer Counts: Commited=188588 Target=208688 Hashed=179723...
2009-03-28 22:15:01.55 spid73Buffer Distribution: Stolen=1645 Free=6552 Procedures=668...
2009-03-28 22:15:01.63 spid73WARNING: Failed to reserve contiguous memory of Size= 65536.
2009-03-28 22:15:01.64 spid73Query Memory Manager: Grants=1 Waiting=0 Maximum=155604 Available=155533
2009-03-28 22:15:01.64 spid73Global Memory Objects: Resource=1571 Locks=116 ...
2009-03-28 22:15:01.64 spid73Dynamic Memory Manager: Stolen=1801 OS Reserved=1504 ...
2009-03-28 22:15:01.64 spid73Procedure Cache: TotalProcs=12 TotalPages=21 InUsePages=0
2009-03-28 22:15:01.64 spid73Buffer Counts: Commited=188588 Target=208688 Hashed=179723...
2009-03-28 22:15:01.64 spid73Buffer Distribution: Stolen=1808 Free=7036 Procedures=21...
2009-03-28 22:15:01.67 spid73WARNING: Failed to reserve contiguous memory of Size= 65536.
2009-03-28 22:15:01.69 spid73Query Memory Manager: Grants=1 Waiting=0 Maximum=155604 Available=155533
March 30, 2009 at 1:55 am
How much memory does your server have? What OS are you running on? And what is your SQL Server's Min/Max memory setting right now?
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 30, 2009 at 2:10 am
Hi Mohit,
We using windows 2000 and 3407, 388 KB of RAM.
For memory setting, Min = 0, Max = 3328 MB
March 30, 2009 at 12:30 pm
kienway (3/30/2009)
Hi Mohit,We using windows 2000 and 3407, 388 KB of RAM.
For memory setting, Min = 0, Max = 3328 MB
Okay few more questions:
1) what do you mean by 3407, 388KB (MB?) Memory? Is it Total ram is 3795MB (3.7GB)?
2) In your SQL Server are you using AWE? In your server boot are you using /3GB or /PWE switch?
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 30, 2009 at 11:52 pm
Yes. Total ram is 3795MB (3.7GB).
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
awe enabled 0 1 0 0
How to check server boot using /3GB or /PWE switch?
March 31, 2009 at 12:48 am
Hello Kienway,
If your server is not production server just alot fixed memory of 2.0 GB to sql server and check if you are getting the same errors?
NOTE: Restart is required to take new settings.
HTH
Cheers!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 31, 2009 at 12:03 pm
To check for 3GB or sorry PAE (Physical Address Extention) not PWE switch are enabled in the BOOT.ini file on the root of C:\.
Those errors are saying your OS was under memory pressure; I wanted to confirm if these settings exist. But as pointed out setting you max memory to 2GB should help release the pressure.
Thanks.
Ref:http://support.microsoft.com/kb/274750
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 31, 2009 at 9:22 pm
Can I change the setting to production server as this is not a development server? Is there any impact?
The setting is as per below.
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server" /fastdetect
March 31, 2009 at 11:58 pm
kienway (3/31/2009)
Can I change the setting to production server as this is not a development server? Is there any impact?The setting is as per below.
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Server" /fastdetect
Okay so you are not using 3GB or PAE switch. As to recommendation? For now I would say set you maximum memory usage in SQL Server to 2.0GB; with standard edition and no switch it should not use more then 1.7GB anyways.
Is there any other processes running on the server? Check performance counters to see if you have any memory pressure...
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 1, 2009 at 3:32 am
Hello Kienway,
The impect on the production server will be downtime. During this activity nobody will be abot to access the database and Applications running on the respective database won't be available.
Important: Take the downtime first with customer before changing memory configuration.
Cheers!
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 1, 2009 at 5:02 am
Nobody else has said this yet, but your Errorlog sample does not show any sign of a memory leak.
What it does show is that SQL Server is unable to get the memory it needs to do some work, which is a different problem.
My guess is that your server has 4GB ram installed and has a 'lights out' management board. This would reduce the memory available to Windows by about 300MB to give the 3.7 GB you report.
Your current setup allows SQL Server to use a theoretical maximum 2 GB memory, but the practical maximum is 1.7 GB. If you add the /3GB switch to boot.ini and reboot, you can get a practical maximum of about 2.4 GB. However, this may be reduced if you have other programs (e.g. anti-virus) running.
However, if your workload has grown a lot since SQL Server was installed, you may be reaching the limit of what can be done with 32-bit Standard Edition SQL Server 2000. In this situation you should look at upgrading to 64-bit SQL Server 2005 or 2008.
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply