I ran the Cleanup Wizard that comes with WSUS 3.0 on a server today, and watched Task Manager as the SQL 2005 server app kept using more and more memory and CPU resources. By the time it finished, the SQLServr.exe task had grabbed 671,212K of memory!
I knew of Susan’s blog for adjusting the SQL resources for SQL 2000 and have used it on my other SBS servers. But I did not know if it would work with SQL 2005. Well, I stumbled across Jesper’s blog where he had the same issue and question. Apparently, the command line sequence that Susan posted seemed to work for Jesper. And it appears to have done the trick for me as well.
C:\Program Files\Microsoft SQL Server\80\Tools\Binn>osql -E -S <server name>\MICROSOFT##SSEE
1> sp_configure ‘show advanced options’, 1;
Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
1> sp_configure ‘max server memory’, 256;
Configuration option ‘max server memory (MB)’ changed from 2147483647 to 256. Run the RECONFIGURE statement to install.