I can clear the buffer for some specified database instead of the whole sql server

Question

from what I know, if I execute DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS, the entire server buffer will be cleared. I wonder if, in any case, there is only a cleanup buffer for the specified database. therefore, a request to other databases will not be affected.

Answer 1

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

However, you can instantly install the database offline, and then return online to clear the planning cache and buffer for the database if the situation permits.

Answer 2

Cannot use FREEPROCCACHE or DBCC FREEPROCCACHE

According to msdn https://msdn.microsoft.com/en-us/library/cc293622.aspx

DBCC FREEPROCCACHE This command deletes all cached plans from the DBCC FLUSHPROCINDB ( <dbid> ) <dbid> This command allows you to specify a specific database identifier, and then clears all plans from that particular database.

db id can be retrieved 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);