You remember my allocated memory alert?
Got it under control thanks to a newsgroup posting by David Copeland.
First off if you are seeing messages like this:
Allocated Memory Alert on DOMAIN
A large amount of memory is committed to applications and processes. Consistently high memory usage can cause performance problems.
To determine which processes and applications are using the most memory, use Task Manager. Monitor the activity of these resources over a few days. If they continue to use a high level of memory and are less critical processes or services, try stopping and then restarting them.
You can disable this alert or change its threshold by using the Change Alert Notifications task in the Server Management Monitoring and Reporting taskpad.
And while at first I bumped up the health monitor, I noticed that there was one of the sqlserver.exe processes that was right underneath Store.exe [Exchange is our normal memory sucker which is perfectly normal] and was sitting at 465,476K.
So David posted in to the poster, to check to see exactly WHAT sql instance was being the problem child, to go to a command prompt and type in tasklist /svc
Then go into Task Manager, click on the Processes tab [click on view/select columns options to make sure the PID [process identifier] option is selected and look for the different process IDs to see which process ID is using the memory.
PID is 672 in the one tracks to MSSQL$SBSMONITORING in the other. [see it?]
So then David said to do the following at a command prompt:
osql -E -S YOURSERVERNAME\sbsmonitoring [hit enter]
[you’ll now enter like a command screen]
sp_configure ‘show advanced options’,1 [hit enter]
reconfigure with override [hit enter]]
go [hit enter]
sp_configure ‘max server memory’, ### [enter the value of the max… I’m trying 70 … I just guessed at this from the poster in the newsgroup] [hit enter]
reconfigure with override [hit enter]
go [hit enter]
which looks like this:
And after I did that, the task manager now looks like this:
And as you can see PID 672 shrunk down and commit charge down in the bottom corner is much less
So I put my health monitor back to where it was before with looking to a max value for allocated memory of 2147483648.