Development · Performance Tuning

SQL Server – Cleaning Procedure Cache for a particular database

HI friends, most of the time we used DBCC FREEPROCCACHE to clear the procedure cache, clears the specific plan from cache by specifying plan handle or SQL handle.

DBCC FREEPROCCACHE does not require any parameters.

To clear the plan cache we just need to execute below query –

DBCC FREEPROCCACHE

Whenever we execute this command, it will results as message like –

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

2

If you don’t want any information message to be appear after execution then execute the query in this way –

DBCC FREEPROCCACHE WITH NO_INFOMSGS

But when we required to clean the procedure cache for a particular database then in that case we use another DBCC command – DBCC FLUSHPROCINDB.

This command work is identical to FREEPROCCACHE only difference is that it accepts the parameters and can be executed for a single database only.

Suppose I want to clear the procedure cache for my database ‘Test_kapil’ then I can do it in this way-

SELECT name as DBname, dbid as DatabaseID FROM sys.sysdatabases WHERE name like ‘%Test_Kapil%’

First we will find out the DBID from the system table using above query.

After getting DBIS from that we will pass that  DBID to command.

DBCC FLUSHPROCINDB(6)

1

This is how we can also clean the procedure cache for a particular database.

Happy Learning 🙂

Advertisements

One thought on “SQL Server – Cleaning Procedure Cache for a particular database

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