«

»

Mar 16 2013

Sqlservr.exe high memory usage on SBS2008

You will sometimes find that your Windows Small Business Server 2008 will become slow and even sometimes reboot or hang. This can be caused by SQL Server that plays an important part in reporting and other functions on the SBS2008 server and that the Sqlservr.exe high memory usage will cause slow and unresponsive functions.

If you look in the Task Manager you will see that the Sqlservr.exe process will accumulate memory as long as there are still memory available. This is because by default there is no limit on the amount of memory that SQL Server is allowed to use.

I will show you how to change this. These settings will not have any effect on the reporting services and other software that is using SQL Server. It might speed up your SBS2008 and also might resolve your server rebooting or hanging from time to time.

Here is how to change your SQL Server memory usage limit to 512kb. I normally change this to either 512kb or 1024mb depending on the amount of memory your server has installed.

–          Open an COMMAND PROMPT (Make sure you have full Administrator rights)

–          Type: osql -E -S \\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

–          At the SQL Prompt type the following:

1> sp_configure ‘show advanced options’,1
2> reconfigure with override
3> go

–          The following message will be displayed:

Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.

–          Then type the following:

1> sp_configure ‘max server memory’, 512
2> go

–          The following message will be displayed:

Configuration option ‘max server memory (MB)’ changed from 2147483647 to 512. Run the RECONFIGURE statement to install.

–          Then type:

1>  exit

Reboot the SBS2008 server and your Sqlservr.exe will be more stable and the high memory usage problem will be resolved.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*