Chapter 04 - Creating Devices, Databases, and Transaction Logs


  • Learn how to create and use devices - SQL Server places databases onto devices. Devices must be created on a computer sytstem's storage device, usually a hard drive.

  • Learn how to create and use databases and transaction logs - Databases store data. Transaction logs store transactions that were made against a database.

  • Become familiar with tempdb and its use - SQL Server uses a temporary table to work with data. There are several things that can be done to tempdb to enhance performance.

  • Learn how to create and use a removable media database - Many applications are sent to a remote site to be executed or are for temporary applications. Databases stored on removable media might be more efficient and cost effective for such applications.

The storage of physical data in SQL Server is controlled through the creation of data devices. Data devices are areas of disk that are preallocated for the use of SQL Server. SQL Server can use devices for the storage of either data, logs, or dumps.

Databases are logical areas that SQL Server reserves for the storage of tables and indexes. One or many databases may be created on a data device provided the device is an appropriate size.

Transaction logs, covered in Chapter 12 "Understanding Transactions and Locking" are the work areas that SQL Server uses to manage the transactions performed by client processes. The transaction logs are used to store the information required by SQL Server to roll back a transaction if a client process issues a ROLLBACK TRANSACTION.

Defining Devices

Devices are the physical files that SQL Server creates on disk for storing databases and logs. Devices must be created before databases can be created. A device can be of two types: database (used for storing databases) and dump (used for storing transaction logs).

Devices can store more than one database or transaction log in them; however, you often can get better performance with a single database per device, because each device is managed by a single I/O thread from the operating system. A database can span multiple devices if it needs to grow, and this can be an optimizing method due to striped physical disk access over multiple drives.

Creating Database Devices

You can create a disk device in SQL Server in two ways: graphically or through Transact-SQL. The graphical method is performed by using SQL Enterprise Manager. The Transact-SQL method is performed using the DISK INIT command. Both methods are discussed in the following sections.

Using SQL Enterprise Manager

SQL Enterprise Manager is a versatile tool that permits DBAs to perform most of the administrative functions of SQL Server without knowledge of the often cryptic Transact-SQL commands required. However, SQL Enterprise Manager does not permit an automated installation or "scripted" installation procedure. For this you will need to use Transact-SQL.

To use SQL Enterprise Manager to create a device, follow these steps:

  1. Start SQL Enterprise Manager from the Microsoft SQL Server 6.5 group. Figure 4.1 shows SQL Enterprise Manager just after it's been started.

    Fig. 4.1 - Note that no server is selected, and that all the main toolbar's buttons are disabled.

  2. Select the server that's going to be managed. Then from the Manage menu, choose Devices. The Manage Database Devices window appears (see fig. 4.2).

    Fig. 4.2 - Each existing device is shown as a bar on the graph, with the dark area representing unused space in the device.

  3. Click the New Device toolbar button to create a new device. The New Database Device dialog box appears (see fig. 4.3).

    Fig. 4.3 - SQL Enterprise Manager's New Database Device dialog box. Note that you can use the slider to specify how big the device should be by dragging the "thumb".

  4. Enter the details about the device being added, including the name, the location that the device should be placed, and the size of it (see fig. 4.4).

    Fig. 4.4 - SQL Enterprise Manager's New Database Device dialog box; the information entered will create a 5M device called NewDevice on the C drive using the physical file C:\MSSQL\DATA\NewDevice.DAT.

  5. Click Create Now to begin creating the device. This process could take some time, depending on the size of the device being created and the speed of the physical drives being used. After the device is successfully created, you see the message box shown in figure 4.5.

    Fig. 4.5 - SQL Enterprise Manager displays a message after all operations indicating success or failure. This message indicates that no errors occurred during the allocation of disk space for the database device's creation.


This device could have scheduled to be created by using the Schedule button. This option allows the user to enter a time (single or recurring instance) for the operation to be performed. In addition, the Transact-SQL script that SQL Server will use to complete the operation may be edited. Additions or modifications could make the scheduled operation more powerful than simply creating a table.

After successful creation of the device, SQL Enterprise Manager will add it to the graph of devices (see fig. 4.6).

Fig. 4.6 - The NewDevice device has been added and is now empty (all dark).


SQL Server creates a physical file on the drive when the device is created. If you are using Windows NT's NT File System (NTFS), the operating system will return control to the application (SQL Enterprise Manager) immediately after executing the create command because of the way NTFS represents files to calling programs. Don't be concerned if you were used to seeing device creation take a long time on FAT or OS/2 HPFS and now it's suddenly very quick. This is one of the blessings of NT's new file system.

Using DISK INIT

DISK INIT is the SQL equivalent of creating a device through SQL Enterprise Manager. In fact, SQL Enterprise Manager's graphical front end is actually just creating the right DISK INIT command to be sent to the server. The syntax for the use of DISK INIT is as follows:

DISK INIT
     NAME = 'logical_name',
     PHYSNAME = 'physical_name',
     VDEVNO = virtual_device_number,
     SIZE = number_of_2K_blocks
     [, VSTART = virtual_address]
For clarification of the syntax used in this book, see the Syntax Guidelines section of the Introduction.

The options for the Transact-SQL command DISK INIT are as follows:

logical_name—Any valid SQL Server identifier. A shorter name is probably preferable because it has to be used each time a database is created.

physical_name—The full path and file name of the file to be used on the operating system to store the data.

virtual_device_number—This is the unique system identifier for the device. It can range from 0 to 255, where 0 is reserved for the master database.

number_of_2K_blocks—This is how a device is sized. The minimum value is 512 (meaning 1M).

virtual_address—This parameter controls the virtual paging of the data device and how SQL Server accesses it. This parameter should be used only when you're told to by a Microsoft Service Provider.

The following SQL statement creates the same data device that was created earlier using the SQL Enterprise Manager:

