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


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




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.

See also Performance and Tuning Guide.


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


With granular permissions enabled, you must be a user with manage data cache privilege.


With granular permissions disabled, you must be a user with sa_role


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



Audit option


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

Related reference