MS SQL Server : limit memory useage

[riwa]

Hi,

We use a programme which came with a MS SQL Server (8.0.760).
I noticed that sqlserv.exe consumes a lot of memory, so much that there is no memory left.

I was wondering if it is possible to limit the amount of memory that sqlserv.exe uses. We don't have any management tools for mssql so I was hoping it could be done using a commandline parameter.

I already contacted our supplier but they don't know this and they say that microsoft wasn't willing to help.

 

[JamesLean]

Yes, SQL Server will eat as much memory as you can throw at it, and by default this is all that is available on the server.

You can limit the max memory though:

- right-click on the server in Enterprise Mgr and go to Properties
- go to the Memory tab
- select the minimum and maximum amounts it should use

--James

 

[riwa]

Problem is that we don't have the enterprise manager.
MSSQL was installed with a program so no tools available.

I guess enterprise manager is not available for legal download somewhere ?

 

[SQLSister]

No Enterprise Manager is not, however you can buy developer tools for around $50.00 and get it and query analyzer. This would be a good idea.

Be aware that limiting memory is a bad thing in SQL Server; it will decrease performance. SQL Server is designed to use all avaliable memory for performance reasosn. This is why the preferred option is to run it on a server with no other applications on it.

Since you don't have Enterprise Manager, you may not have set up backups yet either, so I'll warn you that the ttransaction log will also grow until it takes up the entire hard drive unless you back it up frequently or set the database to simple recovery mode. Just thought I'd mention this one before you reached that stage.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[mrdenny]

You can set the max memory via the isql command line tool.

sp_configure 'Max Server Memory', '1024'
reconfigure
go

This will set the server to use 1 GB of memory as the Max. Change the 1024 to what ever setting you want in MB. Standard edition will use up to 2 GB of memory max.

You'll want to give SQL as much memory as possible to keep it running nice and fast.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)