SQL SERVER – DBCC Commands to Free SQL Server Memory Caches

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.

SQL SERVER - DBCC Commands to Free SQL Server Memory Caches clearcache-800x214

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

Solarwinds

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

Solarwinds
, , , ,
Previous Post
SQL SERVER – Enable Login – Disable Login using ALTER LOGIN – Change name of the ‘SA’
Next Post
SQL SERVER – Download SQL Server Management Studio Keyboard Shortcuts (SSMS Shortcuts)

Related Posts

13 Comments. Leave new

  • can we use dbcc FREESYSTEMCACHE
    dbcc FREESESSIONCACHE
    dbcc FREEPROCCACHE
    commands can be used in production server

    Reply
  • 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

    Reply
  • 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’)

    Reply
  • George livingston
    April 26, 2011 11:29 am

    1). May i run this query on every page???
    2). This will affect the performance????

    Reply
  • 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.

    Reply
  • hi Dave, I run these command at sql server 2008 (not R2)., but memory didnt release at all. Any idea?

    Reply
  • Hi Dave,
    I have executed above commands in Microsoft SQL Server Standard Edition (64-bit) 2008 R2 database, but memory didn’t release.

    Reply
  • 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?

    Reply
    • Ray Sotkiewicz
      January 18, 2014 1:02 am

      Perhaps give the OS more memory and take away a little from SQL…

      Reply
      • How to do this???? How to release the RAM memory from SQL server??

      • Sammy Machethe
        January 14, 2015 7:05 pm

        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.

Leave a Reply

Menu