Export (0) Print
Expand All

DBCC FREESYSTEMCACHE (Transact-SQL)

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. However, you can use this command to manually remove unused entries from all caches or from a specified Resource Governor pool cache.

Topic link icon Transact-SQL Syntax Conventions


DBCC FREESYSTEMCACHE 
    ( 'ALL' [, pool_name ] ) 
    [WITH 
    { [ MARK_IN_USE_FOR_REMOVAL ] , [ NO_INFOMSGS ]  }
    ]

( 'ALL' [,pool_name ] )

ALL specifies all supported caches.

pool_name specifies a Resource Governor pool cache. Only entries associated with this pool will be freed.

MARK_IN_USE_FOR_REMOVAL

Asynchronously frees currently used entries from their respective caches after they become unused. New entries created in the cache after the DBCC FREESYSTEMCACHE WITH MARK_IN_USE_FOR_REMOVAL is executed are not affected.

NO_INFOMSGS

Suppresses all informational messages.

DBCC FREESYSTEMCACHE returns:

"DBCC execution completed. If DBCC printed error messages, contact your system administrator."

Executing DBCC FREESYSTEMCACHE clears the plan cache for the instance of SQL Server. Clearing the plan cache causes a recompilation of all subsequent execution plans and can cause a sudden, temporary decrease in query performance. For each cleared cachstore in the plan cache, the SQL Server error log will contain the following informational message: "SQL Server has encountered %d occurrence(s) of cachestore flush for the '%s' cachestore (part of plan cache) due to 'DBCC FREEPROCCACHE' or 'DBCC FREESYSTEMCACHE' operations." This message is logged every five minutes as long as the cache is flushed within that time interval.

Requires ALTER SERVER STATE permission on the server.

A. Releasing unused cache entries from a Resource Governor pool cache

The following example illustrates how to clean caches that are dedicated to a specified Resource Governor resource pool.

-- Clean all the caches with entries specific to the resource pool named "default".
DBCC FREESYSTEMCACHE ('ALL','default');

B. Releasing entries from their respective caches after they become unused

The following example uses the MARK_IN_USE_FOR_REMOVAL clause to release entries from all current caches once the entries become unused.

DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL;

Community Additions

ADD
Show:
© 2014 Microsoft