Two workspaces are required for dbccdb: scan and text. Space requirements for the workspaces depend on the size of the largest database that will be checked. To run concurrent dbcc checkstorage operations, to set up additional workspaces.
Different databases can use the same workspaces. Therefore, the workspaces must be large enough to accommodate the largest database with which they will be used.
sp_plan_dbccdb suggests
workspace sizes—the following details for determining the
workspace size are provided for background information only.
Each page in the target database is represented by one 18-byte row in the scan workspace. This workspace should be approximately 1.1 percent of the target database size.
Every non-null text column in the target database inserts a 22-byte row in the text workspace. If there are n non-null text columns in the target database, the size of the text workspace must be at least (22 * n) bytes. The number of non-null text columns is dynamic, so allocate enough space for the text workspace to accommodate future demands. The minimum space required for the text workspace is 24 pages.
You can configure dbccdb to run dbcc checkstorage concurrently on multiple databases. This is possible only when the second and subsequent dbcc checkstorage operations have their own dedicated resources. To perform concurrent dbcc checkstorage operations, each operation must have its own scan and text workspaces, worker processes, and reserved cache.
The total space requirement for workspaces depends on whether the user databases are checked serially or concurrently. If you run dbcc checkstorage operations serially, the largest scan and text workspaces can be used for all user databases. If you run dbcc checkstorage operations concurrently, set dbccdb to accommodate the largest workspaces that will be used concurrently. Determine the workspace sizes by adding the sizes of the largest databases that will be checked concurrently.
For more information, see “dbccdb workspaces” in Chapter 59, “dbccdb Tables” in the Reference Manual.
The sp_dbcc_updateconfig. . automatic workspace expansion option indicates whether checkstorage can resize the workspace, if necessary.
At the beginning of a checkstorage run, the size of the workspace is validated. If more space is needed, and automatic workspace expansion is enabled, checkstorage automatically expands the workspace if adequate space is available on the respective segments. If more space is needed, and automatic workspace expansion is not enabled, checkstorage exits and prints an informative message.
A value of 1 enables automatic workspace expansion (the default value). A value of 0 disables enable automatic workspace.
For example, to enable automatic workspace expansion for the database my_db:
sp_dbcc_updateconfig my_db, ‘enable automatic workspace expansion’, ‘1’
To disable automatic workspace expansion for the database my_db:
sp_dbcc_updateconfig my_db, ‘enable automatic workspace expansion’, ‘0’
Default scan and text workspaces are created when the installdbccdb script is run. The name of the default scan workspace is def$scan$ws and its size is 256K. The default text workspace is named def$text$ws and its size is 128K. These default workspaces are used if you have not configured default workspaces and the target database does not have configured workspace values.
