Tuesday, August 7, 2012

SBS2011 Limiting SQL Memory for WSUS and SBS Monitoring

SQL Server Configuration Manager for SQL2008R2

View Process IDS for those SQL instances.

clip_image002

Open Task Manager go to services and sort by process ID to locate SQL instance:

clip_image004

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] (Case Sensitive!)

[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:

clip_image005

http://msmvps.com/blogs/bradley/archive/2005/02/04/34984.aspx

Our destination SBS 2011 has an SQL (S-Q-L) process that was taking up a whopping 5GB of RAM:

clip_image006

Since this was a relatively fresh install of SBS 2011 we needed to find out what SQL setup was using so much memory.

  1. Open Task Manager –> Processes tab.
  2. View –> Choose Columns –> PID, Memory, etc. (Our SBS 2011 Setup Guide has the customizations)
  3. Start –> CMD –> Right click and Run As Admin.
  4. tasklist /svc [Enter]
  5. Scroll through the resulting list looking for the PID.
    • clip_image007
  6. Start –> All Programs –> Microsoft SQL Server 2008 R2 –> Right click and Run as Admin on SQL Server Management Studio.
    • Server type: Database Engine.
    • Server name: \\.\pipe\mssql$microsoft##ssee\sql\query
      • Copy and paste the above.
    • Authentication: Windows Authentication.
  7. Click Connect.
    1. clip_image008
  1. Right click the root node (\\.\pipe\mssql$microsoft##ssee\sql\query) and left click on Properties.
  2. Click Memory.
  3. Change the amount of memory that WSUS can use. On this particular SBS 2011 VM we have 16GB of RAM allocated. So, we will set the amount of RAM to 1GB.
    1. Before:
      • clip_image009
    2. After:
      • clip_image010

Our WSUS SQL memory usage was now a lot more reasonable:

clip_image011

clip_image012

 

 

Credit Sources:

http://blog.mpecsinc.ca/2011/07/sbs-2011-wsus-sql-memory-usage-is-very.html

http://msmvps.com/blogs/bradley/archive/2005/02/04/34984.aspx

1 comment:

  1. Thank you, exactly what i was looking for

    ReplyDelete