Saturday, June 26, 2010

SBS 2008 Sqlservr.exe High Memory Usage

Open an Administrative Command Prompt.

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

Now at the SQL prompt, type:

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

The screen displays:

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

Type:

1> sp_configure 'max server memory', 512
2> go

(NB The RAM usage of 512Mb is a suggestion, use whatever is appropriate in your environment.)

The screen displays:

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

Type:

1> exit

Restart the service, or reboot the server. The database instance may be opened in the management applet using the above notation.

9 comments:

  1. hats of for you Daniel B.
    such a good, short, and comprehensive description of using high memory..
    keep it up

    ReplyDelete
  2. Thanks! - Worked like a Charm

    - SPN

    ReplyDelete
  3. Great Man, good solution.

    ReplyDelete
  4. This worked for me too. Thanks Daniel

    ReplyDelete
  5. If sqlservr.exe is using huge amount of memory there is a reason behind. Forcing it to use less memory doesn't solve the problem.
    You should check files in C:\WSUS\SUSDB\UpdateServicesDbFiles as they are probably huge.

    ReplyDelete
  6. Wow, so simple, yet so effective
    Kudos

    ReplyDelete
  7. but i have 2 sql proccess. one of them is x32. it solved only in one proccess. how can i change the ather one?
    it's sbs server 2008

    ReplyDelete