Decreasing the size of a cache

NoteWhen you reduce the size of a cache, you must reboot Adaptive Server for the change to take effect.

The following is a report on the pubs_log cache:

sp_cacheconfig pubs_log
Cache Name               Status    Type     Config Value Run Value
------------------------ --------- -------- ------------ ------------
pubs_log                 Active    Log Only      7.00 Mb      7.00 Mb
                                            ------------ ------------
                                     Total       7.00 Mb      7.00 Mb
=======================================================================
Cache: pubs_log,   Status: Active,   Type: Log Only
     Config Size: 7.00 Mb,   Run Size: 7.00 Mb
     Config Replacement: relaxed LRU,   Run Replacement: relaxed LRU
     Config Partition:            1,   Run Partition:            1

IO Size  Wash Size Config Size  Run Size     APF Percent
-------- --------- ------------ ------------ -----------
    2 Kb    920 Kb      0.00 Mb      4.50 Mb     10
    4 Kb    512 Kb      2.50 Mb      2.50 Mb     10

The following command reduces the size of the pubs_log cache to 6Mb from command size of 7Mb

sp_cacheconfig pubs_log, "6M"

After a restart of Adaptive Server, sp_cacheconfig shows:

Cache Name               Status    Type     Config Value Run Value
------------------------ --------- -------- ------------ ------------
pubs_log                 Active    Log Only      6.00 Mb      6.00 Mb
                                            ------------ ------------
                                     Total       6.00 Mb      6.00 Mb
=======================================================================
Cache: pubs_log,   Status: Active,   Type: Log Only
     Config Size: 6.00 Mb,   Run Size: 6.00 Mb
     Config Replacement: relaxed LRU,   Run Replacement: relaxed LRU
     Config Partition:            1,   Run Partition:            1

IO Size  Wash Size Config Size  Run Size     APF Percent
-------- --------- ------------ ------------ -----------
    2 Kb    716 Kb      0.00 Mb      3.50 Mb     10
    4 Kb    512 Kb      2.50 Mb      2.50 Mb     10

When you reduce the size of a data cache, all the space to be removed must be available in the default pool. You may need to move space to the default pool from other pools before you can reduce the size of the data cache. In the last example, if you wanted to reduce the size of the cache to 3MB, you would need to use sp_poolconfig to move some memory into the default pool of 2K from the 4K pool. The memory is moved to “memory available for named caches”. See “Changing the size of memory pools” for more information.