Lots of people do not know that following command can be very helpful to clear your memory caches of SQL Server. I have often seen people restarting their entire system to clear the memory caches.
Though, often I do not see any reasons for dropping the memory caches, but there are always special scenarios when users have to drop all the cache from their system to give a fresh start to their system. Let us see commands to clear memory caches
DBCC commands to free several SQL Server memory caches:
DBCC FREESYSTEMCACHE
Releases all unused cache entries from all caches. The SQL Server Database Engine proactively cleans up unused cache entries in the background to make memory available for current entries.
DBCC FREESESSIONCACHE
Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.
DBCC FREEPROCCACHE
Removes all elements from the procedure cache.
Note: Please be careful before you execute these commands on your server, as it will remove all the cache from your system. If you are business need is to remove the memory cache, go ahead and do it, but for any other reason, these commands are not advisable to run anywhere.
Reference: Pinal Dave (https://blog.sqlauthority.com), BOL
13 Comments. Leave new
can we use dbcc FREESYSTEMCACHE
dbcc FREESESSIONCACHE
dbcc FREEPROCCACHE
commands can be used in production server
Hi Pinal,
What are the parameters that can be passed to along with ‘FREESYSTEMCACHE’.??
When I simply run the query ‘DBCC FREESYSTEMCACHE’, I got the following error message
Msg 2583, Level 16, State 3, Line 1
An incorrect number of parameters was given to the DBCC statement.
Regards ,
Biju.K.S
DBCC FREESYSTEMCACHE (‘ALL’) WITH MARK_IN_USE_FOR_REMOVAL;
paste this as it is and it will clean up the buffers
— Clean all the caches with entries specific to the resource pool named “default”.
DBCC FREESYSTEMCACHE (‘ALL’,’default’)
1). May i run this query on every page???
2). This will affect the performance????
Hello Pinal:
The above commands are working in SQL Server 2008 R2 only, they are not in 2012.
Are there any commands which will work in SQL Server 2012, i searched in net but i didn’t find any.
hi Dave, I run these command at sql server 2008 (not R2)., but memory didnt release at all. Any idea?
Hi Dave,
I have executed above commands in Microsoft SQL Server Standard Edition (64-bit) 2008 R2 database, but memory didn’t release.
I create a COM object with sp_OACreate. Then I use the sp_OAMethod to call a load method of the underlying DLL. Finally I use sp_OADestroy to dispose the COM object. The procedure works perfectly fine the first round.
During the second round of execution, sp_OACreate succeeds. But sp_OAMethod returns insufficient memory error. I was under the impression that sp_OADestroy would have released the memory, but that doesn’t seem to be the reality.
I tried the following commands after the first round of execution
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE (‘ALL’)
But none of them (nor all of them together) help.
The only way out is to restart the SQL Service after each round of execution.
Is there a way around this?
Perhaps give the OS more memory and take away a little from SQL…
How to do this???? How to release the RAM memory from SQL server??
Hi Swami,
Open your MSSMS -> Open Object Explorer -> Right-Click on the Server Name -> Select “Memory” page -> Reduce Maximum Server memory (in MB) value to a desired value. You can first check how much of OS RAM you have on your Server’s Computer Properties. If your OS RAM is 16GB or bigger the difference between your OS and SQL memory should be at least 4G.
Thanks,
Sammy Machethe
Thanks Sammy for your contribution.