Creating the dbccdb database

Creating the dbccdb database

Run sp_plan_dbccdb in the master database to obtain recommendations for database size, devices, workspace sizes, cache size, and the number of worker processes for the target database. For example, suppose you run sp_plan_dbccdb with pubs2 as the target database when dbccdb did not exist:

use master
go
sp_plan_dbccdb pubs2
go

The following output appears:

Recommended size for dbccdb is 23MB (data = 21MB, log = 2MB).

Recommended devices for dbccdb are:

Logical Device Name   Device Size   Physical Device Name

sprocdev              28672         /remote/SERV/sprocs_dat
tun_dat               8192          /remote/SERV/tun_dat
tun_log               4096          /remote/SERV/tun_log

Recommended values for workspace size, cache size and process count are:

dbname        scan ws    text ws    cache    process count

pubs2         64K         64K        640K     1

For details on the information provided by sp_plan_dbccdb, see “Planning resources”.

If dbccdb already exists, drop it and all associated devices before creating a new dbccdb database:

use master
go
if exists (select * from master.dbo.sysdatabases
     where name = "dbccdb")
begin
     print "+++ Dropping the dbccdb database"
     drop database dbccdb
end
go

Use disk init to initialize disk devices for the dbccdb data and the log:

use master
go
disk init
   name = "dbccdb_dat",
   physname = "/remote/disks/masters/",
   size = "4096"
go
disk init
   name = "dbccdb_log",
   physname = "/remote/disks/masters/",
size = "1024"
go

Use create database to create dbccdb on the data disk device that you initialized in step 3:

use master
go

create database dbccdb
   on dbccdb_dat = 6
   log on dbccdb_log = 2
go

Optional – add segments for the scan and text workspaces to the dbccdb data device:

use dbccdb
go
sp_addsegment scanseg, dbccdb, dbccdb_dat
go
sp_addsegment textseg, dbccdb, dbccdb_dat
go

Create the tables for dbccdb and initialize the dbcc_types table:
```
isql -Ujms -P***** -iinstalldbccdb
```
The installdbccdb script checks for the existence of the database before it attempts to create the tables. It creates only those tables that do not already exist in dbccdb. If any of the dbccdb tables become corrupted, remove them with drop table, and then use installdbccdb to re-create them.

Create and initialize the scan and text workspaces:

use dbccdb|
go|
sp_dbcc_createws dbccdb, scanseg, scan_pubs2, scan, "64K"|
sp_dbccvreatews dbccdb, textseg, text_pubs2, text, "64K"

When you have finished installing dbccdb, you must update the dbcc_config table.