Examining the space usage

The following steps are recommended to determine which devices your database uses, how much space is allocated on each device, and whether the space is used for data, log, or both. You can use this information when re-creating your databases to ensure that the log, data, and indexes reside on separate devices, and to preserve the scope of any user segments you have created.

NoteYou can also use these steps to preserve segment mappings when moving a database dump from one server to another (on the same hardware and software platform).

If you do not use this information to re-create the device allocations for damaged databases, Adaptive Server will remap the sysusages table after load database to account for discrepancies. This means that the database’s system-defined and user-defined segments no longer match the appropriate device allocations. Incorrect information in sysusages can result in the log being stored on the same devices as the data, even if the data and the log were separate before recovery. It can also change user-defined segments in unpredictable ways, and can result in a database that cannot be created using a standard create database command.

To examine and record the device allocations for all damaged databases:

  1. In master, examine the device allocations and uses for the damaged database:

    select segmap, size from sysusages 
        where dbid = db_id("database_name")
    
  2. Examine the output of the query. Each row with a segmap of “3” represents a data allocation; each row with a segmap of “4” represents a log allocation. Higher values indicate user-defined segments; treat these as data allocations, to preserve the scope of these segments. The size column indicates the number of blocks of data. Note the order, use, and size of each disk piece.

    For example, this output from a server that uses 2K logical pages translates into the sizes and uses described in Table 28-20:

    segmap        size
    -------       --------
         3          10240
         3           5120
         4           5120
         8           1024
         4           2048
    

    Table 28-20: Sample device allocation

    Device allocation

    Megabytes

    Data

    20

    Data

    10

    Log

    10

    Data (user-defined segment)

    2

    Log

    4

    NoteIf the segmap column contains 7s, your data and log are on the same device, and you can recover only up to the point of the most recent database dump. Do not use the log on option to create database. Just be sure that you allocate as much (or more) space than the total reported from sysusages.

  3. Run sp_helpdb database_name for the database. This query lists the devices on which the data and logs are located:

    name       db_size owner   dbid    created 
    -------    ------- ------  ----    -----------
    mydb       46.0 MB sa        15    Apr  9 1991 
    
    
    status          device_fragments    size           usage
    --------------  ----------------    -----          ------------
    no options set  datadev1            20 MB          data only
                    datadev2            10 MB          data only
                    datadev3             2 MB          data only
                    logdev1             10 MB          log only
                    logdev1              4 MB          log only