Skip to Content

How to Create a dbccdb Database ?

Build the DBCDB Database:


  • Before you can run the DBCC CHECKSTORAGE utility, you must first create the dbccdb database. This database is used exclusively for the CHECKSTORAGE utility, and is not created as part of the default ASE installation.

  • Although procedures for creating the dbccdb database are contained in greater detail within the ASE manuals, the purpose of this document is to provide a step-by-step guide on how to create and configure a dbccdb database.

  • This document assumes you do not have a dbccdb database and are performing these steps using the ‘sa’ login (or with an ASE account with dba authority).



Step 1 --- Getting Started:  Estimate Your Requirements

  • In order to create and configure your dbcc database, a good estimate of the size and resources required by your installation is needed. Creating a dbccdb that is too small, or with insufficient resources, can result in performance issues; or even CHECKSTORAGE operation failures. Creating a dbccdb that is too large, or uses excessive resources, will result in waste; and, on rare occasions, result in unexpected problems.

  • To obtain an estimate of dbccdb requriements, please execute the following:
         execute sp_plan_dbccdb
         go

  • Example Output:
Recommended size for dbccdb database is 18MB (data = 16MB, log = 2MB).
No suitable devices for dbccdb in master..sysdevices.
Recommended values for workspace size, cache size and process count are:

dbname                      scan ws     text ws     cache       comp mem     process counttempdb                         144K         48K         1280K          0K                    2
master                          272K         80K           640K          0K                    1
model                             64K         48K           640K          0K                    1
sybsystemdb                  64K         48K           640K          0K                    1
sybsystemprocs          2128K       544K          640K           0K                   1
jumpdb                       2010K       610K         2028K          0K                    3
testdb                         2592K       656K         2560K          0K                   4
  • The resulting output will provide recommendations on how large the dbccdb database should be, what disks are available, and several other parameters.

  • Be certain to save or capture this output, since it will be needed in subsequent steps.

  • In the syntax examples below, the values reported in the above sp_plan_dbccdb output will be used.
    Note:
    • Use the values generated by your own sp_plan_dbccdb output when building the dbccdb, not the sample values used here.
    • While sp_plan_dbccdb gives values for all databases, including system databases, it is not recommended to run DBCC CHECKSTORAGE against system databases.



Step 2 --- Decide on a Method of Running DBCC Checks:  Do You Want to Run DBCC Checks Serially or Concurrently?

  • Before configuring your dbccdb, you must determine if you want to run DBCC CHECKSTORAGE operations serially (one CHECKSTORAGE job at a time), or concurrently (multiple CHECKSTORAGE jobs at the same time). This decision is necessary because the procedures for configuring your dbccdb vary greatly depending on this choice.
  • Due to the different procedures involved, most of the instructions in this guide are divided into two seperate sections: “If DBCC Checks Will Be Run Serially” and “If DBCC Checks Will Be Run Concurrently”. Be certain to follow the section that is appropriate for the way you want to run dbcc checks.



Step 3 --- Build the dbccdb Database:

  • First, the disk devices where the dbccdb will reside must be chosen or initialized.  While the sp_plan_dbccdb output may recommend certain existing devices with available space, it is recommended to use devices that are not shared with other databases to avoid concurrency issues. It is also suggested that log and data devices be seperated onto different disks for performance reasons. Of course, any device used will need to be able to accommodate the recommended size provided in the sp_plan_dbccdb output.

  • Assuming that your devices need to be created, the DISK INIT command is used to initialize the two devices required to contain the data and log segments for the dbccdb. Once they are created, the CREATE DATABASE command is used to build the dbccdb database.  
  • Sample Syntax:

     use master

     go


     disk init
     name = "dbccdb_dat",
     physname = "/xxxx/xxxx/xxxx/",
     size = "20MB"
     go

     disk init
     name = "dbccdb_log",
     physname = "/xxxx/xxxx/xxxx/",
     size = "5M"
     go

     create database dbccdb
     on dbccdb_dat = 20
     log on dbccdb_log = 5
     go

  • Note: In the above syntax, values have been rounded up to the nearest value of “5”. This is optional.

Step 4 --- Create the Work Segments:

  • Add the segments for the scan and text workspaces to the dbccdb data device:

    use dbccdb

    go


     execute sp_addsegment scanseg, dbccdb, dbccdb_dat
     go

     sp_addsegment textseg, dbccdb, dbccdb_dat
     go

