1 out of 2 rated this helpful - Rate this topic

DBCC FREEPROCCACHE (Transact-SQL)

SQL Server 2005

Updated: 12 December 2006

Removes all elements from the procedure cache.

Topic link icon Transact-SQL Syntax Conventions


DBCC FREEPROCCACHE [ WITH NO_INFOMSGS ]

Arguments

WITH NO_INFOMSGS: Suppresses all informational messages.

Result Sets

DBCC FREEPROCCACHE returns:

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

Remarks

Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache causes, for example, an ad hoc SQL statement to be recompiled instead of reused from the cache. This 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