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 will have 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 = "4M" log on logdev = "2M"
Now, if you use mydata, and run sp_helpdb, you see:
sp_helpdb mydata
name db_size owner dbid created status ---------- -------- --------- ------ ------------ --------------- mydata 6.0 MB sa 4 May 27, 1993 no options set device_fragments size usage free kbytes ---------------------- ------------- --------------- ----------- bigdevice 4.0 MB data only 3408 logdev 2.0 MB log only 2032 device segment ---------------------- ---------------------- bigdevice default bigdevice system logdev logsegment
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 9.0 MB sa 4 May 27, 1993 no options set device_fragments size usage free kbytes ---------------------- ------------- --------------- ----------- bigdevice 2.0 MB data only 2048 bigdevice 4.0 MB data only 3408 logdev 1.0 MB log only 1024 logdev 2.0 MB log only 2032 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 12.0 MB sa 4 May 27, 1993 no options set device_fragments size usage free kbytes ---------------------- ------------- --------------- ----------- bigdevice 2.0 MB data only 2048 bigdevice 4.0 MB data only 3408 logdev 1.0 MB log only 1024 logdev 2.0 MB log only 2032 newdevice 3.0 MB data only 3072 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 will not be 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 will get results like these from sp_help and sp_helpsegment:
create table mytabl (c1 int, c2 datetime) on new_space sp_help mytabl
Name Owner Type ----------------- ----------------- ---------------- mytabl dbo user table Data_located_on_segment When_created ------------------------------ -------------------- new_space May 27 1993 3:21PM Column_name Type Length Nulls Default_name Rule_name ------------- --------- ------ ----- ------------ ---------- c1 int 4 0 NULL NULL c2 datetime 8 0 NULL NULL Object does not have any indexes. No defined keys for this object.
sp_helpsegment new_space
segment name status ------- ------------------------------ ------ 3 new_space 0 device size free_pages ---------------------- -------------- ----------- newdevice 3.0MB 1528 table_name index_name indid --------------------- ---------------------- ------ mytabl mytabl 0 total_size total_pages free_pages used_pages --------------- ----------- ----------- ----------- 3.0MB 1536 1528 8
