April 8, 2003 at 5:06 pm
Hi,
I have a sql2k running on windows2k.
This sql server is quite busy everyday, the daily connections are about 500,000.
The main database is about 40 GB. I created maintenance plan to backup the database daily and the transaction log backup each hour. The server has 4 GB memory dynamically configured. It runs well for years. Recently
we run a new application on this server, then after two days all backup jobs failed.
The error message is: memory allocation failure. or there is insufficient system memory. I checked task manager. the memory usagy keeping growing till about 2031 MB,
which is the max memory sql can use, then the jobs failed. It seems to me the sql server can't release memory automatically. I must reboot the server.
Anybody knows the problem? Besides removing some applications, what else I can do to resolve the problem?
Thanks in advance.
Robert
April 8, 2003 at 9:28 pm
Even when the memory is dynamically configured, You could could use MIN and MAX settings. Try setting upto 3.5 GB for MAX. Might Work....
Regards,
Murali Damera.
.
April 9, 2003 at 5:03 am
Also, look in BOL at AWE memory and how to configure.
April 9, 2003 at 7:21 am
How much avaiable memory did you have at time your job failed? Which version SQL Server you are ruuning?
In order to allocate memory than 2GB to SQL Server, you have to use /3GB switch in your boot.ini file. And 'AWE' only works when physical memory more than 4GB.
Edited by - Allen_Cui on 04/09/2003 07:25:51 AM
April 9, 2003 at 11:36 am
Thank you all.
When the jobs failed, the available memory is about 1.5 GB.
I am running on sql2k 8.00.679 that is sp2
plus latest security patch.
For a standard sql server, can it use more than 2 GB memory? I thought the max memory can be used is 2 GB.
Thanks again.
Robert
April 9, 2003 at 11:55 am
As you already know, SQL Server standard edition can't use more than 2GB memory. But I am thinking your problem may not be just the shortage of memory that you allocated for SQL Server. Check your machine application and system log as well as SQL Server errorlog for any errors that could related.
April 9, 2003 at 3:40 pm
System error didn't show anythins else. the application log error showa: Error accepting connection request via Net-Library'SSNETLIB', Execytion continuing.
I also doubt it's not the memory problem, it must be related with our new application.
That is an Oitlicense.exe. Today I removed this application from the server, the problem has been resolved. I still have the question: This application maybe used ssnetlib Encryption. It should increase network traffic quite a lot. Why it effect the memory? I traced the task manager, when the application is running on the server, the sqlserver memory usage keep growing until about 2 GB, then the jobs failed.
When the application was moved from the server, the sql server memory usage keep growing alse until about 2 GB, Then keep it the same, the job is running. This it normal, the sql server will release memory automatically. My question is the application makes the sqlserver not release memory, why? Anybody knows? or other problem? Thanks!
By the way Allen, how to change in boot.ini file?
[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
C:\="PC-DOS"
Seems to me no where to change the memory.
Robert
April 10, 2003 at 3:33 am
When the app is runinng to you retain a high number of apparently open connections? If so thn it may be affecting the connection release. Other than that I would have to say if a vendor product you give them a nice run thru what you see as they may have an internal memory leak or connection issues causing the buffers to fill and become unavailable. To check for that possibility open Task Manager goto View->Select Columns... and add "Non-paged Pool" it should in most all cases never be higher than 2048k. If it is then you have Windows memory buffers that have data waiting to be sent across the connections and once they reach a certain level SQL will report memory unavilable in many cases). Just to let you know, I have am app I wrote that has suffered from this off and on and am still trying to narrow the issue, it usally depending on the memory dies arond 40mb in NP Pool amount. Other than that I don't have anything specific you can test for.
April 10, 2003 at 7:18 am
Here is an example.
[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 Advanced Server (3GB)" /3GB /fastdetect
April 10, 2003 at 11:14 am
Thank you Antares for your explanation. I think this is the reason for my problem. Thank you Allen also, your example is for advanced server. that 3 GB is assighned to the windows2k advanced server or to sql2k server?
Thank you again guys!
Robert
April 10, 2003 at 12:44 pm
It is for Win2K Advanced Server. See 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" /3GB /fastdetect
C:\="PC-DOS"
April 10, 2003 at 3:39 pm
I mean if it is for win2k advanced server then only 1 gb for sql server.So if I want to give sql server 3 GB, then just input 1 GB for win advanced server as below? (total is 4 GB)
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 advanced Server" /1GB /fastdetect
C:\="PC-DOS"
Robert
April 11, 2003 at 6:52 am
Ther is no such switch /1GB. /3GB actually allocates 1GB for OS and give the rest memory to applications such as SQL Server. If you don't specific /3GB in boot.ini, System will allocate 2GB for itself and the rest for applications by default.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply