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.
- Before:
- After:
- Before:
Our WSUS SQL memory usage was now a lot more reasonable:
UPDATE 2011-07-16: Tweaked the steps for a typo as per Arcon’s comment.
Philip Elder
MPECS Inc.
Microsoft Small Business Specialists
Co-Author: SBS 2008 Blueprint Book
*Our original iMac was stolen (previous blog post). We now have a new MacBook Pro courtesy of Vlad Mazek, owner of OWN.
15 comments:
I've added this into my setup guide (that borrows heavily from your awesome setup guide).
You got a typo.
Right click the root node (\\.\pipe\mssql$microsoft##ssee\sql\query) and \\.\pipe\mssql$microsoft##ssee\sql\query.
Should be I think
Right click the root node (\\.\pipe\mssql$microsoft##ssee\sql\query) and PROPERTIES
Anyway - great great job you're doing - by sharing all this knowledge with us - what else could we please for ?:) You helped me with many many things:) Keep up the good job guys and inspire us to search for more and more:)
Arcon,
Good catch! :)
My eyes must have been crossed when I was putting that together.
Philip
Following your steps, trying to right click on SUSDB to get to properties, I get the following error:
Property Owner is not available for Database '[SUSDB]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
Any ideas?
I am having the same problem
Property Owner is not available for Database '[SUSDB]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
Any ideas?
Philip, thank you for this info.
my server is a vm and limited to 6 GB of memory so if sql server is using like 3 to 4 GB its noticeable wen working with the server.
Limiting the memory worked immediately and my server is a lot more responsive.
@anonymous users, Right click the root node (\\.\pipe\mssql$microsoft##ssee\sql\query) and PROPERTIES not SUSDB!!!!
I am getting the same error even running as ADMIN.
Property Owner is not available for Database '[SUSDB]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
I'm sure you figured it out by now, but you don't have to call up the properties for the SUSDB node but for the root, i.e. the properties of \\.\pipe\mssql$microsoft##ssee\sql\query
The screenshot that has SUSDB selected is a bit confusing, I give you that.
What impact will this have on the sql server?
Yes!! What a tip, thank you! The SBS is useable again. After a year it feels like the SBS 2011 machine is finally being configured as it should be. The big question is why Microsoft didn't fix this in the standard SBS installation.
Just a note to say thanks. Easy to understand post that solved my issue. Keep posting!
Used your steps so sql would only use 1024mb of ram. It helped.
Thanks!
Thank you, we still rely on our SBS 2011 server (running on Dell server with 24G RAM). Your post has made our little part of the world better.
Worked like a champ! I had 2 SQL non-critical databases eating up the majority of my RAM. I used these instructions and my server is back to its old self again.
Many thanks!!
Hi --
I had this same SBS database chewing up a ton of physical RAM on an SBS 2011 box. I followed these instructions to reduce the maximum memory to 2 GB (the machine has 24 GB of RAM). I also reduced the "minimum memory per query" from 1 GB to 0. After a lot of "Executing" time, this instance's physical RAM consumed rose from a little over 5 GB to close to 7 GB. Any ideas?
Post a Comment