Finding Information About Lost Devices and Databases

If the size of the original devices are not known, they can be derived from the sysdevices, sysdatabases, and sysusages tables in the master database.

The size and device assignment of databases can be derived from the sysdevices, sysusages, and sysdatabases tables. Each chunk of disk space assigned to a database is represented by a row in the sysusages table. The size of each chunk is in the row. The dbid of the database to which the chunk is assigned is also in the row, and the corresponding database name can be obtained from sysdatabases. The device containing the chunk of disk space can be deduced by noticing which low/high range in sysdevices contains the starting virtual address (vstart) specified in the sysusages table.

To determine whether a device is a log device, look at the value of segmap in sysusages: 7 indicates log and data, 4 indicates log only, and 3 indicates data only. Anything else is a user-defined segment.

Before re-creating devices, gather information about all affected databases. (This information must be gathered before re-creating lost devices and databases, since as part of those procedures you drop affected devices and databases, and at that point this information is lost).

To find information about lost databases

Use the following query to examine the device allocations and uses for the damaged database. You will have to assign the same blocks of space, for the same purposes. This query shows the uses and sizes of the devices allocated to mydb:
```
select segmap, size from sysusages
    where dbid =
        (select dbid from sysdatabases
            where name = "mydb")
```
Examine the output of the query. Each row with 3 in the segmap column represents a data allocation; each row with 4 in the column represents a log allocation. The size column indicates the number of 2K blocks of data. Note the order, use, and size of this information. For example:
```
segmap       size
-----------  ----------
          3       10240
          3        5120
          4        5120
          3        1024
          4        2048
```
This translates into these sizes and uses, in megabytes:
```
Data -- 20
Data -- 10
Log  -- 10
Data -- 2
Log  -- 4
```
Note A 7 in the segmap column indicates that the data and log are on the same device, and you can recover only up to the point of the most recent database backup. If the transaction log and database are on the same device, you can perform a DUMP DATABASE, not a DUMP TRANSACTION statement.
Save this information. You will need it when you re-create lost databases.