Solved

sql2008, buffer pool usage at db level

Posted on 2012-03-14
3
320 Views
Last Modified: 2012-03-20
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
Comment
Question by:motioneye
  • 2
3 Comments
 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 150 total points
Comment Utility
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
 
LVL 25

Expert Comment

by:jogos
Comment Utility
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
 
LVL 25

Accepted Solution

by:
jogos earned 350 total points
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now