We help IT Professionals succeed at work.

sql2008, buffer pool usage at db level

I know the query below will display current buffer pool usage at each db instance in SQL. But my questions is how do I drop those buffer pool let says from [DB_1] and let only those size  available in [DB_2]. The reasons why is because [DB_1] is no longer active in production but must be make available in SQL instance.

SELECT
(CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',
(CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
COUNT (*) AS 'Page Count'
FROM sys.dm_os_buffer_descriptors
GROUP BY [database_id], [is_modified]
ORDER BY [database_id], [is_modified];
GO
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT
Commented:
The easyest way I can think of is to restart the SQL Server service and if that database is not used anymore the buffer pool should be low.
In SQL 2008 you have a "Resource Governor" that could help you do more detailed SQL resource management.

Commented:
When that cache isn't used anymore it will be replaced by new pages that are demanded on that moment ... indifferent for what db.
Commented:
Don't do this on production
<<The easyest way I can think of is to restart the SQL Server service and if that database is not used anymore the buffer pool should be low.>>
DROPCLEANBUFFERS (after checkpoin) is easyer
http://msdn.microsoft.com/en-us/library/ms187762.aspx

Warning : That is for all databases and the db you want to give priority will get punished as hard as the others.

Check the "Resource Governor" suggestion from first comment to have some management.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.