DISK INIT
     Name = 'NewDevice',
     PhysName = 'C:\SQL60\DATA\NewDevice.DAT',
     VDevNo = 6,
     Size = 2500

Using Device Options

You should consider two important device options when creating devices in SQL Server: mirroring and default devices. Both options are important to mission-critical environments where downtime needs to be minimized.

Mirroring
Mirroring is used to provide an absolute copy of the database (usually) on a different physical device so that if a hardware failure occurs on the primary device, the database can be switched over to run on the mirror device. SQL Server performs mirroring by installing a mirror-handling "user" on the server. This user is listed as spid 1 in the system processes, which you can list by running the system procedure sp_who. Mirroring is a continuous operation and provides maximum redundancy in the event of a failure.

Mirroring can be performed at two levels with SQL Server: either by the underlying operating system (Windows NT Advanced Server) or by SQL Server. More information on mirroring the operating system can be found in the Microsoft documentation that accompanies NTAS 3.51. In most cases, the operating system's implementation of mirroring will provide better performance and options—such as various different implementations of Redundant Arrays of Inexpensive Drives (RAID)—than SQL Server. There are six levels of RAID (levels 0 to 5) that can be implemented and they provide different methods of distributing physical data (and its copies) across multiple drives.

See Appendix B, Redundant Arrays of Inexpensive Drives, for more information about levels of RAID.

Furthermore, with NTAS it's possible to mirror whole drives, thereby protecting all devices that reside on the drive.

SQL Server's mirroring works by creating an additional device on the specified disk and then using the mirror handler to copy every transaction that the device receives to the mirror device.

To enable mirroring on a device by using SQL Enterprise Manager, follow these steps:

  1. Start SQL Enterprise Manager, select the server that's going to be managed, and then choose Devices from the Manage menu. The Manage Database Devices window appears (refer to fig. 4.6).

  2. Double-click the device to be mirrored in the graph. The Edit Database Device dialog box appears (see fig. 4.7).

    Fig. 4.7 - SQL Enterprise Manager's Edit Database Device dialog box allows you to change the size of a device by using the spinner controls on the "Size (MB)" field.

  3. Click the Mirroring button to display the Mirror Database Device dialog box (see fig. 4.8). SQL Enterprise Manager will automatically place an appropriately named mirror device in the Mirror Device Filename field.


    If you're using long file names for your devices (such as NewDevice), be aware that there seems to be a bug in the way that SQL Enterprise Manager assigns the name of the mirror device. SQL Enterprise Manager defaults the name to be limited by the old FAT 8.3 limitations and will truncate NewDevice to NewDevic. This isn't a major problem, but it could result in duplicate files being attempted to be created due to the difference in the name not being considered until after the eighth byte of the file. For example, NewDevice and NewDevice2 will have a default mirror name created that's identical and will cause an error condition in SQL Enterprise Manager.

    Fig. 4.8 - SQL Enterprise Manager's Mirror Database Device dialog box. The filename has been corrected to read c:\mssql\data\newdevice.mir to work around the bug in SQL Enterprise Manager.

  4. Click the Mirroring button to create the mirror device.

Mirroring can also be done by using Transact-SQL. The DISK MIRROR command's syntax is as follows:

DISK MIRROR
     NAME = 'logical_name',
     MIRROR = 'physical_name'
     [, WRITES = {SERIAL | NOSERIAL }]
logical_name—This is the name of the device that will be mirrored.

physical_name—This is the full file name of the mirror device. Use an extension of .MIR for compatibility with SQL Enterprise Manager.

WRITES—This option isn't used on NT. It's provided only for backward compatibility with earlier versions of SQL Server that ran on OS/2.

The following example shows how to mirror the NewDevice created earlier:

DISK MIRROR
     Name = 'NewDevice',
     Mirror = 'C:\SQL60\DATA\NEWDEVICE.MIR'
Default Devices
Default devices are used by SQL Server when the CREATE DATABASE command isn't accompanied by a specific device that it should be placed on. Many devices can be specified as default. SQL Server will use them alphabetically until each device is filled.

To make a device a default device, select the Default Device check box on the Edit Database Devices dialog box in SQL Enterprise Manager. Alternately, use the sp_diskdefault system-stored procedure. The syntax for sp_diskdefault is as follows:

sp_diskdefault device_name, {defaulton | defaultoff}
device_name is the logical device name that's being made default or not. If your device name has special characters you may need to enclose the device name in single quotes so that SQL Server will recognize it.

In the following example, the device NewDevice is made a default device:

sp_diskdefault NewDevice, defaulton

Displaying Device Information

here are two ways to find information about the devices that are now installed/active on a SQL Server: by using SQL Enterprise Manager or by using the system-stored procedure sp_helpdevice. Viewing device information by using SQL Enterprise Manager's Manage Database Devices Window has been demonstrated several times earlier in this chapter; refer to those examples for help.

The syntax for sp_helpdevice is as follows:

sp_helpdevice [logical_name]
logical_name is the name of the device that's to be inspected. If no device is specified, sp_helpdevice will report information on all the devices on the SQL Server.


The sp_helpdevice command must be entered through Transact-SQL. See the command-line applications section of Chapter 1, "Introducing Microsoft SQL Server" for help on starting a command-line ISQL session. There is also a graphical interface for ISQL/w. And Transact-SQL commands can be entered in SQL Enterprise Manager by choosing Tools, SQL Query Tool.


If you use the command-line ISQL application, remember to enter a go command to perform the commands entered to that point. The go command is the default commandend identifier. This identifier can be changed by using a switch when starting the ISQL session. Again, see the command-line applications section in Chapter 1.

The following example shows the output and use of sp_helpdevice to view all the devices on the server:

