• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

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
0
motioneye
Asked:
motioneye
  • 2
2 Solutions
 
lcohanDatabase AnalystCommented:
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.
0
 
jogosCommented:
When that cache isn't used anymore it will be replaced by new pages that are demanded on that moment ... indifferent for what db.
0
 
jogosCommented:
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.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now