First you need to execute the system stored procedure sp_plan_dbccdb (you can start it without parameters to consider all database or provide a database name i.e. if you only plan to run the dbcc checks on one database):
1> sp_plan_dbccdb mydb 2> go Recommended size for dbccdb database is 2023MB (data = 2021MB, log = 2MB). ... Recommended values for workspace size, cache size and process count are: dbname scan ws text ws cache comp mem process count mydb 1340M 335M 335M 0K 15
This gives you the basic sizes you need to use when creating and configuring the DBCC database (dbccdb).
Now let’s create the database:
1> create database dbccdb on dbcc_data_dev = 2021, log on dbcc_log_dev = 2 2> go
You of course need to have these 2 devices (dbcc_data_dev and dbcc_log_dev) already available and with sufficient space for the database.
Then we’ll create a named cache for the new database:
1> sp_cacheconfig dbccdb_cache, "336M" 2> go
The size of the cache should be chosen to allow for:
- a pool for small IOs (2K on a server with 2K pages…): it needs to be at least 512K since it’s the minimum size for a cache pool
- a pool for large IOs (for the size of an extent. An extent being 8 pages, it means that on a server with 2K pages you need to create a 16K pool, on a server with 4K pages a 32K pool…): it’s the size shown by sp_plan_dbccdb (under cache).
So in our case it was 335,5 MB, so we chose 336 MB.
The 4K pool is automatically taking up all the configure cached, so we need to reassign some of it for large IOs:
1> sp_poolconfig dbccdb_cache, "335M", "32K" 2> go
And we bind the cache to the dbccdb database:
1> sp_bindcache dbccdb_cache, dbccdb 2> go
Then you need to add the scan and text segments in the dbccdb for our mydb databases:
1> sp_addsegment mydb_scanseg, dbccdb, dbcc_data_dev 2> go
1> sp_addsegment mydb_textseg, dbccdb, dbcc_data_dev 2> go
You can then assign these 2 segments to our database using sp_dbcc_createws (this basically creates the appropriate tables in dbccdb):
1> sp_dbcc_createws dbccdb, mydb_scanseg, mydb_scan_ws, scan, "1340M" 2> go
1> sp_dbcc_createws dbccdb, mydb_textseg, mydb_text_ws, text, "335M" 2> go
Then you need to set the number of worker processes:
1> sp_dbcc_updateconfig mydb, "max worker processes", "15" 2> go
Then assign the cache:
1> sp_dbcc_updateconfig mydb, "dbcc named cache","dbccdb_cache","335M" 2> go
And the two workspaces we’ve created above:
1> sp_dbcc_updateconfig mydb, "scan workspace", mydb_scan_ws 2> go
1> sp_dbcc_updateconfig mydb, "text workspace", mydb_text_ws 2> go
You can now use your dbcc checks e.g.:
1> dbcc checkstorage mydb 2> go
Update: You can check the settings using sp_dbcc_evaluatedb (using the database name e.g. mydb as parameter). You might notice that the suggested cache size returned by this stored procedure is actually much smaller than the one returned by sp_plan_dbccdb. Never found out why there is such a difference (in my case sp_plan_dbccdb reported 335M just the same as for the text workspace and sp_dbcc_evaluatedb reported less than 10 MB…).