monitoring sqlservr.exe

[alan12345]

Is there any job I can run that alerts me if sqlservr.exe memory usage memory usage gets really big?

Thanks,
Alan

 

[yelworcm]

You can use perfmon to trigger an executable or batch job, but SQL Server is supposed to take up a lot of memory.

 

[alan12345]

How can you use perfmon to trigger an executable or batch?

thanks,
Alan

 

[yelworcm]

Expand the Performance Logs and Alerts section, and make an alert.

 

[mrdenny]

You can also configure SQL Server to not use all the available memory. You should set it to leave at least 512 MB for the OS (more if you are running other Apps on the server besides SQL).

As yelworcm said SQL is designed to use as much memory as is available.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com

 

[purefreshair]

say mrdenny,

How do you configure SQL Server to not use all the available memory? I have a case where the server has 2GB of ram in it; sqlsevr.exe is using nearly 1.5GB. The server also has an app on it that will be accessed/queried by several users.

- Kevin

 

[mrdenny]

You can use Enterprise Manager or the procedure sp_configure.

In Enterprise Manager right click on the server, and select properties. Memory tab. Change the Maximum server memory (MB) to the most memory you want SQL to use.
Or

exec sp_configure 'max server memory', 1024
reconfigure

Change the 1024 to the number of megs that you want SQL to use.

A restart is not needed. SQL will adjust the amount of memory is uses on the fly. If you are using more memory that what you are changing the setting to (ie you are currently using 1085 Megs and you change the max to 1024) it will take SQL a little time to get down to the 1024 limit you just let.

Denny
MCSA (2003) / MCDBA (SQL 2000)

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

http://www.mrdenny.com