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 remaps 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 is the output from a server that uses 2K logical pages translates into the sizes and uses described in Table 12-21:

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

    Table 12-21: 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      status
    -------    ------- ------  ----    -----------  -------------
    mydb       46.0 MB sa        15    May 26 2005  no_options set
    
    
    device_fragments  size    usage      created              free kbytes
    ----------------  -----   --------   ------------         -----------
    datadev1          20 MB  data only  June 7 2005 2:05PM         13850
    datadev2          10 MB  data only  June 7 2005 2:05PM          8160
    datadev3           2 MB  data only  June 7 2005 2:05PM          2040
    logdev1           10 MB  log only   June 7 2005 2:05PM  not applicable
    logdev2            4 MB  log only   June 7 2005 2:05PM  not applicable