/*-----------------------------
sp_helpdevice
-----------------------------*/
device_name    physical_name               description
status cntrltype device_number low         high
-----------------------------------------------
diskdump       nul                         disk, dump device
    16     2         0             0           20000
diskettedumpa  a:sqltable.dat              diskette, 
    1.2 MB, dump device
    16     3         0             0           19
diskettedumpb  b:sqltable.dat              diskette, 
    1.2 MB, dump device
    16     4         0             0           19
master         C:\SQL60\DATA\MASTER.DAT    special, 
    default disk, physical
    disk, 40 MB
    3      0         0             0           20479
MSDBData        C:\SQL60\DATA\MSDB.DAT     special, 
    physical disk, 2 MB
    2      0         127           2130706432  2130707455
MSDBLog         C:\SQL60\DATA\MSDBLOG.DAT  special, 
    physical disk, 2 MB
    2      0         126           2113929216  2113930239

(1 row(s) affected)

Creating Dump Devices

Dump devices are special devices that SQL Server uses to perform backups and to dump (clear out) the transaction logs on databases. By default, SQL Server creates dump devices for the use of backups and log clearing. Several types of dump devices can be created, based on the medium that's being used to write the data to:

  • Disk. A disk device can be a local disk device or a network disk device that's used for dumping data from the database—in the form of a backup. If the device is on the network, make sure that the NT server that's running SQL Server can access the network share where the device is placed.

  • Tape. A tape dump device is used to back up a database directly to a tape device attached to the local computer. It isn't possible to dump to a tape attached to a remote computer.

  • Diskette. A diskette dump device is provided for backward compatibility with earlier versions of SQL Server. Not recommended for use, it's supported only through the server console, because sp_volchanged commands must be used to tell the backup server that the diskettes have been changed.

  • Named pipe. SQL Server has a named pipes interface to perform backups that allows third parties to hook in custom backup software and utilities. Named pipe devices aren't managed by SQL Enterprise Manager and must be explicitly referenced in a manual DUMP or LOAD command issued through ISQL.

    See the Chapter 1 section entitled Command-Line Applications

  • NULL. This is the special device that's used to dump the transaction logs of a database so that they're freed for more transactions to be posted against the server. Performing a dump to a NULL device will remove log entries from the database/log without adding data to the device itself. The NULL device is named DISKDUMP and is added to the system automatically when SQL Server is created. You can't manually add a NULL device to an existing SQL Server.

There are two ways to add a dump device to the system: through SQL Enterprise Manager, or by using the system-stored procedure sp_addumpdevice. The following two sections show you how to use both methods.

Using SQL Enterprise Manager to Add a Dump Device

Using SQL Enterprise Manager to add a dump device removes the burden on the DBA to remember the syntax required for the system-stored procedures that must be executed to perform the task.

To add a dump device by using SQL Enterprise Manager, follow these steps:

  1. Launch SQL Enterprise Manager from the Microsoft SQL Server 6.5 group. Select the server that's going to be managed. From the Tools menu, choose Database Backup/Restore. The Database Backup/Restore dialog box appears (see fig. 4.9).

    Fig. 4.9 - The Backup tab is chosen by default when entering this dialog box.

  2. Click the New button to create a new dump device. The New Backup Device dialog box appears (see fig. 4.10).

    Fig. 4.10 - SQL Server places all disk-based dump devices in the BACKUP subdirectory by default.

  3. Enter a Name and specify a Location for the device.


    Click the button to find a particular directory on the server or network.

  4. Specify whether the device is Tape or Disk, and then click the Create button. The device is added to SQL Server and to the list of available devices.


SQL Server allocates resources for a dump device in the list and in the sysdevices system catalog table; however, it doesn't actually create a file/output item until a backup is actually performed to the device. If you click the dump device to inspect it before you perform a backup, you'll receive error #3201, and SQL Server will say that the device is offline. Don't worry about this error unless you're sure that a backup has been performed to the device.

Using sp_addumpdevice

SQL Server's system-stored procedure sp_addumpdevice is used to add dump devices to the system. sp_addumpdevice is the only way that you can add a diskette-based device for dumping to the SQL Server. The syntax for sp_addumpdevice is as follows:

sp_addumpdevice {'disk' | 'diskette' | 'tape'},
      'logical_name',
      'physical_name'

Prior versions of SQL Server had some other parameters for sp_addumpdevice that were used to define the characteristics of the media being added. This is no longer necessary because SQL Server now inspects the device to determine its characteristics automatically.

The options for the system-stored procedure sp_addumpdevice are as follows:

logical_name—This is the logical name of the device that's going to be used for backups/dumps.

physical_name—This is the physical name of the device that's going to be used for the dump. For a 'disk' or 'diskette' dump device, specify the full path of the output file that should be created. For a 'tape' device, reference the locally attached tape device by using Windows NT's Universal Naming Convention (UNC)--for example, "\\.\tape0".

The following example adds a disk-based dump device to SQL Server:

sp_addumpdevice 
      'DiskBackup',
      'C:\SQL60\Data\DISKBACKUP.DAT'
The following example adds a remote disk-based dump device on the network workstation/server MainFileServer:

sp_addumpdevice
      'NetworkBackup',
      '\\MainFileServer\Data\NETBACKUP.DAT'
The following example adds a tape dump device to SQL Server:

sp_addumpdevice
      'TapeBackup', 
      '\\.\Tape0'

Dropping Devices

Microsoft provides two ways to drop a device: by using SQL Enterprise Manager or by using the system-stored procedure sp_dropdevice. Dropping a device completely deallocates the disk space associated with the device and frees the space for other uses by the operating system or server.

If a device is not in use any more or is not correctly sized, it may be necessary to drop it so that it can be appropriately resized or the disk space given back to the operating system for other uses.

Using SQL Enterprise Manager to Remove Devices