Step 5 --- Create System Tables for the DBCCDB and Initialize the DBCC Types Table:

  • Run the ‘installdbccdb’ script as ‘sa’ from command line:

         isql -Usa -P***** -iinstalldbccdb

  • The installdbccdb script checks for the existence of the dbccdb database before attempting to create the tables. It creates only those tables that do not already exist in the dbccdb. If any of the dbccdb tables become corrupted, remove them with DROP TABLE, and then use the installdbccdb script to re-create them.



Step 6 --- Set the Recommended Configuration Parameters:

  • In setting dbccdb configuration parameters, refer to the output from sp_plan_dbccdb.




II.  If DBCC Checks Will Be Run Serially

  • If DBCC's will be run concurrently, skip to the section below, entitled "If DBCC Will Be Run Concurrently".  Otherwise, continue with the steps presented below.

  • In the sp_plan_dbccdb output, identify the largest values recommended for each category.  In the sample output used here, the largest values recommended are:
         scan ws           = 2592K
         text ws            = 656K
         cache               = 2560K
         process count  =4

  • Note: Be certain to identify the largest values, regardless of database name. In the example above, all values came from the same entry (for testdb), but this will not always be the case. For instance, a higher cache recommendation might be found in another database entry. Once the highest values have been identified, they will be set globally for all CHECKSTORAGE operations.



    Step 1 --- Create the Work Spaces:
  • The “scan ws” and “text ws” references are specialized segments that act as a scratch area for DBCC CHECKSTORAGE internal use during processing.  Using the largest values reported in sp_plan_dbccdb, execute
    the following:

         use dbccdb
         go

         sp_dbcc_createws dbccdb, scanseg, scan_ws_default, scan, "2592K"
         go

         sp_dbcc_createws dbccdb, textseg, text_ws_default, text, "656K"
         go

  • Note: Use of the workspace names “scan_ws_default” and “text_ws_default” is optional. You may use names of your own choosing.


Step 2 --- Configure the Process Count:

  • This parameter can be confusing since the ‘process count’ belongs to the dbccdb-specific configuration setting ‘max worker processes’, rather than the ASE configuration parameter ‘number of worker processes’.
    For example, execute the following:

         use dbccdb
         go

         sp_dbcc_updateconfig null ,"max worker processes", "4"
         go

  • Note: in the above syntax, ‘null’ is used rather than specifying a specific database name.This will result in the parameter being set globally for all CHECKSTORAGE operations.

