The sysusages Table

Database storage information is listed in master..sysusages.

Each row in master..sysusages represents a space allocation assigned to a database. Thus, each database has one row in sysusages for each time create database or alter database assigns a fragment of disk space to it.

When you install SAP ASE, sysusages contains rows for these databases:

master, with a dbid of 1
The temporary database, tempdb, with a dbid of 2
model, with a dbid of 3
sybsystemdb, with a dbid of 31513
sybsystemprocs, with a dbid of 31514

If you upgraded SAP ASE from an earlier version, databases sybsystemdb and sybsystemprocs may have different database IDs.

If you installed auditing, the sybsecurity database is dbid 5.

As you create new databases, or enlarge existing ones, new rows are added to sysusages to represent new database allocations.

Here is what sysusages might look like on an SAP ASE that includes the five system databases and one user database. The user database was created with the log on option, and was extended once using alter database. It has a database ID (dbid) of 4:

select dbid, segmap, lstart, size, vdevno, vstart from sysusages
order by 1

dbid     segmap      lstart      size        vdevno      vstart 
----     ---------   ----------  --------    --------    ----------- 
1        7           0           6656        0           4 
2        7           0           2048        0           8196 
3        7           0           1536        0           6660
4        3           0           5120        2           0 
4        4           5120        2560        3           0
4        3           7680        5120        2           5120 
31513    7           0           1536        0           10244
31514    7           0            63488       1            0

In this example, the lstart and size columns describe logical pages for which the size may vary from 2KB – 16KB bytes. The vstart column describes virtual pages (for which the size is always 2KB). These global variables show page size information:

@@maxpagesize – logical page size
@@pagesize – virtual page size

The following matches the database ID to its name, shows the number of megabytes represented by the size column, shows the logical device name for each vdevno in the list, and computes the total number of megabytes allocated to each database. The example output shows only the result for dbid 4, and the result has been reformatted for readability:

select  dbid, db_name(dbid) as 'database name', lstart,
     size / (power(2,20)/@@maxpagesize) as 'MB',
     d.name
from sysusages u, sysdevices d
where u.vdevno = d.vdevno
and d.status & 2 = 2
order by 1
compute sum(size / (power(2,20)/@@maxpagesize)) by dbid

dbid     database name     lstart      MB          device name
------    --------------    --------    --------    -------------------- 
4         test              0           10          datadev
4         test              5120        5           logdev
4         test              7680        10          datadev

Compute Result:
----------- 
25

The following describes the changes to the segmap values in the sysusages table as you add segments. The server in the example initially includes the default databases and a user database named testdb (a data-only database), and a log on the testlog device, as shown in the following output from the sysusages table:

select dbid, segmap from master..sysusages where dbid = 6

 dbid    segmap 
------  ----------- 
  6          3 
  6           4

If you add a user segment newseg to the test database and create table abcd on newseg and again select the segment information from sysusages:

sp_addsegment newseg, testdb, datadev

create table abcd ( int c1 ) on newseg

select dbid, segmap from sysusages
where dbid=6
dbid     segmap 
------   ----------- 
6           11
6            4

Note that the segment mapping for the user database has changed from a value of 3 to a value of 11, which shows that segment mappings for user databases change when you reconfigure a database.

To determine the status of the segments, run:

sp_helpsegment

 segment     name            status
-------    ---------------- ----------
0           system              0
1           default             1
2           logsegment          0
3           newseg              0

The segment newseg is not part of the default pool.

If you add another segment, newseg1, to the testdb database and select the segment information from sysusages again, the segment mapping for newseg has changed from 11 to 27:

sp_addsegment newseg1, testdb, datadev

select dbid, segmap from sysusages

 dbid    segmap 
------  ----------- 
6            27
6              4