SQL Enterprise Manager provides a simple interface to the removal of database devices and is a convenient tool for managing large enterprises where lots of servers are involved. The DBA no longer has to know all the physical layouts of the server's devices because they are represented graphically by SQL Enterprise Manager.


For some reason, the drop device feature of SQL Enterprise Manager defaults to not removing the device file physically from the hard drive, nor does it give you an option to tell it to do so. Consequently, using SQL Enterprise Manager to remove a disk device won't actually make any disk space available for use on the server. You must go to a command prompt or to an Explorer Window and manually delete the file.

If you're deleting a device so that the server frees up some allocated disk space, you're probably better off using sp_dropdevice and specifying DELFILE.

To use SQL Enterprise Manager to remove a database device, follow these steps:

  1. In the SQL Enterprise Manager screen, select the server from which you want to remove the device. From the Manage menu, choose Devices. The Manage Database Devices window appears (see fig. 4.11).

    Fig. 4.11 - SQL Enterprise Manager's Manage Database Devices window lists all the devices that are available on the server that is being managed.

  2. Click the device that you want to remove. From the Manage Database Devices window's toolbar, click the Delete Device button. A message box appears to confirm that you want to delete the device (see fig. 4.12).

    Fig. 4.12 - This is the last chance to abort a device deletion.

    If the database device has one or many databases or logs on it, a second warning dialog box appears, asking permission to drop all the databases/logs that reside on it (see fig. 4.13).

Fig. 4.13 - This dialog box lists databases that are using the device about to be deleted. You must drop these databases before the device can be deleted.

Using sp_dropdevice to Remove Devices

The system-stored procedure sp_dropdevice is provided for dropping devices from SQL Server. The syntax for sp_dropdevice is as follows:

sp_dropdevice logical_name[, DELFILE]
The options for the system-stored procedure sp_dropdevice are as follows:

logical_name—This is the name of the device to be removed.

DELFILE—If DELFILE is included, the physical file that was created on the server will also be removed.

An error will occur if this procedure is run against a device that has databases in it. The databases must be dropped before the device can be deleted.

Defining Databases and Transaction Logs

Databases are logical entities in which SQL Server places tables and indexes. A database exists on one or many database devices; correspondingly, a database device can have one or many databases on it.

Every database has a transaction log that belongs or is associated with it. The transaction log is a place that SQL Server writes all the database transactions to before writing them to the database. The transaction log is used to hold "open" transactions (transactions started with a BEGIN TRAN statement) until they're "closed" or COMMITted. By default, the transaction log is placed on the same database device as the database; however, better performance can be achieved by creating two devices—one for the log and one for the database itself.

SQL Server can logically maintain up to 32,767 databases on a single server. However, it's more likely that the server will run out of disk, memory, and CPU resources before this limit is ever reached. A database can be up to 1T (terabyte) in size and can have as many as 32 device fragments (that is, placements on database devices).

Given that a physical disk unlikely will be greater than 10G (gigabytes) in size, it would seem to be impossible to get a database much bigger than 320G by using SQL Server (which would suggest that Microsoft should increase the number of device fragments permitted). However, a database device actually can be mapped to multiple physical devices, provided some form of software- or hardware-based striping is in use. Striping is highly recommended because it provides substantial performance gains due to multiple physical disk drives being used for a single database device. When used with RAID, striping also provides an extra level of data integrity in case of a media failure.

See Appendix B, Redundant Arrays of Inexpensive Drives, for more information

Creating a Database and Transaction Log

The process of creating a database causes SQL Server to copy the model database to the new database name. This process copies all the items in the model database's catalog. If you want to have custom default tables and objects in every database created on a server, add them to the model just like you would add them to any database.

You can take two approaches to create a database and transaction log: you can use either SQL Enterprise Manager or the CREATE DATABASE command.

Using SQL Enterprise Manager to Create Database and Transaction Logs

To create a database using SQL Enterprise Manager, follow these steps:

  1. Launch SQL Enterprise Manager from the Microsoft SQL Server 6.5 group. Select the server that's going to be managed. From the Manage menu, choose Databases. The Manage Databases window appears (see fig. 4.14).

    Fig. 4.14 - Each existing database is shown as a bar on the graph, with the dark area representing unused space in the database.

  2. Click the New Database toolbar button to create a new database.

  3. Enter the details about the database being added, including the name, the devices that the data and logs should be placed on, and how much disk space should be used for each (see fig. 4.15).

    Fig. 4.15 - SQL Enterprise Manager's New Database dialog box. The required information is entered to add a 3M database called NewDatabase on the NewDevice for data and logs.


    SQL Server 6.0 introduced a new database creation option that stops any users from accessing a database until a load operation is performed. This option is very useful for a database administrator who wants to create a database without having any users connect to it. Click the Create For Load check box in the New Database dialog box if you want to stop any users from accessing the database until the load operation has been completed.

  4. Click Create Now. After the database is successfully created, the Manage Databases window appears, showing the new database in the graph (see fig. 4.16).

    Fig. 4.16 - The NewDatabase database has been added and is mostly empty.

Using the CREATE DATABASE Command to Create Database and Transaction Logs

The CREATE DATABASE command is the Transact-SQL method to creating a database. The syntax for CREATE DATABASE is as follows:

CREATE DATABASE database_name
      [ON {DEFAULT | database_device} [= size]
      [, database_device [= size]]...]
      [LOG ON log_device [= size]
      [,log_device[= size]]...]
      [FOR LOAD]
The options for the Transact-SQL command CREATE DATABASE are as follows:

  • database_name—This is the name of the database to be created. The database name must comply with the standard rules for naming objects.

  • database_device—This is the device or list of devices that this database is to be created on and how much disk space, in megabytes, is to be reserved on each. If DEFAULT is specified, SQL Server will choose the next free default database device to use.

  • log_device—The LOG ON parameter is where the log device is specified. Like the database device, it's possible to specify more than one device to be used for the logging of the database being created.

  • FOR LOAD—This parameter disallows user access until a LOAD operation has been completed on the database.

