To completely remove a data cache, reset its size to 0:
sp_cacheconfig pubs_log, "0"
This changes the cache status to “Pend/Del.” You must restart Adaptive Server for the change to take effect. Until you do, the cache remains active, and all objects bound to the cache still use it for I/O.
If you delete a data cache, and there are objects bound to the cache, the cache bindings are marked invalid at the next restart of Adaptive Server. All objects with invalid cache bindings use the default data cache. Warning messages are printed in the error log when the bindings are marked invalid. For example, if the titles table in the pubs2 database is bound to a cache, and that cache is dropped, the message in the log is:
Cache binding for database '5', object '208003772', index '0' is being marked invalid in Sysattributes.
If you re-create the cache and restart Adaptive Server, the bindings are marked valid again.
You cannot drop the default data cache.
