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 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 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 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
