The following tutorial shows how to create a user segment and how to remove all other segment mappings from the device. The examples in this section assume a server using 2K logical page sizes.
When you are working with segments and devices, remember that:
If you assign space in fragments, each fragment has an entry in sysusages.
When you assign an additional fragment of a device to a database, all segments mapped to the existing fragment are mapped to the new fragment.
If you use alter database to add space on a device that is new to the database, the system and default segments are automatically mapped to the new space.
The tutorial begins with a new database, created with one device for the database objects and another for the transaction log:
create database mydata on bigdevice = "5M" log on logdev = "4M"
Now, if you use mydata, and run sp_helpdb, you see:
sp_helpdb mydata
name db_size owner dbid created status ---------- -------- --------- ------ ------------ --------------- mydata 9.0 MB sa 5 May 27, 2005 no options set device_fragments size usage created free kbytes ---------------- ------ ---------- ----------- ----------- bigdevice 5.0 MB data only May 25 2005 3:42PM 3650 logdev 4.0 MB log only May 25 2005 3:42PM not applicable ------------------------------------------------------------------------ log only free kbytes = 4078 device segment ---------------------- ---------------------- bigdevice default bigdevice system logdev logsegment (return status = 0)
Like all newly created databases, mydata has the segments named default, system, and logsegment. Because create database used log on, the logsegment is mapped to its own device, logdev, and the default and system segments are both mapped to bigdevice.
If you add space on the same database devices to mydata, and run sp_helpdb again, you see entries for the added fragments:
use master alter database mydata on bigdevice = "2M" log on logdev = "1M" use mydata sp_helpdb mydata
name db_size owner dbid created status ---------- -------- --------- ------ ------------ --------------- mydata 12.0 MB sa 4 May 25, 2005 no options set device_fragments size usage created free kbytes ----------------- -------- ---------- ----------------- ----------- bigdevice 5.0 MB data only May 25 2005 3:42PM 2048 logdev 4.0 MB data only May 25 2005 3:42PM not applicable data only 2.0 MB log only May 25 2005 3:55PM 2040 log only 1.0 MB log only May 25 2005 3:55PM not applicable --------------------------------------------------------------------------- log only free kybytes = 5098 device segment ---------------------- ---------------------- bigdevice default bigdevice system logdev logsegment
Always add log space to log space and data space to data space. Adaptive Server instructs you to use with override if you try to allocate a segment that is already in use for data to the log, or vice versa. Remember that segments are mapped to entire devices, and not just to the space fragments. If you change any of the segment assignments on a device, you make the change for all of the fragments.
The following example allocates a new database device that has not been used by mydata:
use master alter database mydata on newdevice = 3 use mydata sp_helpdb mydata
name db_size owner dbid created status ---------- -------- --------- ------ ------------ --------------- mydata 15.0 MB sa 5 May 25, 2005 no options set device_fragments size usage created free kbytes ------------------ --------- ----------- ------------------ -------------- bigdevice 5.0 MB data only May 25 2005 3:42PM 3650 logdev 4.0 MB log only May 25 2005 3:42PM not applicable bigdevice 2.0 MB data only May 25 2005 3:55PM 2040 logdev 1.0 MB log only May 25 2005 3:55PM not applicable newdevice 3.0 MB data only May 26 2005 11:59AM 3060 ---------------------------------------------------------------------------- log only free kbytes = 5098 device segment ---------------- ---------- bigdevice default bigdevice system logdev logsegment newdevice default newdevice system
The following example creates a segment called new_space on newdevice:
sp_addsegment new_space, mydata, newdevice
Here is the portion of the sp_helpdb report which lists the segment mapping:
device segment ---------------------------- ------------------ bigdevice default bigdevice system logdev logsegment newdevice default newdevice new_space newdevice system
The default and system segments are still mapped to newdevice. If you are planning to use new_space to store a user table or index for improved performance, and you want to ensure that other user objects are not stored on the device by default, reduce the scope of default and system with sp_dropsegment:
sp_dropsegment system, mydata, newdevice
sp_dropsegment "default", mydata, newdevice
You must include the quotes around “default”; it is a Transact-SQL reserved word.
Here is the portion of the sp_helpdb report that shows the segment mapping:
device segment ---------------------------- -------------------- bigdevice default bigdevice system logdev logsegment newdevice new_space
Only new_space is now mapped to newdevice. Users who create objects can use on new_space to place a table or index on the device that corresponds to that segment. Since the default segment is not pointing to that database device, users who create tables and indexes without using the on clause are not placing them on your specially prepared device.
If you use alter database on newdevice again, the new space fragment acquires the same segment mapping as the existing fragment of that device (that is, the new_space segment only).
At this point, if you use create table and name new_space as the segment, you get results like these from sp_helpsegment:
create table mytabl (c1 int, c2 datetime) on new_space
sp_helpsegment new_space
segment name status ------- ------------------------------ ------ 3 new_space 0 device size free_pages ---------------- ----------- ----------- newdevice 3.0MB 1523 Objects on segment ‘new_space’: table_name index_name indid partition_name ----------------- ------------------ -------- ---------------- mytabl mytabl 0 mytabl_400001425 Objects currently bound to segment ‘new_space’: table_name index_name indid total_size total_pages free_pages used_pages reserved_pages ------------- ----------- ----------- ----------- -------------- 3.0MB 1536 1523 13 0
