Chapter 4: dbcc Stored Procedures

sp_dbcc_updateconfig

Description

Updates the dbcc_config table in dbccdb with the configuration information of the target database.

Syntax

sp_dbcc_updateconfig dbname, type, "str1" [, "str2"]

Parameters

dbname: is the name of the target database for which configuration information is being updated.

type: specifies the type name from the dbcc_types table. Table 4-2 shows the valid values for type.

str1: specifies the first configuration value for the specified type to be updated in the dbcc_config table. Table 4-2 describes the expected value of str1 for the specified type.

str2: specifies the second configuration value for the specified type that you want to update in the dbcc_config table. Table 4-2 describes the expected value of str2 for the specified type.

Examples

Example 1

Updates dbcc_config with the maximum number of worker processes for dbcc checkstorage to use when checking the pubs2 database. The new maximum number of worker processes is 4:

sp_dbcc_updateconfig pubs2, "max worker processes", "4"

Example 2

Updates dbcc_config with the size of the dbcc named cache “pubs2_cache”. The new size is 10K:

sp_dbcc_updateconfig pubs2, "dbcc named cache", pubs2_cache, "10K"

Example 3

Updates dbcc_config with the new name of the scan workspace for the pubs2 database. The new name is scan_pubs2. This update is made after using sp_dbcc_alterws to change the name of the scan workspace:

sp_dbcc_updateconfig pubs2, "scan workspace", scan_pubs2

Example 4

Updates dbcc_config with the new name of the text workspace for the pubs2 database. The new name is text_pubs2. This update is made after using sp_dbcc_alterws to change the name of the text workspace:

sp_dbcc_updateconfig pubs2, "text workspace", text_pubs2

Example 5

Updates dbcc_config with the OAM count threshold value for the pubs2 database. The new value is 5:

sp_dbcc_updateconfig pubs2, "OAM count threshold", 5

Example 6

Updates dbcc_config with the I/O error abort value for the pubs2 database. The new value is 3:

sp_dbcc_updateconfig pubs2, "IO error abort", 3

Example 7

Updates dbcc_config with the linkage error abort value for the pubs2 database. The new value is 8:

sp_dbcc_updateconfig pubs2, "linkage error abort", 8

Example 8

Enables automatic workspace expansion for the database my_db

sp_dbcc_updateconfig my_db, ‘enable automatic workspace expansion’, ‘1’

Usage

sp_dbcc_updateconfig updates the dbcc_config table for the target database.
If the name of the target database is dbccdb, and the database dbccalt exists, sp_dbcc_updateconfig updates the dbcc_config table in dbccalt.
If the target database name is not found in dbcc_config, sp_dbcc_updateconfig adds it and sets the operation sequence number to 0 before updating other configuration information.
If the expected value for the specified type is a number, sp_dbcc_updateconfig converts the values you provide for str1 and str2 to numbers.

Table 4-2 shows the valid type names to use for type and the expected value for str1 or str2.

**Table 4-2: Type names and expected values**
type name	Value expected for str1 or str2
dbcc named cache	The name of the cache, specified by str1, and the new size (in kilobytes or megabytes) or the number of 2K pages, specified by str2.
IO error abort	The new error count, specified by str1. The value must be a number greater than 0. str2 is not used with this type.
linkage error abort	The new linkage error count value specified in str1. The value must be a number greater than 0. str2 is not used with this type.
max worker processes	The new number of worker processes, specified by str1. The value must be a number greater than 0. str2 is not used with this type.
OAM count threshold	The new threshold count, specified by str1. The value must be a number greater than 0. str2 is not used with this type.
scan workspace	The new name for the scan workspace, specified by str1. str2 is not used with this type.
text workspace	The new name of the text workspace, specified by str1. str2 is not used with this type.
automatic workspace expansion	Allows checkstorage to automatically expands the workspace if adequate space is available on the respective segments.

Se the System Administration Guide for more information on the type names and values.

Permissions

Only a System Administrator or the Database Owner can run sp_dbcc_updateconfig.