Unbinds a database, table, index, text object, or image object from a data cache.
sp_unbindcache dbname [,[owner.]tablename [, indexname | "text only"]]
is the name of database to be unbound or the name of the database containing the objects to be unbound.
is the name of the table’s owner. If the table is owned by the database owner, the owner name is optional.
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.
is the name of an index to be unbound from a cache.
unbinds text or image objects from a cache.
Unbinds the titles table from the cache to which it is bound:
sp_unbindcache pubs2, titles
Unbinds the titleidind index from the from the cache to which it is bound:
sp_unbindcache pubs2, titles, titleidind
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"
Unbinds the transaction log, syslogs, from its cache:
sp_unbindcache pubs2, syslogs
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.
Adaptive 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.
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.
The permission checks for sp_unbindcache differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be a user with manage data cache privilege. |
Granular permissions disabled |
With granular permissions disabled, you must be a user with sa_role |
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
System procedures sp_bindcache, sp_dboption, sp_help, sp_helpdb, sp_helpcache, sp_helpdb, sp_helpindex, sp_unbindcache_all
