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, you need 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 dbcc checkstorage operations are run serially, the largest scan and text workspaces can be used for all user databases. If dbcc checkstorage operations are run concurrently, then dbccdb should be set to accommodate the largest workspaces that will be used concurrently. You can 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.
