sp_unbindcache

Description

Unbinds a database, table, index, text object, or image object from a data cache.

Syntax

sp_unbindcache dbname [,[owner.]tablename 
	[, indexname | "text only"]]

Parameters

dbname: is the name of database to be unbound or the name of the database containing the objects to be unbound.

owner: is the name of the table’s owner. If the table is owned by the Database Owner, the owner name is optional.

tablename: is the name of the table to be unbound from a cache or the name of a table whose index, text object, or image object is to be unbound from a cache.

indexname: is the name of an index to be unbound from a cache.

text only: unbinds text or image objects from a cache.

Examples

Example 1

Unbinds the titles table from the cache to which it is bound:

sp_unbindcache pubs2, titles

Example 2

Unbinds the titleidind index from the from the cache to which it is bound:

sp_unbindcache pubs2, titles, titleidind

Example 3

Unbinds the text or image object for the au_pix table from the cache to which it is bound:

sp_unbindcache pubs2, au_pix, "text only"

Example 4

Unbinds the transaction log, syslogs, from its cache:

sp_unbindcache pubs2, syslogs

Usage

When you unbind a database or database object from a cache, all subsequent I/O for the cache is performed in the default data cache. All dirty pages in the cache being unbound are written to disk, and all clean pages are cleared from the cache. See the Performance and Tuning Guide for more information.
Cache unbindings take effect immediately and do not require a restart of the server.
When you drop a database, table, or index, its cache bindings are automatically dropped.
To unbind a database, you must be using the master database. For tables, indexes, text objects, or image objects, you must be using the database where the objects are stored.
To unbind any system tables in a database, you must be using the database, and the database must be in single-user mode. Use the command:
```
sp_dboption db_name, "single user", true
```
See sp_dboption for more information.
The following procedures provide information about the bindings for their respective objects: sp_helpdb for databases, sp_help for tables, and sp_helpindex for indexes.
sp_helpcache prints the names of objects bound to caches.
sp_unbindcache needs to acquire an exclusive table lock when you are unbinding a table or its indexes to a cache. No pages can be read while the unbinding takes place. If a user holds locks on a table, and you issue sp_unbindcache on that object, the sp_unbindcache task sleeps until the locks are released.
When you change the cache binding for an object with sp_bindcache or sp_unbindcache, the stored procedures that reference the object are recompiled the next time they are executed. When you change the binding for a database, the stored procedures that reference objects in the database are recompiled the next time they are executed.
To unbind all objects from a cache, use the system procedure sp_unbindcache_all.

Permissions

Only a System Administrator can execute sp_unbindcache.