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