Thursday, November 01, 2007

WSUS 3.0, Windows Internal Database, and Memory Usage

I'm posting this here, not because I think anyone will care, but because if I ever have to do this again I want to be able to remember it!

With the release of WSUS 3, Microsoft switched from MSDE to Windows Internal Database. I did the upgrade for a client on Tuesday, and came back to finish patching some of his systems today... lo and behold, the sqlservr.exe process was eating 1.6GB of RAM. Thanks, Microsoft.

I'd seen this before with the full versions of the SQL Server product, and was pretty sure I could fix it... till I realized that it was no longer MSDE. Initially, googling showed me how to do this with OSQL... but guess what? It wasn't present in the Binn directory where sqlservr.exe was running. Thanks again, Microsoft... I mean, why would ANYONE need to reconfigure a running instance? Let's save space and not include management tools.

After more googling, here's what I found:

1) Install the management tools for SQL Server. You can find the necessary files here: Microsoft SQL Server Native Client and Microsoft SQL Server 2005 Command Line Query Utility.

2) Run the sqlcmd utility and change the config options.

c:\Program Files\Microsoft SQL Server\90\Tools\binn\SQLCMD.EXE -S -E \MICROSOFT##SSE
1> sp_configure 'show advanced options', 1;
2> reconfigure;
3> go
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
1> sp_configure 'max server memory', 512;
2> reconfigure;
3> go
Configuration option 'max server memory (MB)' changed from 2147483647 to 512. Run the RECONFIGURE statement to install.
1> exit


Voila. No more freaking RAM for you, sqlservr.exe. :P

16 Comments:

Anonymous flatron said...

Thank you very much for the information. It was very useful.

3:30 AM  
Anonymous Leigh said...

I too found the OSQL method but needed an alternative solution. This is just the job, many thanks.

9:42 PM  
Anonymous John said...

Great Information

I just did the upgrade from WSUS 2 to v3 and it (tried) installed the 2005 db without the above tools.
The database failed to upgrade because of a permissions problem however to change the permissions you need the tools you have listed above.

10:01 AM  
Anonymous Moktarino said...

Gotta use this command with the new WSUS:

sqlcmd -S \\.\pipe\MSSQL$MICROSOFT##SSEE \sql\query –E

9:25 AM  
Anonymous Jason Shuler said...

The command to execute sqlcmd didn't work for me. I had to use this instead:

SQLCMD.EXE -E -S \MICROSOFT##SSEE

Everything else worked as specified. Thanks!

12:55 AM  
Anonymous Gerard said...

Trying this on SBS 2008 (win 2008 x64)as windows internal database is taking 2.1GB mem!!!
Can't get any of the sqlcmd.exe scripts to work. Anyone found a way?

8:06 AM  
Anonymous Jamie said...

Ran into this issue on W2K8 and, like the above poster, had problems with the supplied command lines. Found this one:

sqlcmd -E -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

12:13 PM  
Anonymous Xhan said...

Thanks a lot! Very useful information!

Especially when you need a quick WSUS install on an older server after a virus outbreak :D

7:48 PM  
Anonymous Todd said...

It's worth pointing out to SBS2008 users that your server will typically be set up to grab everything available for local distribution. In my case that led to 53GB of drivespace getting chewed and 5GB of RAM to track it. Using the WSUS console, configure your server to just track critical updates and then run the server clean up wizard. It should scale back to an acceptable level. If you drop its max RAM requirement before this you will thrash your paging file when running the cleanup.

12:37 AM  
Anonymous Gregor Streng said...

Thanks a lot, eating 1.6 GB of 4GB is just....

The command
sqlcmd -E -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query
worked on my SBS2003 with WSUS 3.0 SP2

6:07 AM  
Anonymous WdLuhn said...

I have found that the memory use builds over time, and by just restarting the sql service the memory usage returns to an acceptable amount. I have added a stop/start batch file to the scheduled tasks...

5:43 AM  
Anonymous Jesper Rex said...

I used
np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

In SQL Server Management Studio Express, on a SBS2008 Server, and was able to configure memory settings there.
Remember: You have to select"Run As administrator" when starting SQL Server management studio Express.

4:57 AM  
Anonymous Edgar Romero said...

Excellent post thank you.

I had to install the following for an R2 installation.
Microsoft® SQL Server® 2012 Native Client X64 Package
Microsoft® SQL Server® 2012 Command Line Utilities X64 Package

The following worked for me.
CD C:\Program Files\Microsoft SQL Server\110\Tools\Binn
sqlcmd -E -S np:\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

8:07 AM  
Anonymous Scott Tucker Kansas City said...

This comment has been removed by a blog administrator.

6:21 PM  
Anonymous Anonymous said...

Windows 2012 install these two packages from http://www.microsoft.com/en-us/download/details.aspx?id=29065 and http://www.microsoft.com/en-us/download/details.aspx?id=36434
Microsoft® SQL Server® 2012 Native Client X64 Package
Microsoft® SQL Server® 2012 Command Line Utilities X64 Package

Then the command is, and then follow the instructions in the blog post.

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\sqlcmd.exe -E -S -S np:\\.\pipe\MICROSOFT##WID\tsql\query

1:42 AM  
Anonymous charlie said...

Thank you. Works great!

8:31 AM  

Post a Comment

<< Home