can i cleanup buffer for some specified database instead of the entire sql server

Question

from what i know, if i execute 'DBCC FREEPROCCACHE' and 'DBCC DROPCLEANBUFFERS', the buffer of entire server will be cleanup. i wonder if there's anyway to cleanup buffer of specified database only. therefore, query for other databases will not be affected.

Answer 1

You can clear all execution plans from a single database using DBCC FLUSHPROCINDB(<db_id>). I'm not aware of any similar command to clear specific pages from the buffer cache.

However, you can set the database offline momentarily and then back online to clear both plan and buffer caches for a database if the situation allows for this.

Answer 2

Not a possibility with FREEPROCCACHE or DBCC FREEPROCCACHE

As per msdn https://msdn.microsoft.com/en-us/library/cc293622.aspx

DBCC FREEPROCCACHE This command removes all cached plans from memory DBCC FLUSHPROCINDB (<dbid>) This command allows you to specify a particular database id, and then clears all plans from that particular database.

db id can be fetched this way

DECLARE @intDBID INT;
SET @intDBID = (SELECT [dbid] 
                FROM master.dbo.sysdatabases 
                WHERE name = 'AdventureWorks');

-- Flush the procedure cache for one database only
DBCC FLUSHPROCINDB (@intDBID);