The following example creates the same database that was created in the preceding section by using SQL Enterprise Manager:

CREATE DATABASE NewDatabase
      On NewDevice = 3
      Log On NewDevice = 2

Displaying Database Information

The Manage Databases window in SQL Enterprise Manager provides a graphical display of all the information about a database. To view this information in ISQL, use the system-stored procedure sp_helpdb. The syntax for sp_helpdb is

sp_helpdb database_name
If a database_name is supplied, sp_helpdb reports information about that database; otherwise, it reports information about all the databases on the server.

The following example shows the use of sp_helpdb for all the databases on the server. The information provided shows only the total size of the database and any options in effect:

/*-----------------------------
sp_helpdb
-----------------------------*/
name         db_size   owner dbid created     status
----------------------------------------------------
master      17.00 MB   sa    1    Jun  7 1995 
    trunc. log on chkpt.
model        1.00 MB   sa    3    Jun  7 1995 
    no options set
msdb         4.00 MB   sa    5    Nov 23 1995 
    trunc. log on chkpt.
NewDatabase  5.00 MB   sa    6    Jan  7 1996 
    no options set
pubs         3.00 MB   sa    7    Jun  7 1995 
    select into/bulkcopy, trunc.
    log on chkpt., dbo use only
tempdb       7.00 MB   sa    2    Jan  7 1996 
    select into/bulkcopy, single
    user
In the following example, a database is supplied, and more detailed information, including device fragment information, is returned from SQL Server:

/*-----------------------------
sp_helpdb NewDatabase
-----------------------------*/
name         db_size   owner dbid created     status
----------------------------------------------------
NewDatabase  5.00 MB   sa    6    Jan  7 1996 
    no options set

device_fragments               size          usage
------------------------------ ------------- --------
NewDevice                            2.00 MB log only
NewDevice                            3.00 MB data only

device                         segment
------------------------------ ---------------------
master                         default
master                         logsegment
master                         system.

The segment information is displayed only if you're executing sp_helpdb from the database that you're inspecting.

Increasing the Size of the Database and Transaction Log

SQL Server allows the size of a database to be resized in case its space is consumed by user data. In the same way, transaction logs can be increased in size if they get full too quickly and require excessive dumping.

Using SQL Enterprise Manager

To increase the size of a database or transaction log by using SQL Enterprise Manager, follow these steps:

  1. Run SQL Enterprise Manager, select the required server, and from the Manage menu choose Databases. Double-click the database that needs to be adjusted; the Edit Database dialog box appears (see fig. 4.17).

    Fig. 4.17 - SQL Enterprise Manager's Edit Database dialog box allows you to configure options that apply to the currently selected database.

  2. Click the Expand button to display the Expand Database dialog box (see fig. 4.18).

    Fig. 4.18 - At the bottom of the dialog box is a graph showing all the devices on the server and the amount of free space in each.

  3. If you're expanding the database, select from the Data Device drop-down list box the device that you want to expand the database into. In the Size (MB) text box next to the drop-down box, enter the amount of megabytes that are required.

  4. If you're expanding the log, select from the Log Device drop-down list box the device that you want to expand the log into. In the Size (MB) text box next to the drop-down box, enter the amount of megabytes that are required.


    To add a new device for the database or log to grow into, select <new> from either drop-down list box, and the New Database Device dialog box will appear.

  5. Click Expand Now to expand the database/log and return to the Edit Database dialog box. The Edit Database dialog box will be updated and will reflect adding the new log or database devices in the Log Space Available or Database Space Available fields.


SQL Enterprise Manager also provides an option to shrink a database. This is done by internally calling DBCC SHRINKDB. However, what SQL Enterprise Manager neglects to inform you is that the SQL Server must be started in single-user mode to perform these operations. If it's not started in single-user mode, there will be users (system handles such as the CHECKPOINT and MIRROR handlers) that can't be removed from the database. To start SQL Server in single-user mode use the -m keyword and start SQL Server from the command-line.

Using the ALTER DATABASE Command to Extend a Database

Transact-SQL provides the ALTER DATABASE command to allow a database to be extended. Transaction logs are also extended by using the ALTER DATABASE command; however, after the database is extended, the system-stored procedure sp_logdevice is used to specify that the extension to the database is actually for transaction log use.

The syntax for ALTER DATABASE is as follows:

ALTER DATABASE database_name
      [ON {DEFAULT | database_device} [= size]
      [, database_device [= size]]...]
      [FOR LOAD]
The options for the Transact-SQL command ALTER DATABASE are as follows:

  • database_name—This is the name of the database that's being extended.

  • database_device—This is one or more database devices and the size (in megabytes) to be allocated to the database.

  • ON DEFAULT—If DEFAULT is specified, SQL Server will allocate the requested space to the first free database device or devices that have enough space to meet the request.

  • FOR LOAD—If FOR LOAD is specified, SQL Server will stop any user processes from connecting to the database until a LOAD has completed. FOR LOAD can be specified only if the database was initially created with the FOR LOAD option.

In this example, NewDatabase is extended by 5M on the NewDevice database device:

ALTER DATABASE NewDatabase
     On NewDevice = 5
In the following example, NewDatabase is extended by a further 5M, and the logs are placed on the extended portion:

ALTER DATABASE NewDatabase
     On NewDevice = 5
Go
sp_logdevice NewDatabase, NewDevice

Dropping Databases

Dropping a database frees up any space that it consumed on any database devices, and removes any objects that it contained. Dropping a database isn't reversible, so be careful; a restore will be required to recover the database.


