How to free up memory used by idle SQL Server databases?

Question

We have a SQL Server with a few hundred databases. Many of the databases on that server are used only a few times per week. We have a problem where SQL Server sometimes becomes very slow. When that happens, memory usage indicates 99% and paging happens. In order to work around the problem we scheduled a restart of the SQL service every night to unload all those databases and free up memory.

What is the correct way of freeing up memory used by idle databases without taking the SQL Service down? We would like to automate the unloading of any database that was not accessed in the last 30 minutes.

Note: I'm looking for a solution that applies to SQL 2005. However, if there's a feature in SQL 2008 to do that I'd like to know about it.

Answer 1

SQL Server will free automatically all memory that can be freed and will avoid paging. If you encounter paging then the 99% memory is in use, is not available to be freed. You need to investigate how is the memory used, it is likely external components like sp_oa_xxx created object or distributed queries. Start by investigating the memory consumers, look at sys.dm_os_memory_clerks and read on How to use the DBCC MEMORYSTATUS command to monitor memory usage on SQL Server 2005.

As a side note, you already have the means to automatically close databases that are not is use: alter database <dbname> set auto_close on:

AUTO_CLOSE: When set to ON, the database is shut down cleanly and its resources are freed after the last user exits. The database automatically reopens when a user tries to use the database again.

If you host hundreds of databases that are used seldom then AUTO_CLOSE is exactly what you're looking for.