I log to the msde database for ISA 2004. I noticed that the SQL server process is eating up about 450MB of memory. Is this normal? Is there anything I can do to lessen this? I do not want to add more memory unless I have to. Thanks!
Posts: 5
Joined: 7.Feb.2004
From: UK
Status: offline
You can stop sql server hogging memory, but your going to need some client tools to do it. You can do this easily by using SQL Enterprise manager. This comes with all retail versions of sql server. Memory allocation is dynamic out of the box. That means the SQL server engine determines that your database requires that much memory to function. Be warned setting a memory allocation that is too low will cause the server to slow down significantly when responding to read requests. I'd recommend logging the requests to a separate SQL server and stopping the sql serevr service on your ISA server.
You can add more memory to the system but MSDE will probably eat that up too. I have an ISA server with 2GB of memory and the sqlservr process would consume 1.7GB after only 3 days of uptime. There's obviously a memory leak problem with MSDE. I have uninstalled it until MS comes out with a fix or a patch.
Just for reference, we have a 4GB ISA system with the /3GB switch utilized, and the sqlservr process consumes ~1.8 GBs routinely. Doesn't bother us much though cuz 2.2 GBs are still available.
First of all apologies for bouncing an old thread, but I've just had this problem myself and may have found a solution.
There is a 'max server memory' option in SQL and MSDE, with the default being 2147483647Mb! However it is possible to change this using the osql utility installed with MSDE. I haven't run this long enough on our server to test, but believe it will work according to Microsoft's documentation.
It appears that by default SQL Server, and MSDE will grab as much memory as they can since they assume that they are on a dedicated database server. MS seem to imply that the 'max server memory' parameter should be set if the server isn't exclusively a SQL server here. Microsoft also have more details on the max and min memory parameters.
I then found this page with details of the scripts to configure these paramaters, and this knowledgebase article explaining how to use osql. With these two articles, it is possible to reset the 'max server memory' parameter, with the change taking effect immediately.
You will need to know the SQL instance name to use osql and this can be found in the Services list. In my case, the service was named MSSQL$MSFW and MSFW is the instance name.
Two scripts are shown below, one to check your server's max memory setting, and one to change the value.
To view the memory on your server, copy the following commands to a text file (eg. c:\checkmemory.sql):
code:
USE master EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE
USE master EXEC sp_configure 'max server memory (MB)'
USE master EXEC sp_configure 'show advanced options', 0 RECONFIGURE WITH OVERRIDE
You can now check the 'max server memory' setting on your server by running the following from a command prompt on the server:
quote:Originally posted by myxiplx: First of all apologies for bouncing an old thread, but I've just had this problem myself and may have found a solution.
There is a 'max server memory' option in SQL and MSDE, with the default being 2147483647Mb! However it is possible to change this using the osql utility installed with MSDE. I haven't run this long enough on our server to test, but believe it will work according to Microsoft's documentation.
It appears that by default SQL Server, and MSDE will grab as much memory as they can since they assume that they are on a dedicated database server. MS seem to imply that the 'max server memory' parameter should be set if the server isn't exclusively a SQL server here. Microsoft also have more details on the max and min memory parameters.
I then found this page with details of the scripts to configure these paramaters, and this knowledgebase article explaining how to use osql. With these two articles, it is possible to reset the 'max server memory' parameter, with the change taking effect immediately.
You will need to know the SQL instance name to use osql and this can be found in the Services list. In my case, the service was named MSSQL$MSFW and MSFW is the instance name.
Two scripts are shown below, one to check your server's max memory setting, and one to change the value.
To view the memory on your server, copy the following commands to a text file (eg. c:\checkmemory.sql):
code:
USE master EXEC sp_configure 'show advanced options', 1 RECONFIGURE WITH OVERRIDE
USE master EXEC sp_configure 'max server memory (MB)'
USE master EXEC sp_configure 'show advanced options', 0 RECONFIGURE WITH OVERRIDE
You can now check the 'max server memory' setting on your server by running the following from a command prompt on the server:
Posts: 5
Joined: 19.Jan.2005
From: England
Status: offline
Hi, I'm sorry to add to this again, but I'm also having sqlsrv.exe memory problems on our ISA 2004 server. I want to use the above script to limit the sqlsvr.exe memory usage to 256MB - i'm a little cautious though because I only have a live server to do this on. Can anyone confirm the above script works ok, and what happens when the sqlsvr.exe memory usage reaches it's limit - are there any side effects?Thanks
I can confirm that the script from "myxiplx" will work because I tried it on our live server and we have had no noticable effect. We have run it like this for a couple of months. I am sure the solution that Tom Shinder found will also work. It seems like the same script. The way I did it you had to install the enterprise manager from the full SQL server and Tom's way uses a utility built into the MSDE version.
RE: sqlsvr is a memory hog - 17.Oct.2005 4:06:00 PM
Guest
Hello: If you don't want to use OSQL.EXE or the SQL Query Analyzer to modify these memory settings, there's an easier-to-use script for this at www.ISAscripts.org named "ISA_MSDE_Max_Memory.vbs".
Posts: 1
Joined: 24.Oct.2005
From: Chicago, IL
Status: offline
Yes, SQL is a pig, but I think there is an underlying reason why SQL is taking all of this memory. I setup a database for my System Administrator to log ISA activity and noticed that the create table scripts he sent me didn't have any primary keys defined.
Without a primary key, MSSQL must do a full table scan each time it needs to access a record. Worse yet, is there are two defined indexes on the table. Without a primary key present, each time there is an insert (which is the majority of the transactions here) the engine has to scan the entire table to determin where to put the new record. This is extremely CPU intensive and uses up memory. Specially as the table grows.
For our table, FirewallLog and WebProxyLog I created a 'dummy' key called xKey with a type of integer and an identity field (auto increment). I then made this column the Primary Key.
Once we have more data in our logging database, I will see if I can create a true primary key from the data itself, which is always a better practice and more efficient.
Another thing to consider is to set transaction logging for the database to simple. I doubt that any of you will be rebuilding the database from transactions logs, so why spend the cpu cycles creating them?
|
My Subscription |
My Forums |
Address Book |
Member List |
Search |
FAQ |
Ticket List |
Log Out
Printable Version
All Forums >> [ISA Server 2004 Firewall] >> Logging and Reporting >> sqlsvr is a memory hog 
sqlsvr is a memory hog - 
![[Wink]](/image/smiles/wink.gif)
![[Frown]](/image/smiles/frown.gif)
RE: sqlsvr is a memory hog - 
New Messages
No New Messages
Hot Topic w/ New Messages
Hot Topic w/o New Messages
Locked w/ New Messages
Locked w/o New Messages
Post New Thread