Be sure to remember the difference between dropping a database and dumping one. Dropping a database drops all the tables and indexes and removes the logical area on the database device reserved for the database. Dumping a database creates a backup of the database's data onto a disk, diskette, or other type of media.

User accounts that had their default database as the database that's being dropped will have their default database changed to master. Only the System Administrator (SA) or the database owner (dbo) can drop a database. The master, model, and tempdb databases can't be dropped by any user account. Also, any databases that are participating in replication or have active users can't be dropped until the replication is suspended or until the users have disconnected from the database.

Using SQL Enterprise Manager to Drop a Database

To use SQL Enterprise Manager to drop a database, follow these steps:

  1. Run SQL Enterprise Manager, select the server that the database resides on, and from the Manage menu choose Databases. The Manage Databases window appears (see fig. 4.19).

    Fig. 4.19 - SQL Enterprise Manager's Manage Databases window lists all the active databases on the currently managed server.

  2. Click the database that you want to drop.

  3. Click the Delete Database toolbar button. A message box appears, asking for confirmation to drop the database (see fig. 4.20).

  4. Click Yes; the database is dropped.

Fig. 4.20 - This message box is the last chance that you have to abort a database being dropped before SQL Enterprise Manager performs the drop.

Using the DROP DATABASE Command to Drop a Database

The syntax for the DROP DATABASE command in Transact-SQL is as follows:

DROP DATABASE database_name, [database_name...]
database_name is the name of the database to be dropped.

Databases in all "states" (including Active, Damaged, Suspect, Offline, or Not recovered) can be dropped by using the DROP DATABASE command. A database that's still in Recovery status must be dropped by using the system-stored procedure sp_dbremove.

Defining Segments

Segments are logical groups of disk devices (or portions of devices) on which database objects are placed. Database segments are created in a database, which in turn can be placed on a particular database disk device. The advantage of segments is that individual objects, such as tables and indexes, can then be explicitly placed in a segment, allowing for greater performance.

Typically, two segments would be created for a database that spans two disk devices. These segments would then be used in such a way that all the tables with non-clustered indexes would be created on one segment and the non-clustered indexes for these tables would be created on the other segment. Non-clustered indexes are indexes that are binary search trees of the data. This has a tremendous performance advantage because the reading and writing of the data and index pages can execute concurrently on two physical devices rather than run serially.

See Chapter 10, Managing and Using Indexes and Keys, for more information about creating indexes.


SQL Enterprise Manager doesn't have any user interface to data segments, and so all the work has to be done with ISQL.

Segments are also good for allowing the TEXT and IMAGE data associated with a database table to be stored on a separate physical device, and for splitting large tables across separate physical devices.


Better performance and far easier management is provided by splitting the database devices themselves across multiple disks at the operating system or hardware level by the use of RAID, rather than by using segments.

Using Default Segments

When a new database is created, three default segments are created for it. The SYSTEM segment houses all the system tables and their indexes. The LOGSEGMENT stores the transaction log for the database. The DEFAULT segment stores any user-created objects, unless they're explicitly moved or placed on a different segment.

Adding Segments

You add segments by using the system-stored procedure sp_addsegment. The syntax for sp_addsegment is as follows:

sp_addsegment segment_name, database_device
The options for the system-stored procedure sp_addsegment are as follows:

segment_name—The name of the segment that's being added.

database_device—The name of the database device that this segment should be placed on.

The following example creates a segment on the database device NewDevice:

sp_addsegment seg_newdevice1, NewDevice

Extending Segments

You can extend segments by using the system-stored procedure sp_extendsegment. When a segment is extended, it simply allocates more database disk devices to it for the use of any objects placed in the segment. The syntax for sp_extendsegment is as follows:

sp_extendsegment segment_name, database_device
The options for the system-stored procedure sp_addsegment are as follows:

segment_name—The name of the segment to be extended.

database_device—The name of the database device that this segment should be extended on.

The following example extends the segment seg_newdevice1 on to the database device NewDevice2:

sp_extendsegment seg_newdevice1, NewDevice2

If you want to extend the DEFAULT segment, you must enclose default in quotation marks because default is a reserved word. For example,

sp_extendsegment 'default', NewDevice

Using Segments

After segments are created on a database, you can place an object on those segments in two ways. Both CREATE TABLE and CREATE INDEX have an ON SEGMENT option that allows a table or index to be created on a particular segment.

See Chapter 5, Creating Database Tables and Using Data Typesfor more information about creating tables on segments.

SQL Server also provides a system-stored procedure, sp_placeobject, that will direct the server to place any new data for a table onto a new segment. You would need to use this for tables that you want to partially load on one segment and then switch over to another segment. Executing sp_placeobject doesn't move any previously existing data allocations to the new segment; all it does is cause future allocations to occur on the requested new segment.

The syntax for sp_placeobject is as follows:

sp_placeobject segment_name, object_name
The options for the system-stored procedure sp_placeobject are as follows:

  • segment_name—This is the name of the segment that the object should be placed on.

  • object_name—This is the object that's to be moved. The object can be a fully qualified table column if the column's data type is IMAGE or TEXT. Because SQL Server doesn't store IMAGE or TEXT data in the same data pages as the rest of the table data, these column types can be placed on their own segment. This provides substantially better performance. IMAGE or TEXT data should be placed with sp_placeobject before table population to ensure that all rows' data is stored in the correct segments.

The following example makes all further data allocations for the table authors on the new segment seg_data2:

sp_placeobject seg_data2, authors
The following example moves the logo column from the pub_info table to a new segment for image data:

sp_placeobject seg_ImageData, 'pub_info.logo'

Dropping Segments

Dropping segments removes them from the database devices that they reside on. A segment can't be dropped if it contains any database objects; those objects need to be dropped first. For information about dropping tables see Chapter 5, "Creating Database Tables and Using Datatypes" For information about dropping indexes, see Chapter 10,Managing and Using Indexes and Keys,. Segments are dropped by executing the system-stored procedure sp_dropsegment. The syntax for sp_dropsegment is as follows:

