Creating the dbccdb database

StepsCreating the dbccdb database

  1. 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”.

  2. 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
    
  3. 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
    
  4. 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
    
  5. 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
    
  6. 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.

  7. 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.