Examining the Space Usage

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.

Note: You 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, SAP ASE 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 may result in a database that cannot be created using a standard create database command.

In master, examine the device allocations and uses for the damaged database:
```
select segmap, size from sysusages 
    where dbid = db_id("database_name")
```

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 the following table:

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

Sample Device Allocation
Device Allocation	Megabytes
Data	20
Data	10
Log	10
Data (user-defined segment)	2
Log	4

Note: If the segmap column contains 7s, it indicates that 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.

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