sp_dropsegment segment_name[, device_name]
  • segment_name—This is the name of the segment that's to be dropped.

  • device_name—This is the database device from which the segment should be removed. If no database device is specified, the segment is dropped from all the devices that it spans. If, however, a database device name is specified, the segment is removed only from that device.

Using the Tempdb Database

Tempdb is a special database that's used by SQL Server to handle any "dynamic" SQL requests from users. Tempdb is a workspace for SQL Server to use when it needs a temporary place for calculations, aggregations, and sorting operations. The sorts of things that Tempdb is used for include the following:

  • Creating temporary tables for sorting data.

  • Holding temporary tables created by users and stored procedures.

  • Storing the data that matches any server cursors that are opened by a user process.

  • Holding values for temporary user-created global variables.

One key advantage to using Tempdb is that its activity isn't logged. This means that any data manipulation activity done on Tempdb temporary tables are much faster than normal disk devices.

This is a double-edged sword, however, because if SQL Server is brought down at any time, all the information in Tempdb is lost. Take care not to rely on Tempdb without having application code that can restart itself in the event of a server shutdown.

Adjusting Tempdb's Size

The default size of Tempdb when SQL Server is installed is 2M. For most production environments, this size will be insufficient for Tempdb. If the environment is highly active with large queries or lots of requests for queries, it's recommended that Tempdb be extended.

Tempdb is created in the master device by default and can be expanded in that device, or it can be moved so that it spans multiple database devices. To increase the size of Tempdb, follow the same steps outlined earlier in the section entitled Increasing the Size of the Database and Transaction Log for resizing any other database.

Placing Tempdb in RAM

Because Tempdb is so critical to server performance (since SQL Server uses it for just about every query/operation that occurs), Tempdb has a unique option of being able to be placed in RAM. Placing Tempdb in RAM dramatically improves the amount of time required for sorting.

Before you consider placing Tempdb in RAM, you must consider how Tempdb uses memory now available in the server. All RAM that's allocated to SQL Server is paged based on a least recently used (LRU) algorithm. What this means is that quite often, data in Tempdb will be in memory anyway as part of a regular page.

You shouldn't place Tempdb in RAM unless sufficient RAM is available to the server to handle its normal operations. It's unlikely that you'll perceive any benefit from Tempdb being in RAM unless the server has more than 64M of memory. With 128M or more of RAM, it's quite likely that you'll achieve performance gains by having Tempdb in RAM.

There are no data-integrity considerations with Tempdb in RAM, because none of the operations that occur on Tempdb are logged. What this means is that Tempdb is rebuilt every time the server is restarted, and any data that was in it—whether or not Tempdb was in RAM or on a disk device—is lost.

To place Tempdb in RAM, you must use the system-stored procedure sp_configure and restart the server. For more information on using sp_configure and other configurable options of SQL Server, refer to Chapter 16, "Understanding Server, Database, and Query Options"

Using Removable Media for Databases

A new feature—introduced in SQL Server 6.0 and continued with 6.5—enables databases to be placed on removable media such as CD-ROMs and magneto-optical (MO) drives. This feature permits the mass distribution of databases in a more friendly form than a backup tape, which the client must restore before using it. Also, a CD-ROM-based database is truly read-only and is a great way of securing data integrity.


SQL Enterprise Manager doesn't have any user interface to allow the creation of removable media, so all the work has to be done with ISQL. It's possible to create a device and database on a removable drive attached to the Windows NT system; however, this won't be the same as a removable-media-capable database and shouldn't be done.

Creating a Removable Database

A removable database has to be created in such a way that three devices are used: one for the system catalog, one for the user data, and one for the transaction log. Only the System Administrator (SA) can create removable databases.

SQL Server has a special system-stored procedure, sp_create_removable, that will create a database and devices that will be acceptable for use when creating a database for removable media purposes. It's important that you use this stored procedure because it will guarantee that the database created is usable on a removable device. The syntax for sp_create_removable is as follows:

sp_create_removable database_name, sysdevice_name_,
      'sysdevice_physical', sysdevice_size,
      logdevice_name, 'logdevice_physical', logdevice_size,
      datadevice1_name, 'datadevice1_physical',
      datadevice1_size [... , datadevice16_name,
      'datadevice16_physical', datadevice16_size]
The options for the system-stored procedure sp_create_removable are as follows:

  • database_name—The name of the database to be created.

  • sysdevice_name—The logical name to use for the device that will hold the system catalog tables.

  • sysdevice_physical—The physical device path and file name that will be used to store the data for the system catalog device.

  • syssize—The size in megabytes of the device.

  • logdevice_name—The logical name to use for the device that will hold the transaction log.

  • logdevice_physical—The physical device path and file name that will be used to store the data for the log device.

  • logsize—The size in megabytes of the device.

  • datadeviceN_name—The logical name to use for the device that will hold the user data. There can be up to 16 data devices.

  • datadeviceN_physical—The physical device path and file name that will be used to store the data for datadevice.

  • datasize—The size in megabytes of the device.

The following example creates a 1M data, log, and system catalog database and appropriate devices called MyRemovable:

/*-----------------------------
sp_create_removable MyRemovable, MySys, 
    'C:\SQL60\DATA\REMOVABLE\MYSYS.DAT', 1,
      MyLog, 'C:\SQL60\DATA\REMOVABLE\MYLOG.DAT', 1,
      MyData, 'C:\SQL60\DATA\REMOVABLE\MYDATA.DAT', 1
-----------------------------*/
CREATE DATABASE: allocating 512 pages on disk 'MySys'
Extending database by 512 pages on disk MyData
DBCC execution completed. If DBCC printed error 
    messages, see your System Administrator.
