Chris,
Yes, you should have to restart from what I am guessing. Take the amount of physical RAM you have and subtract a GB and how much do you have left? Enough to run everything else on your server other than SQL Server. And Windows can only see so much RAM if you have 4GB on a 32-bit system. Only ~ 3.2GBs
There is no such thing as a "memory leak." SQL is doing exactly what it is supposed to, and the poor guy always gets blamed for everything.
SQL Server wants memory. It uses memory to create and hold it page files. Page files and processes are used by SQL to remember (memory) frequently used paths and objects from users. As more users read and write to the databases, SQL remembers the frequent ones and pages are built up making it more efficient. When you reboot, SQL loses all that memory and becomes less efficient. But, in return, all the memory it has stored (1024MB) gets released so the OS and other processes can use it.
Basically, SQL stores all the information in buffers. If you configure SQL correctly and allow it as much memory as possible, then it performs better as there will be less page swaps from Buffer to disks which results in less disk Input/Output.
Page swaps happen when the required page is not available in the buffer and hence it will take that page or query from SQL Server data files (on disk) in order to accommodate the pages. When that page is placed into buffer, it will flush off older pages.
If you reboot, the server needs to cache all required objects again from disk, but it is better than Page swaps happening frequently. Bottom line is you do not want to reboot, you want to add more RAM. RAM is cheap.
So while SQL now has no pages in its buffer, the OS can do what is is supposed to do: Be a file and application server since it now has memory. So, the main issue here is not SQL Server, it is the amount of physical memory on your machine. SQL Server Express should be configured at 512 Min and 1024 Max in MBs.
This is why other SQL Servers such as 2005 (not Express) or 2008 WITH a lot of RAM are so efficient.
Do you have your virtual memory set correctly so that SQL never takes all of the available memory. Virtual memory can be more effecient than physical memory due to its not having to be contiguous memory. The amount of virtual memory is generally 1.5 times the amount of your physical memory. I am probably way off, but I am going to guess you have somewhere between 1GB and 4GB of memory and really go out on a limb and say 2GBs. This would mean a 3GB virtual file. There is a lot of debate as to how much virtual memory one should allocate for the larger amounts of RAM being used such as 32GB. Would you make a 48GB swap file? Probably not, but some Microsoft people still say you should.
So, in theory, if your OS only had 5% of memory to use, it could page the virtual memory and use that.
Another possibility is to limit the maximum amount of RAM SQL can use, but 1GB is already getting a bit low. But, if you are getting into trouble at 900MB of RAM being taken by SQL, you could minimize to 700MB or increase the size of your virtual memory. By default, the virtual memory resides on your system drive, but it is generally better to move it to a different drive, preferable a dedicated drive. (Big argument here on memory dumps and leaving it on the system drive).
For this stuff, you would right click on My Computer and select the Advanced tab. Click on the Performance Settings. First on visual effects, especially on a dedicated "main computer," Choose the radio button for Adjust for best performance unless you like fading and sliding menus. Under the advanced tab you will select Background services if this is the database and Programs if it is the clients. At the bottom, you will see virtual memory. Set this for 1.5 times your physical RAM.
Under Data Execution Prevention, you can choose the DEP for essential windows only.
AGAIN, INFO HELPS. HOW MUCH RAM? ETC.
