I have a 2005 server that did have 4gb of memory. We upgraded to 8gb because the server stats were showing that 97% of memory was being used. After the upgrade to 8gb we are still showing 97% of memory being used.
The stats show that SQL is using 6,697 mb of it. Of that the buffer cache has 6,694 mb of active pages.
Is this common or should I look for ways to reduce the cache?
We are running SQL Server 9.00.3077.00 SP2 Enterprise Edition (64-bit)
Yes, SQL Server is optimised to retain as much in cache as possible. Why write a page to disk and clear the cache only to risk having to read it back again any second? Unused memory isn't really any use to it. It only flushes the cache when there is pressure to do so, or you instruct it to.
The best measure of memory pressure in SQL Server is not how much SQL Server is using but how often a request cannot be met by what is in the cache. Look at the Buffer Hit Cache Ratio % in perfmon. Or you could just keep buying RAM until you have enough to store all your databases in memory