SBS2011 Limiting SQL Memory for WSUS and SBS Monitoring
SQL Server Configuration Manager for SQL2008R2
View Process IDS for those SQL instances.
Open Task Manager go to services and sort by process ID to locate SQL instance:
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:
Our destination SBS 2011 has an SQL (S-Q-L) process that was taking up a whopping 5GB of RAM:
Since this was a relatively fresh install of SBS 2011 we needed to find out what SQL setup was using so much memory.
- Open Task Manager –> Processes tab.
- View –> Choose Columns –> PID, Memory, etc. (Our SBS 2011 Setup Guide has the customizations)
- Start –> CMD –> Right click and Run As Admin.
- tasklist /svc [Enter]
- Scroll through the resulting list looking for the PID.
- 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.
- Click Connect.
- Right click the root node (\\.\pipe\mssql$microsoft##ssee\sql\query) and left click on Properties.
- Click Memory.
- 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.
Our WSUS SQL memory usage was now a lot more reasonable: