Small Business Susan

Allocated Memory Alert Revisited

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. 



15 comments ↓

  • #   David Schrag on 02.05.05 at 7:49 am     

    There’s a typo in the instructions above. Where it reads

    spconfigure ‘show advanced options’,1 [hit enter]

    it should be

    sp_configure ‘show advanced options’,1 [hit enter].

    How did you decide that 70 was the right number to use?


  • #   Susan on 02.05.05 at 8:23 am     

    From the original posting in the newsgroup and the ranges of memory he was seeing. I’ve asked for the offiical value and will post in the blog.

    Right now it’s a guess.


  • #   Rich Lusk on 02.19.05 at 5:25 am     

    What if the sqlserver.exe PID is pointing to Sharepoint? That’s what is happening to one of my client’s server at 417,640KB.

    How do we know what is normal memory useage and what is not?


  • #   Susan on 02.19.05 at 7:07 am     

    Let me check… I’ll get back to you.


  • #   Rich Lusk on 03.04.05 at 5:37 am     

    Susan, did you find out anything about the allocated memory alert I mentioned?


  • #   Fionnbarr Kennedy on 06.27.06 at 7:58 am     

    I’m just a bloke who has to run his network while doing the day job. Tried this after much troubleshooting – I guess its something should be pre-configured in SBS 2003 – there must be lots of people wondering why their server’s running slow! Thanks v much for the suggestion!


  • #   Jeff Stillwell on 07.10.06 at 10:02 am     

    Thanks for posting this solution! It seems to be the trick for 2 of our clients who are using ML350′s with dual Xeon procs and 4gb RAM!

    Jeff


  • #   Cary Hetzel on 07.19.06 at 3:08 pm     

    Wow! Thank You so very much!! After following the instructions our sqlservr.exe went from 775,460 K to 111,556 K!!

    That’s like 1/7 of what it was!! No wonder our server was starting to lag a bit!!

    Is there anything that can help with the store.exe?? I know this is eats quite a bit of memory as well.

    Also, since this is now eating much less RAM, will this effect SQL’s performance?? Right now we aren’t really using it, but we have a new ERP that runs on .NET and uses SQL, will this negatively affect how our ERP runs?? Or… will the system adjust to meet the needs?

    If it’s really bad, and negatively affects our ERP, how can I switch it back?


  • #   Kjetil on 07.24.06 at 3:44 am     

    Brilliant! Thanx a lot!

    Please post a list over the mem values if you get them (I tried 70 and this seems to work fine for me too).

    Best regards
    Kjetil


  • #   Daryl Sensenig on 08.23.06 at 7:52 am     

    Thanks, this looks like a great solution. However, when I type “osql -e -s SERVERNAME\sbsmonitoring” I get this: “Error: No user selected. Try -u or -e switches”
    Any suggestions?


  • #   Marco on 08.24.06 at 8:02 pm     

    Try it with a capital -E -S


  • #   Online Pharmacy on 08.30.06 at 6:17 am     

    Quiet cool , but

    Please post a list over the mem values if you get them (I tried 70 and this seems to work fine for me too).


  • #   Phil Gillespie on 09.04.06 at 7:46 am     

    Have applied this to over 20 servers now and works well.

    The MSFW also seems to run away with memory and seems to work on this also.


  • #   Henry Tjham on 09.07.06 at 11:25 am     

    Had this exact problem last month with SBSMONITORING, did everything suggested, problem solved. Thanks SBS Diva.

    Yesterday, got the same Allocated Memory Alert again, this time caused by WSUS (PID points to MSSQL$WSUS). I used the same commands to set the maximum memory to 70 MB. Simply replace SBSMONITORING with WSUS in the commands.

    Then I tested WSUS’s queries on Updates, Computers, and Reports. Everything ran fine and just as fast.

    ——————————————
    Extra info: You can check the maximum memory allocation for your processes by using the SQL commands provided in the following KB: http://support.microsoft.com/kb/909636. The KB also gives you the SQL commands that sets the maximum limit, it does exactly the same thing as the CMD commands provided by SBS Diva.

    ——————————————
    Keywords (to help searches to point here): WSUS Allocated Memory Alert WSUS MSSQL$WSUS


  • #   Brandon on 10.30.06 at 1:56 pm     

    I followed this on a SBS 2003 server which was running high on SBSMONITORING. After a month, the error came back, and I re-ran. So far it’s running a lot less, something like 90MB.

    Has anyone seen it come back like I have? Any ideas or suggestions?