SQL SERVER – DBCC commands to free several SQL Server memory caches

DBCC commands to free several SQL Server memory caches:

DBCC FREESYSTEMCACHE
Releases all unused cache entries from all caches. The SQL Server 2005 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.

Reference : Pinal Dave (http://blog.SQLAuthority.com) ,BOL

About these ads

11 thoughts on “SQL SERVER – DBCC commands to free several SQL Server memory caches

  1. 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

  2. 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’)

  3. 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.

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

  5. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s