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.
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, 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:
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 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
Device allocation |
Megabytes |
|---|---|
Data |
20 |
Data |
10 |
Log |
10 |
Data (user-defined segment) |
2 |
Log |
4 |
If 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.
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
