Large Amount of Memory crittical Alert on sqlservr 1

[attrofy]

We are running W2k3 SBS, which includes the SQL Server. We dont use Sql Server, and I have never configured it. However, every day, in my daily logs I am given a critical alert about ALLOCATED MEMORY, and too much memory being used. In checking the logs, I see sqlservr is constantly the memory hog.

Top 5 Processes by Memory Usage
Process Name - ID Memory Usage 

sqlservr - 1096    1,664 MB      
 
store - 3528         338 MB      
 
services - 732        87 MB      
 
Rtvscan - 2908        50 MB      
 
w3wp - 7420           50 MB

However, I can only find two process threads running on the server that have anything to do with SQL. sqlmangr.exe, and I forget the other one at the moment (as I dont currently see it running). Both of these are being used by the Exchange server, so ending them seems to be problematic. We dont get that many emails, and even though we are running an intranet system with the Exchange server, it doesnt get much if any usage at the moment. THese problems go away after a reboot - for a few days - then the same process begins its memory leak again. Is there something else that could be hogging so much memory bandwidth that I am overlooking? We have a pretty limited scope server - at the moment is basically just acting as DC, and hosting mail service for two users. As the problems get straight, we will bring more online. But with 4Gb of memory, having almost a quarter of it dedicated to a service we arent even using just seems to be a configuration problem somewhere. Oh, Sql Server Service Manager is stopped - or more correctly - Not Connected. Any thoughts - thanks in advance for the help.

 

[mrdenny]

If you are sure that you aren't using the SQL Server you can stop and disable the service. It should be listed as MS SQL Server.

If you are using it you can configure it to limit it's memory use. It will default to using 2 Gigs of memory.

If you want the steps to configure it let me know.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

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

http://www.mrdenny.com

 

[milesy]

I am having the same problem. Our setup differs a little, we do use Exchange a lot and we also use the intrnet site that comes with SBS 2003. Am i correct in thinking that I cannot just stop MSSQLserver as you suggested for Attrofy??

Craig Miles, CCNA

 

[milesy]

OK try this it seemed to work for me.

Stop the service MSSQL$sbsmonitoring.

As some as I closed it my memory usage started to to fall back to a level that I expect to see being after a re-boot.

Hope this helps.

Craig Miles, CCNA

 

[adamroof]

same issues, even with SBS Monitoring disabled, but it is the main culprit along with Share Point. Double check that your SP and SBSMon database SQL agent is running its cleanup tasks.

Im currently having an issue with web traffic connections every hour on the hour, and when i blocked outbound 80 from my server, the email alerts of excessive memory allocation stopped. the sbs monitoring is currently running. i removed the block and still have yet to get that alert again.

so in a nutshell... dunno.

 

[58sniper]

If you've got SBS Premium, and you're not using SQL, rip it out. The Standard version doesn't use SQL and it works fine. And, the only difference between SBS Standard and Premium is SQL and ISA. If you're not using them, either don't install them, or pull them out.

Pat Richard, MCSE(2) MCSA:Messaging, CNA(2)

Want to know how email works? Read for yourself -

http://www.ietf.org/rfc/rfc2821.txt

 

[milesy]

Well I looked at the server agin this morning and the memory has started to rise again. SO the MSSQLserver monitor was not the culprit. If I track it down I will post an answer

Craig Miles, CCNA

 

[adamroof]

heres mine

Process Name - ID Memory Usage 
store - 4672 891 MB      
 
sqlservr - 1336 166 MB      
 
sqlservr - 1092 96 MB      
 
Cm_smex - 4936 71 MB      
 
w3wp - 7740 67 MB

id say if you dont use share point, remove it through add/remove, the sql drives sbs mon, so id say keep it.

if you arent using sql for anything else, 1.5GB is way too much, check those cleanup jobs, chek the log size, run a shrink on the database.

 

[mrdenny]

Keep in mind that SQL is a memory hog. It will take what ever memory it wants. SQL is designed to be run on a standalone server. It can take quite a lot of tweaking to get SQL to interact correctly on a server with other software running on it.

Assuming that you have SQL Enterprise Manager installed (I'm not sure what SBS installes by default) open Enterprise Manager and connect to the local server.

Right click on the server name, and click properties (at the bottom). On the memory tab you can adjust the max amount of memory that SQL will use. SQL needs at least 256 Megs of memory as a max. The more memory that you give SQL the better that it will run.

Don't adjust the minimum amount.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

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

http://www.mrdenny.com

 

[58sniper]

SQL doesn't drive SBS mon by default, since far more SBS boxes are Standard (no SQL) than Premium.

Pat Richard, MCSE(2) MCSA:Messaging, CNA(2)

Want to know how email works? Read for yourself -

http://www.ietf.org/rfc/rfc2821.txt

 

[attrofy]

Thanks all for the suggestions. I have tried most of the above suggestions with no luck (as these were the common thoughts I found googling around). However, I did have a problem with stopping sbsmonitoring with the Exchange portions of the server that control backend email delivery and other services. I believe sharepoint had something to do with that as well.

When installing the system, I never saw an option to not install SQL, MS in its infinite wisdom felt I needed it since it was part of the SBS suite.

In rechecking the services and the installed components, I see installed "Microsoft SQL Server Desktop Engine" (2 instances - one says (SBSMonitoring)the other says (SharePoint)). All of which I believe someone elluded to earlier. As stated, when stopping these services, it seems to effect our Exchange server, so I am a bit "skeert" to try and uninstall these. The only other installed component is "Microsoft Windows SharePoint Sevices 2.0"

As for Mrdenny's explanations, I think I am missing a component somewhere. As for running services, the only thing pertaining to SQL is SQLsbsMonitoring, SQLSharepoint, and MSSQLServerADHelper - whatever that is. That is the closest thing I found MS SQL SERVER. In right clicking the tray icon for SQL, it provides limited options and errors when trying to start and stop the service. It always tells me it is connected to "// " with no name. So anything I try to configure, or stop, it either errors out, or tells me it "couldnt find the service on //" - obviously it is looking for a server that it should be running on. Any thoughts in this area?
Thanks again all for the help - keep the suggestions coming. It is quite possible I dont have something configured properly, as the only setup for SQL has either been automated through Exchange services, or what comes configured default from MS. It is possible I need to configure SQL in order to stop it.

 

[mrdenny]

You can install SQL Server more than one time on a machine. Each install has it's own name and is called an instance. Each instance is totally separate from each other and know nothing about each other.

If you have what is called the default instance (which is optional) the service will be named MSSQLSERVER. If you have a named instance they will be named MSSQL$InstanceName. An an example the name of the service that runs the SQL Service for a sharepoint install will default to something like MSSQL$Sharepoint.

SQLsbsMonitoring will be the SQL Instance for the Monitoring software.
SQLSharepoint will be the SQL Instance for the SharePoint site. If you are not using Sharepoint you can safely stop and disable this service.

In order to use the SQL Server Service Manager (the little icon by the clock) you need to know the full name of the instance. The full name of the instance will be ServerName\InstanceName. If your server is ServerA and your instance is SharePoint then the full instance name is SERVERA\SharePoint. That should allow you to get to the services.

The MSSQLSErverADHelper is used if you want to register your servers in Active Directory. This can be handy if you have a very large install base of SQL Servers and you want to connect to them via DFS. For a small shop you don't need to worry about this. You'll probably see that this service is currently not running, and the start up status should be manual (unless someone has configured one or more SQL instances installed on the server to be registered with Active Directory; which isn't very likely).

If you have the full blown install of SQL Server you'll be able to find the Enterprise Manager in the Start Menu. It's under Start > Programs > Microsoft SQL Server > Enterprise Manger.

If you don't have Enterprise Manager there are some command line tools which you can use to configure your SQL Server.

As you've probably noticed SQL Server isn't exactly for the feint of heart.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

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

http://www.mrdenny.com

 

[attrofy]

Good call on all accounts - you earned a star.

I do not have the enterprise version of SQL, and had no luck logging into any instances of it. I agree SQL server is not for the feint of heart, and as such, this is the reason I have not touched anything pertaining to these services. For all intents and purposes, it appears that there are no instances of SQL running, as I cant get a list out of the drop down menus. I can type the server name in, but can't type in any of the instances. So for the life of me, I am not sure how SQL is tying up so many resources and hogging so much free memory.

I do recall finding a few command line entries in doing some web searching a while back, but if I recall correctly, I kept getting similar results that SQL was not started, etc. ANy thoughts on how a non-running service is being such a pain and resource hog? And why I keep getting daily notices about it in the server reports?

Thanks again for your help.

 

[mrdenny]

If the services aren't running the can't use resources.

Open up the services management console (start run services.msc) and see if the SQL Services are running. They'll start with MSSQL.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

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

http://www.mrdenny.com

 

[attrofy]

Here is everything under services that pertain to SQL:

MSSQL$SBSMonitoring     Started
MSSQL$Sharepoint        Started
MSSQLServerADHelper     (off)
SQLAgent$SBSMonitoring  Started
SQLAgent$Sharepoint     (off)

I forget which one of the two services effects the Exchange functions, but one prevents email from being sent or received. The other prevents the company intranet from working. The SQLAgent$SBSMonitoring is interesting, and I cant ermember if I hadve played with that service or not.

Any thoughts? Thanks again for the help.

 

[mrdenny]

OK, from a command prompt on the server run the following.

osql -S [i]ServerName[/i]\SBSMonitoring -E

This should present you will a line that says 1>
Now type in

sp_configure 'max server mem'
go

It probably has some realy large memory in the config_value column. To change that run this.

exec sp_configure 'max server mem', [i]New Max Memory Setting[/i]
reconfigure
go

For the New Max Memory Setting you may need to play with this setting a little bit to get good performance. I recommend at least 256 Megs for each one (The setting is in Megs so just enter 256 for the value).

You'll then want to connect to the other instance and configure that one as well.

Give the server a little time and the memory that SQL is using should drop. If it doesn't (SQL doesn't always like to release the memory that it's using) just restart the services. If you restart the MSSQL$SBSMonitoring service be sure to restart the SQLAgent$SBSMonitoring after the MSSQL service.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

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

http://www.mrdenny.com

 

[attrofy]

Well, we're getting warmer, but still no cigar.

When I try to configure the 'max server mem' I get an error that says:
"The configuration option 'max server mem' does not exist or it may be an advanced option."

It then lists all my possible options:

allow updates
nested triggers
remote access
default language
remote proc trans
show advanced options
remote login timeout
remote query timeout
user options
max text repl size
cross DB Ownership Chaining


I tried to show the 'show advanced options' but I might as well have been using a japanese keyboard - it was having the same effect.

So I tried connecting to the Sharepoint instance, and had the same results.

Any further thoughts?

 

[mrdenny]

ok, after you log into osql and get to the "1>" prompt run this.

exec sp_configure 'show adv', 1
reconfigure
go

Then run sp_configure with the max memory.

exec sp_configure 'max server mem', New Max Memory Setting
reconfigure
go

Then disable advanced options.

exec sp_configure 'show adv', 0
reconfigure
go

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

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

http://www.mrdenny.com

 

[attrofy]

OK, now we're cooking with gas. Just to clarify, you stated that the 'max mem setting' is in mb? The value that was configured was 2,147,483,647 - this too me looks like it is in bytes - about 2 gig worth. Can you verify that this is indeed mb? Is there any way to set or verify that info through the command prompt (which btw reminds me of the early DOS 4 & 5 days of the EDLIN command....)?

Thanks again for all your help

 

[mrdenny]

Yes it's in Megs. The default is like 2 TB. The Max possible setting is well above what Windows 32bit will support so that it will cover all possible servers.

You set and verify the setting via osql using the sp_configure command. If you use

exec sp_configure 'max server mem'
go

it will return the min, max, current_setting, active_setting values.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

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

http://www.mrdenny.com