Step 3 --- Create a Data Cache:

  • Considerations:

    • Creating a data cache for CHECKSTORAGE is not mandatory since this procedure will use the default data cache if nothing is specified. However, it is strongly recommended to perform this step even if you plan to use the default data cache for DBCC.  Further, it is generally considered a 'best practice' to define and use a named data cache for the dbccdb in order to control resources and to avoid any potential contention with other processes running on the server.

    • When creating a new named data cache, or designating an existing one to be used, you will also need to configure a large I/O pool for that cache that is equal to the estimated cache value reported bysp_plan_dbccdb. Since you also need a few extra MB's available in the default page size pool (2k pool in 2k page servers, 4k pool in 4k page servers, etc), the data cache should be configured slightly larger than what was recommended by the sp_plan_dbccdb output. Hopefully, this will become clearer in the following examples.

    • Note: whichever data cache is used for dbccdb, it is important that the cache NOT be partitioned (an unpartitioned cache will have a Config/Run partition size of ‘1’).
  • Size the  Data Cache:

    • If you are using the default data cache:
      • You first need to determine if the existing configuration is sufficient for the dbcc. To do this, execute the following to determine the current size of the default data cache and if which I/O pools (if any) are configured. Also, verify that the cache is NOT partitioned and if it is large enough to accommodate the recommended cache size from sp_plan_dbccdb (plus a little extra MB's):
        • sp_cacheconfig “default data cache”
        • sp_poolconfig "default data cache"
      • Use the sp_cacheconfig procedure to adjust this configuration as needed: sp_cacheconfig “default data cache”, ....

    • If you are using a named data cache:
      • Use sp_cacheconfig to set up your data cache. For example, using the output referenced above, a 5 MB data cache is recommended.  That is, based on the sp_plan_dbccdb output, we would need 2560K (2.5 MB) plus at least 2MB for the default page size pool and then we rounded up  to 5MB:

             sp_cacheconfig "dbccdb_cache", "5MB"
             go

Step 4 --- Configure an 8-Page I/O Pool:

  • Checkstorage operates in 8-page extents, so the data cache should be configured to use a large I/O pool of 8-page blocks. The page size of your server will determine the type of large I/O pool you will configure as follows:
    • (2KB-page size) * (8 extents) = 16k buffer pool
    • (4KB-page size) * (8 extents) = 32k buffer pool
    • (8KB-page size) * (8 extents) = 64k buffer pool
    • (16KB page size) * (8 extents) = 128k buffer pool

  • Assuming this is a 2k-page server, you would configure the large I/O pool as follows:

         sp_poolconfig "dbccdb_cache", "2560K", "16K"
         go

  • Note: In the example above, we use the actual recommended value for ‘cache’ from the sp_plan_dbccdb output, not the slightly larger size mentioned earlier.




III.  If DBCC Checks Will Be Run Concurrently


If you are running dbcc's in parallel, the dbccdb database will need to be configured with a seperate set of values for EACH database that dbcc's will be run against.  The following steps must be repeated for each database you plan on running CHECKSTORAGE against, using the unique values from sp_plan_dbccdb for each database.




Step 1:  Create the Work Spaces:

  • The “scan ws” and “text ws” references are special segments that serve as an internal scratch area for the DBCC CHECKSTORAGE process.

  • For example, the syntax for creating workspaces for ‘jumpdb’ (from the example sp_plan_dbccdb output above) is as follows:

         use dbccdb
         go

         sp_dbcc_createws dbccdb, scanseg, scan_ws_jumpdb, scan, "2010K"
         go

         sp_dbcc_createws dbccdb, textseg, text_ws_jumpdb, text, "610K"
         go

  • Repeat this procedure for each database CHECKSTORAGE will be run against, using the unique database name as part of the naming convention. Therefore, ‘testdb’ would use ‘scan_ws_testdb’ with a value of 2592K, and so on.

Step 2 --- Configure the Process Count:

  • Since the ‘process count’ paramater belongs to the dbccdb-specific configuration setting ‘max worker processes’, rather than to the ASE configuration parameter ‘number of worker processes’.  Using the recommended values for ‘Process Count’ from the sp_plan_dbccdb output, assign values for each database.

  • Example syntax for database “jumpdb”:

         use dbccdb
         go

         sp_dbcc_updateconfig jumpdb ,"max worker processes", "3"
         go

  • Repeat the above to specify a unique ‘process count’ for each database you plan to run CHECKSTORAGE against."

  • Note: In the above syntax, specify the database name instead of using ‘null’. This will result in the parameter being set specifically for the indicated database when running CHECKSTORAGE.



Step 3 --- Configure the Data Cache:

  • Creating a data cache for CHECKSTORAGE use is not mandatory, since this procedure will use the default data cache if nothing is specified. However, it is strongly recommended to perform this step even if you plan to use the default data cache for DBCC.  Further, it is generally considered a 'best practice' to define and use a named data cache for dbccdb in order to control resources used and to avoid any potential competition with other processes running on the server.

  • When creating a new named data cache or designating an existing one to be used, you will also need to configure a large I/O pool within the cache equal to the estimated cache value reported bysp_plan_dbccdb. Further, since you need some extra MB's available in the default page size pool as well (2k pool in 2k page servers, 4k pool in 4k page servers, etc), the data cache should be configured slightly larger than the recommendation made by sp_plan_dbccdb output. Hopefully, this will become clearer in the following examples.

  • Note: whichever data cache is used for the dbccdb, it is important that the cache NOT be partitioned (an unpartitioned cache will have a Config/Run partition size of ‘1’).

  • If you are using the default data cache, execute the following to determine the size of the default data cache and which I/O pools are configured. Also, use this output to verify that the cache is NOT partitioned and that it is large enough to accommodate the recommended cache size from the sp_plan_dbccdb output, plus a little extra (a few MB extra should be sufficient):
    • sp_cacheconfig “default data cache”
    • sp_poolconfig "default data cache"

  • Use sp_cacheconfig to define the data cache, and use the sample sp_plan_dbccdb output for sizing the cache. If you intend to run DBCC CHECKSTORAGE against both jumpdb and testdb concurrently, then the named cache should be sized a little larger than the sp_plan_dbccdb outputFor example, 2028K for jumpdb + 2560k for testdb) = 588K (since the sum is about 4.5 MB and we need a few MB extra and then we round up to at least 6.5 to 7 MB or more).
    • Syntax Example:
           sp_cacheconfig "dbccdb_cache", "10MB"
           go

