The 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 Adaptive Server, 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 Adaptive Server from an earlier version, databases sybsystemdb and sybsystemprocs may have different dbids.
If you installed auditing, the sybsecurity database is dbid 5.
As new databases are created or current databases enlarged, new rows are added to sysusages to represent new database allocations.
Here is what sysusages might look like
on an Adaptive Server 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 dbid 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 whose size may vary from 2k to 16k bytes. The vstart column describes virtual pages (whose size is always 2k bytes). 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:
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
The segment mapping for the user database has changed from a value of 3 to a value of 11, which shows that segments mappings for user databases are not permanent, but change when you reconfigure a database
Run sp_helpsegment to determine the status of the segments:
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
The master..sysusages segmap column is a bit map of segment assignments. segmap shows the storage that is permitted for the database fragment it represents. You control the bit values in this mask using stored procedures for segment management. The valid bit’s numbers in the mask come from syssegments in the local database. (Your “local” database is the database you are currently using: either your default database from login, or the database you most recently used with use database.)
Adaptive Server supplies three named segments:
system, which is segment 0
default, which is segment 1
logsegment, which is segment 2
Use sp_addsegment to create additional segments. If you create segments in the model database, these segments exist in all databases you subsequently create. If you create them in any other database, they will exist only for that database. Different segment names in different databases can have the same segment number. For example, newseg1 in database testdb and mysegment in database mydb can both have segment number 4.
See below for more information about the possible values for the segmap column.
