1 out of 1 rated this helpful - Rate this topic

DBCC FREESYSTEMCACHE

SQL Server 2005

Updated: 12 December 2006

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. However, you can use this command to manually remove unused entries from all caches.

Topic link icon Transact-SQL Syntax Conventions

Syntax


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

Arguments

ALL: Specifies all supported caches.

MARK_IN_USE_FOR_REMOVAL: Asynchronously frees currently used entries from their respective caches after they become unused.

NO_INFOMSGS: Suppresses all informational messages.

Result Sets

DBCC FREESYSTEMCACHE returns:

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

Remarks

New entries created in the cache after the DBCC FREESYSTEMCACHE WITH MARK_IN_USE_FOR_REMOVAL is executed are not affected.

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. In SQL Server 2005 Service Pack 2, 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.

Permissions

Requires ALTER SERVER STATE permission on the server.

Reference

DBCC (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	New content: Added information about the error message logged in Service Pack 2 when this command is executed.

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD