How to Clear SQL Buffer or cache. Error: There is insufficient memory available in the buffer pool in SQL

Some time we had issues like There is insufficient memory available in the buffer pool in SQL. In the SQL every query, stored procedure and Views will create cache in the sql server memory. This will help our query performance to get the result quickly. Some time the buffer will full in the memory. That time we need to clear the buffer cache. You can clear the buffer cache fully using below query in the SQL new query window.

Clearing SQL Buffer: run below query

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFER

DBCC FREEPROCCACHE -- Delete or flush modified data to the disk

DBCC DROPCLEANBUFFER -- Remove all deleted and other unwanted sources

If you want to clear particular SP or views query please use below plan handle property to customize the clearing process.

DBCC FREEPROCCACHE (plan_handle | sql_handle | pool_name)

Note:

Please don’t try first in production server. Try in the dev server.

According to MSDN:

Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server.