Up to Something

If you have to work in networks with any Windows products, coupled with MSDE or SQL Express you will eventually run into memory consumption problems. Apparently no GUI interface deals with it. I have seen numerous complaints on the Internet for sqlservr.exe consuming loads of memory. Some psychos recommend “uninstalling and re SBS Diva has a great article which I will here condense:

osql is the command-line tool for monkeying with MS-SQL200*.

c:\> osql -E -S MYSERVER\instancename
1>

So, first add the “Process ID” column to Task Manager. Note the PID of the offending SQL process. Next, open command prompt, and run tasklist /svc. Locate the PID matching the process, and find the name you want:

sqlservr.exe                  1972 MSSQL$SBSMONITORING
sqlservr.exe                  2020 MSSQL$SHAREPOINT

The part after the ‘$’ is the instancename. (Hopefully you already know your machinename.)

Once you have the instance, run osql as shown above.

c:\> osql -E -S MYSERVER\instancename
1> sp_configure 'max server memory',128
2> reconfigure with override
3> go

max server memory: this option is in megabytes. This will change the ‘MSSQL$INSTANCENAME’ to operate at 128 Megabytes. (When set this way, most of my instances reported between 160M and 180M when in use.)

Notes

It’s simple to list all of the configured parameters for the server, simply load osql, as shown above, and run:

1> sp_configure 'show advanced options',1
2> reconfigure
3> go
1> sp_configure
2> go

That will dump all the configured options. It of course enabled advanced options.