Extending database by 512 pages on disk MyLog
DBCC execution completed. If DBCC printed error 
    messages, see your System Administrator.
DBCC execution completed. If DBCC printed error 
    messages, see your System Administrator.

Using the Removable Database

While the database is in development, the following rules should be observed to ensure that the database is usable on removable media:

  • Keep the System Administrator (SA) as the Database Owner (DBO) of the database

  • Don't create any views, or any stored procedures that reference objects that can't be found in the database.

  • Don't add any users to the database or change any of the user permissions on the database. You can, however, add groups, and permissions may be assigned to those groups.

  • Don't alter any of the database devices created by sp_create_removable.

After database development is completed and you want to test that the database is acceptable for removable media, you should run the system-stored procedure sp_certify_removable. This procedure checks all the conditions required for a removable database and can automatically fix anything that it finds unacceptable. The syntax for sp_certify_removable is as follows:

sp_certify_removable database_name[, AUTO]
The options for the system-stored procedure sp_certify_removable are as follows:

  • database_name—This is the name of the database to be certified.

  • AUTO—If AUTO is specified, the stored procedure will correct any problems that it encounters. If you don't specify AUTO, you should correct any problems found using normal SQL Server tools.


If sp_certify_removable reports that it corrected anything when the AUTO flag was specified, it's highly recommended that you retest your application program to make sure that it's still compatible with the database. If no testing occurs, SQL Server could have rendered your application useless without you knowing about it.

As part of its execution, sp_certify_removable also takes offline all the devices that have been created for use in the removable database and makes them available for copying to the actual physical device. The output from sp_certify_removable is very important because it indicates the database characteristics to use when installing the removable database in to a SQL Server.

The following example shows the MyRemovable database being certified and brought offline:

/*-----------------------------
sp_certify_removable MyRemovable, AUTO
-----------------------------*/
DBCC execution completed. If DBCC printed error 
    messages, see your System Administrator.
DBCC execution completed. If DBCC printed error 
    messages, see your System Administrator.
DBCC execution completed. If DBCC printed error 
    messages, see your System Administrator.
File: 'C:\SQL60\DATA\REMOVABLE\MYLOG.DAT' closed.
Device dropped.
The following devices are ready for removal.  Please 
    note this info. for use when installing on a 
    remote system:

Device name  Device type  Sequence    Device frag. 
    used by database 
Physical file name
---------------------------------------------------
---------------------
MySys        System + Log 1           1 MB                         
C:\SQL60\DATA\REMOVABLE\MYSYS.DAT
MyData       Data         2           1 MB                         
C:\SQL60\DATA\REMOVABLE\MYDATA.DAT

Database is now offline
Closing device 'MyData' and marking it 'deferred'.
Device option set.
Closing device 'MySys' and marking it 'deferred'.
Device option set.

Installing the Removable Database

After a distribution media/device is made, you must install it on the target SQL Server. Installation is achieved by using the information supplied in the output from sp_certify_removable. This information should be distributed with each CD-ROM or device that the removable media is placed on.

The system-stored procedure sp_dbinstall is used to install a database from removable media. The syntax for sp_dbinstall is as follows:

sp_dbinstall database_name, device_name, 'physical_device',
      size, 'device_type'[, 'location']
The options for the system-stored procedure sp_dbinstall are as follows:

  • database_name—The name of the database to be installed. This can be any name that's valid for database and doesn't need to be the same as the original device.

  • device_name—The name of the database device that's to be installed.

  • physical_device—The full path information for the device on the removable media.

  • size—The size of the device being created.

  • device_type—The type of device being created on the target SQL Server. Valid types are 'SYSTEM' and 'DATA'. The 'SYSTEM' device must be created first. If more than one 'DATA' device exist, each should be installed by using sp_dbinstall.

  • location—The location to use on the local drive for the device being installed. The system device must be installed locally; however, the data devices can be left on the removable media, if necessary or desired.

The following example installs the system device created earlier by the system-stored procedure sp_certify_removable in the section Using the Removable Database from a CD-ROM in drive E:

sp_dbinstall MyRemovable, MySys, 'e:\MySys.dat', 
    1,'SYSTEM', 'c:\sql60\data\invsys.dat'
After the system device is installed, the data device is installed but left on the CD-ROM:

sp_dbinstall MyRemovable, MyData, 'e:\MyData.dat', 
    1,'DATA'
After all the data devices are installed, you need to place the database online so that users can access it. This is achieved by using sp_dboption. The following example shows you how to bring MyRemovable online:

sp_dboption MyRemovable, OFFLINE, FALSE
See the Chapter 16 section entitled Displaying and Setting Database Options

Uninstalling a Removable Media Database

If a removable media database is no longer required, you can remove it by using the system-stored procedure sp_dbremove. This procedure removes any entries from the system catalog relating to the database that was installed. The syntax for sp_dbremove is as follows:

sp_dbremove database_name[, dropdev]
The options for the system-stored procedure sp_dbremove are as follows:

  • database_name—This is the name of the database to be removed/dropped.

  • dropdev—If the keyword dropdev is supplied, sp_dbremove also removes any references in the system catalog to devices that were created as a result of the removable media database being created.

sp_dbremove doesn't remove the physical data files that were used to store the database devices. This needs to be done manually. See the Caution in the previous section entitled Using SQL Enterprise Manager to Remove Devices for more information about removing device files manually.

From Here...

In this chapter, you learned all about devices, databases, and segments. This chapter provided you with information on how to create all the fundamentals for your server. From here you should consider looking at the following chapters to further develop your SQL Server and application programming knowledge:


QUE Home Page

For technical support For our books And software contact support@mcp.com

Copyright © 1996, Que Corporation

Table of Contents

03 - Understanding the Underlying Operating System Windows NT

05 - Creating Database Tables and Using Datatypes