Step 4 --- Configure an 8-page I/O Pool:

  • Checkstorage operates in 8-page extents, so the data cache should be configured to use a large io pool of 8-page blocks. The page size of your server will determine the type of large io pool you will configure:
    • (2KB-page size) * (8 extents) = 16k buffer pool
    • (4KB-page size) * (8 extents) = 32k buffer pool
    • (8KB-page size) * (8 extents) = 64k buffer pool
    • (16KB page size) * (8 extents) = 128k buffer pool

  • Assuming this is a 2k-page server, you would configure the large I/O pool as follows:

sp_poolconfig "dbccdb_cache", "4588K", "16K"
      go

  • Note: Here we use the TOTAL of the recommended values for ‘cache’ from the sp_plan_dbccdb output for those databases for which DBCC CHECKSTORAGE will be run.

IV.  Finishing Up

Step 1 --- Check the sp_configure ‘number of worker processes’ value:

  • Execute the following to determine what the parameter is currently set to:
       sp_configure “number of worker processes”

  • If the DBCC procedure will be run serially:
    • Make certain that the ‘number of worker processes’ ASE configuration parameter is set to at least 1 higher than the highest ‘process count’ value from the sp_plan_dbccdb output.

  • If the DBCC procedure will be run concurrently:
    • Make certain that the ASE configuration parameter, ‘number of worker processes’, is set to at least 1 higher than the sum of the ‘process count’ values from the sp_plan_dbccdb output for all databases CHECKSTORAGE will be run against.

    • The new value for ‘number of worker processes’ can be set as follows (where <n> is the new value being assigned for the parameter):

           sp_configure “number of worker processes”, <n>
           go


        

Step 2 --– Update the dbcc_config table:

  • The dbcc_config table provides the CHECKSTORAGE operation information on execution parameters and what resource limts it should observe. This table needs to be updated in order for the CHECKSTORAGE procedure to use latest parameters and values.  An example of updating this table using sp_dbcc_updateconfig for the DBCC 'max worker processes' value was provided in Step 6 above.  Additionally, the following should be added to the table:

    • Data Cache:
      • If DBCC will be run serially:
        • Use the highest recommended value for ‘cache’ in the sp_plan_dbccdb output.  Run the following to store the cache information:
               sp_dbcc_updateconfig null, "dbcc named cache", <name of the data cache created in Step 6>, "<highest value recommended in sp_plan_dbccdb output>"
               go

      • If DBCC will be run concurrently:
        • Run the sp_dbcc_updateconfig procedure for each database CHECKSTORAGE will be run against, using the following syntax:
          sp_dbcc_updateconfig <database name>, "dbcc named cache", <name of the data cache created in Step 6>, "<recommended value for that database>"
        • For example:
               sp_dbcc_updateconfig jumpdb, "dbcc named cache", dbccdb_cache , "2028K"
               go

               sp_dbcc_updateconfig testdb, "dbcc named cache", dbccdb_cache , "2560K"
               go

    • Work Spaces:
      • If DBCC will be run serially:
        • Use the following syntax to set the global parameter for the scan and text workspaces for all databases:
               sp_dbcc_updateconfig null, "scan workspace", scan_ws_default
               go

               sp_dbcc_updateconfig null, "text workspace", text_ws_default
               go

      • If DBCC will be run concurrently:
        • Use the following syntax for each database CHECKSTORAGE will be run against:
               sp_dbcc_updateconfig <database name>, "scan workspace", <scan segment name for this db>
               sp_dbcc_updateconfig <database name>, "text workspace", <text segment name for this db>

          For example:
               sp_dbcc_updateconfig jumpdb, "scan workspace", scan_ws_jumpdb
               go

               sp_dbcc_updateconfig jumpdb, "text workspace", scan_ws_jumpdb
               go



Step 3 --- Confirm Configuration:

  • At this point, dbccdb configuration should be complete.  To confirm and double-check your settings, execute sp_dbcc_evaluatedb to determine if the current configuration settings are sufficient:

         sp_dbcc_evaluatedb
         go

  • Lastly, although not required, consider turning on the database option “trunc log on chkpt” for the dbccdb database to help avoid ‘log full’ conditions in the database.  For example:

         use master
         go

         sp_dboption dbccdb, 'trunc log on chkpt', true
         go 
Tags: