SBS 2K3 RTM SP1 R2 Premium - Post install must do - Tame SQL Memory Usage

As soon as the ToDo list is done, and one has installed the Premium Technologies, one must reconfigure the maximum amount of memory that some of the SQL Server and MSDE instances use.

Otherwise, we end up with this:

A gigabyte in use for one SQL instance as well as Allocated Memory messages in your Outlook folder for that client!

BTW, I generally install a minumum of 3 GB in all of our SBS servers. Once everything is configured, I change the Allocated Memory Alert threshold to just over the installed physical memory level. Thus, I won't have to do it later after receiving the continuous e-mail messages. :D

To tame the SQL instance's memory usage do the following:
Right click on your taskbar and bring up the Task Manager.
Click on the Processes Tab.
Click on the Mem Usage category to sort by it, then click it again to bring the highest ones to the top.
If PID is not the second column in your view, click on view and select columns.
Put a check mark beside PID.
Click OK.
Note the PID of the offending SQL instance.
Now, click:
Start-->run-->cmd-->enter.
tasklist /svc
Scroll back up until you see the PID in the list.
To the right of the PID, will be the appropriate instance to go after.
The following screen shot has the rest:

In this case, the offending instance belonged to the Firewall MSDE.

So, we proceed at the command line as follows:

osql -E -S mysbsserver\msfw
sp_configure 'show advanced options',1
reconfigure with override
go

You will get a message that the option has been changed from 0 to 1.

Then, on to:

sp_configure 'max server memory',128
reconfigure with override
go

You will then get a message indicating the maximum allowable memory for that instance has been changed from 2 GB to 128 MB.

It is pretty kewl to watch the memory drop from over a gigabyte to the 128 MB mark almost instantaneously!

Exit out of the osql command shell by typing:

exit [enter]

Done!

For all of our new installs, I place memory restrictions on the following instances by default:

• MSFW
• SBSMonitoring
• WSUS

If the server has less that 2 GB, I tend to be a lot more restrictive than 2 GB to 3 GB, and a lot less restrictive for installations with more than 3 GB of RAM.

Thanks to Susan Bradley's posts here and here where I originally discovered how to deal with this issue.

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists