sp_unbindcache

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 with an index, text object, or image object that 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

There are additional considerations when using sp_unbindcache:

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.
The SAP ASE server issues error number 857 if you attempt to use sp_unbindcache to unbind a database that is in use.
Cache unbindings take effect immediately and do not require a restart of the server, except with the system tempdb.
Although you can still use sp_unbindcache on a system tempdb, the binding of the system tempdb is now non-dynamic. Until you restart the server:
- The changes do not take effect
- sp_helpcache reports a status of “P” for pending, unless you have explicitly bound the system tempdb to the default data cache, in which case the status as “V” for valid, because by default the system tempdb is already bound to the default datacache.
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.
These 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

The permission checks for sp_unbindcache differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage data cache` privilege.
Disabled	With granular permissions disabled, you must be a user with sa_role

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect