Chapter 9
Managing Databases



It is important to understand how to manage a database in SQL Server. Every object and its corresponding data revolves around the database. If a database isn't properly managed, you may experience system downtime, countless headaches, and loss of data.

A Database Primer

The following sections discuss the fundamental terminology and concepts necessary to manage a SQL Server database.

What Is a Database?

A database is an organized collection of data (see Figure 9.1). This collection of data is logically structured and systematically maintained. SQL Server extends the concept of a database by allowing you to create and store other types of objects, such as stored procedures, triggers, views, and other objects that interact with the data.

Figure 9.1.
A database.

What Is the Transaction Log?

The transaction log is the history of data modifications to a database (see Figure 9.2). Whenever you create a database, SQL Server automatically creates a corresponding database transaction log. SQL Server uses the transaction log to ensure transaction completeness and to incrementally restore data changes (see Chapter 14, "Backups," for more information on restoring the transaction log).

Figure 9.2.
A transaction.


The capability to guarantee transaction completeness helps separate SQL Server from less well-equipped RDBMS software. To SQL Server, virtually every data modification must have a starting point and an ending point. If the ending point isn't reached, SQL Server automatically reverses any changes that were made. Suppose that the power goes out to the server midway through a process that is deleting all the rows from a table. When SQL Server restarts, it automatically restores all the rows that had been deleted, thus returning the table to its original state before the delete process was run. Through the use of the transaction log, SQL Server can guarantee that all the work was done or that none of the work was done.

SQL Server automatically uses a write-ahead type of transaction log. This means that changes to the database are first written to the transaction log and then they are written to the database. Examples of database changes written to the transaction log include data modified through the UPDATE, INSERT, and DELETE commands; any type of object creation; and any security changes.

SQL Server automatically marks the starting point and ending point whenever you execute a command that performs data modifications. For greater control, you can define the starting point and ending point for a group of data modifications. This is often done when more than one set of data modifications occurs within a unit of work.

For example, if a user transfers $1,000 from checking to savings, you can use a user-defined transaction to ensure that the checking account was debited and the savings account was credited. If the transaction did not complete, the checking and saving accounts return to their original states (their states before the transaction began).

To specify the beginning of a user-defined transaction, use the following statement:

BEGIN TRANsaction [transaction_name]

To specify the end of a user-defined transaction, use the following statement:

COMMIT TRANsaction [transaction_name]

To roll back any changes made within a user-defined transaction, use the following statement:

ROLLBACK TRANsaction [transaction_name | savepoint_name]

The transaction log is a table within the database, named syslogs. The syslogs table has two columns: xactid (binary) and op (tinyint). The data contained in the xactid column is useless to look at because it is in binary format. Only SQL Server can understand the contents of the transaction log.


NOTE: No, you cannot disable the transaction log. This question is commonly asked when the log is not part of someone's backup strategy. Consequently, many people would rather not periodically dump the trans-action log. Unfortunately, you can't avoid having transactions written to the log. But you can have the transaction log truncated automatically in SQL Server; set the database option Truncate Log On Checkpoint to TRUE (see "Setting Database Options," later in this chapter, for more information about Truncate Log On Checkpoint).

How Databases and Devices Interact

Every database in SQL Server must use at least one device. Whenever you create a database, you are dedicating a predefined amount of device space to the database. Depending on your needs, you can create your entire database on one device or multiple devices (see Figure 9.3).

Figure 9.3.
The way in which databases and devices interact.

Estimating the Size of a Database

When you create a database, you must specify how much space to allocate to it. Other than "guesstimating," the second easiest way to estimate database size is to use the Estimator application included on the CD that accompanies this book (see Figure 9.4). The Estimator automatically calculates database storage requirements for a new table or an existing table.

Figure 9.4.
The Estimator application.


TIP: Use the Estimator application on the CD that accompanies this book to estimate the size of a database. The application works great when calculating the size of a new or existing database.

Estimating the Size of the Transaction Log

When estimating the size of the transaction log, a good starting point is to take the database size and multiply it by a factor of 10 to 25 percent. Whether you should use 10 percent or 25 percent (or some other factor) depends on the frequency between transaction log dumps and the average size of your transactions. With SQL Server, you can easily increase the size of your transaction log but you cannot easily de-crease it.

Database Pre-Flight Checklist

Before you create a database, review the following checklist:

Database Post-Flight Checklist

After you create a database, review the following checklist:

Database Basics

The following sections provide step-by-step instructions for managing a database.

Creating a Database

Before you can create tables and start to manage your data, you must create a database. Follow these steps to create a new database:

  1. From the Enterprise Manager, access the Server Manager dialog box and select a server.

  2. From the toolbar, click the Manage Databases toolbar button. The Manage Databases dialog box appears (see Figure 9.5).

    Figure 9.5.
    The Manage Databases dialog box.

  3. Click the New Database toolbar button at the top left corner of the Manage Databases dialog box. The New Database dialog box appears (see Figure 9.6).

  4. Enter a name for the database.

    Figure 9.6.
    The New Database dialog box.



    NOTE: Database names can be up to 30 characters long and must conform to valid SQL Server naming conventions (no spaces, table names must begin with a character, and so on).

    Be consistent when naming a database. Use all uppercase or lowercase characters. Database names are case sensitive. Don't forget that you may often need to type the database name in a SQL statement--this may make you think twice about using all 30 characters! Try to make the name meaningful and relatively short.
  5. Select a data device. This is the device on which the database will reside.


    CAUTION: Do not put the database on the master device! Doing so will cause a major headache if you ever have to recover the master device.
  6. Enter the size of the database (in megabytes). The size automatically defaults to the amount of available space on the selected device. Although you can override the default size, remember that it cannot be larger than the amount of available space on the selected device. If the device does not have enough free space for the database, you must increase the size of the device before proceeding (refer to Chapter 8, "Managing Devices," for more information on increasing the size of a device).


    NOTE: The minimum size of a database is 1M. In versions of SQL Server before version 6.x, the minimum size was 2M. The maximum size of a database is 1 terrabyte.



    TIP: Don't worry if you allocate too little or too much space to your database. You can go back and change the size of the database after it is created.

  7. Select a log device. Although it usually isn't recommended, you may set this option to None if you want to place the log on the same device as the database.

  8. Always select a separate log device when creating production databases. Doing so improves performance and allows the log to be backed up. If you do not place the log on a separate device, you will be unable to dump the transaction log.


    If you selected a log device in step 7, enter the size of the log (in megabytes). The size automatically defaults to the amount of available space on the selected device. Although you may override the default size, remember that it cannot be larger than the amount of available space on the selected device. If the device does not have enough free space for the log, you must increase the size of the device before proceeding.


    CAUTION: Under normal circumstances, do not select the Create For Load option when creating a database. This option is used only when restoring a database from a backup (see Chapter 15, "Recovery," for more information on restoring a database).

  9. Choose Create Now to create the database.


TIP: Always back up the master database after you create a new database. Doing so makes it easier to recover your database should the master database become damaged.


NOTE: The sa is the only user who can create a database, unless the statement permission is granted to another user (see Chapter 11, "Managing SQL Server Security," for more information about managing statement permissions).

You can use the following Transact SQL command to create a database:

CREATE DATABASE database_name
[ON {DEFAULT | database_device} [= size]
[, database_device [= size]]...]
[LOG ON database_device [= size]
[, database_device [= size]]...]
[FOR LOAD]

Viewing Information about a Database

After you create a database, you can view information such as data size, data space available, log size, and log size available. Follow these steps to view information about a specific database:

  1. From the Enterprise Manager, access the Server Manager window, open the Databases folder, and double-click a database. The Edit Database dialog box appears (see Figure 9.7).

    Figure 9.7.
    Viewing information about a database.

  2. From the Edit Database dialog box, you can view information about a database, set database options, and set statement permissions (see Chapter 11, "Managing SQL Server Security," for more information about statement permissions).

You can use the following Transact SQL command to view information about a database:

sp_helpdb [dbname]

Setting Database Options

Each database in SQL Server has its own database options. Follow these steps to set database options:

  1. From the Enterprise Manager, access the Server Manager dialog box, open the Databases folder, and double-click a database. The Edit Database dialog box appears.

  2. Select the Options tab in the Edit Database dialog box (see Figure 9.8).

    Figure 9.8.
    Setting database options on the Options tab.

  3. Check the options you want selected.

  4. The following sections describe each of the database options available on this tab.

  5. Click OK to save your changes.


TIP: When you change database options, the changes take effect immediately. You do not have to restart the server for the option to take effect.

Select Into/Bulk Copy
Default setting
: FALSE

The underlying consideration for how to set the Select Into/Bulk Copy option depends on how you handle the backup of the transaction log. Use the following information to help determine how you should set this option.

Set this option to FALSE if you depend on the transaction log for recovery (see Chap-ter 15, "Recovery," for more information on database recovery). This setting is typically used for production databases. When this option is set to FALSE, you cannot perform the following operations: Select Into, fast mode BCP, or Writetext.

Set this option to TRUE if you do not depend on the transaction log for recovery. This setting is typical for development databases and non-mission critical databases. By setting this option to TRUE, you can perform the following operations:


NOTE: If Select Into/Bulk Copy = FALSE, you get the following error message when you use the SELECT [column_list] INTO [destination_table] statement:

Msg 268, Level 16, State 2

You can't run SELECT INTO in this database. Please check with the Database Owner.



TIP: Those shops that use the transaction log for database recovery can temporarily set this option to TRUE, run a nonlogged operation (such as Select .. Into or fast-mode BCP), reset the option to FALSE, and dump the database. You must dump the database before you resume dumping the log. If you do not dump the database after performing a nonlogged operation, the next time you try to dump the transaction log, you receive an error message.

DBO Use Only
Default setting
: FALSE

When the DBO Use Only option is set to TRUE, only the database owner (DBO) can access the database. Use this option if you want to keep everyone but the database owner and sa out of the database.

No Checkpoint on Recovery
Default setting
: FALSE

The No Checkpoint on Recovery option controls the issuance of a checkpoint record to the log after the database has been recovered. In SQL Server terminology, the word recovered means that the database was successfully loaded during the startup of SQL Server. Typically, you leave this option set to FALSE unless you are dumping the log to a standby server. If you are dumping the log to a standby server, set this option to TRUE on the standby server's database. Doing so allows you to dump the log from the primary server and apply the log to the standby server.

Read Only
Default setting
: FALSE

When the Read Only option is set to TRUE, the contents of the database can be viewed but not modified.

Single User
Default setting
: FALSE

When the Single User option is set to TRUE, only one user at a time (including the sa) can be in the database.

Columns Null by Default
Default setting
: FALSE

The Columns Null by Default option determines whether a column is defined as NULL or NOT NULL when it is created. This option is implemented for ANSI compatibility.

The following examples explain the impact of setting this option.

Example A:

Columns Null By Default = FALSE
CREATE TABLE sales (sales_id int)

Result: The column sales_id is defined as NOT NULL.

Example B:

Columns Null By Default = TRUE
CREATE TABLE sales (sales_id int)

Result: The column sales_id is defined as NULL.


NOTE: Explicitly specifying a column as NULL or NOT NULL with the CREATE TABLE command overrides the Columns Null By Default option.

Truncate Log on Checkpoint
Default setting
: FALSE

When the Truncate Log on Checkpoint option is set to TRUE, the transaction log is automatically truncated when a CHECKPOINT is issued by the system. (A CHECKPOINT is issued by the system about once every minute.)

Set this option to FALSE if you use the transaction log as part of your backup recovery process.


NOTE: If you try to dump the transaction log when the database option Truncate Log on Checkpoint = TRUE, you will receive an error.

Set this option to TRUE if you are not concerned about using the transaction log as part of your backup recovery process. This option is useful for development databases or non-mission critical databases. When you set this option to TRUE, you significantly lessen the chance of the transaction log running out of space.

Other Database Options Three additional database options are not accessible through the Edit Database dialog box. You can use sp_dboption command to change any database option.


NOTE: The offline, published, and subscribed database options are new to SQL Server 6.x.

You can use the following Transact SQL command to set database options:

sp_dboption [dbname, optname, {TRUE | FALSE}]

Offline
Default setting
: FALSE

When the offline option is set to TRUE, the database is taken offline from the system. It is not recovered during system startup and remains inaccessible until it is placed online.

Set this option to FALSE unless you are working with a removable media database.
Published

Default setting
: FALSE

When the published option is set to TRUE, it permits the database to be published for replication. It does not perform the replication, it only allows it to be replicated.

When this option is set to FALSE, it prevents the database from being published. Set this option to FALSE unless you want this to be a published database.

Subscribed

Default setting
: FALSE

When the subscribed option is set to TRUE, it permits the database to be subscribed for replication. It does not perform the subscription, it only allows it to be subscribed to.

When this option is set to FALSE, it prevents the database from being subscribed. Set this option to FALSE unless you want this to be a subscribed database.

Expanding the Database

You can easily expand the size of the database after it has been created. SQL Server offers two approaches to increasing the size of a database. Before proceeding, you should review which approach best suits your needs. Approach 1: Use the Currently Assigned Device One way you can allocate more space to the database is to use more space on the device currently in use by the database (see Figure 9.9). The existing device must have sufficient space to accommodate the increase in database size (refer to Chapter 8, "Managing Devices," for more information on managing devices).

Figure 9.9.
Approach 1: Expanding the database.


TIP: To determine which device or devices are in use by a database, use sp_help:

sp_helpdb [dbname] Approach 2: Use Another Device

The second way you can expand the database is to assign another device to the database (see Figure 9.10). Doing so allows the database to grow in size by spanning multiple devices. The device must exist already, and it must have sufficient space to accommodate the increase in database size.

Figure 9.10.
Approach 2: Expanding the database.


Follow these steps to use another device to increase the size of a database:

  1. From the Enterprise Manager, access the Server Manager dialog box and select a server.

  2. Click the Manage Databases button on the toolbar; the Manage Databases dialog box appears. Double-click a database to display the Edit Database dialog box.

  3. From the Edit Database dialog box, select the Database tab. Click the Expand button. The Expand Database dialog box appears (see Figure 9.11).

  4. Select a data device.

  5. In the Size text box, enter the amount of additional space you want allocated to the database.


    CAUTION: Do not use the master device to gain additional database space. Doing so can cause a major headache if you ever have to recover the master device.
  6. Click Expand Now to save your changes.


NOTE: You can increase the size of a database up to a maximum of 32 times. After 32 times, you must drop and re-create the database.


TIP: Always back up the master database after you increase the size of a database. Doing so makes it easier to recover your database should the master database become damaged.

Figure 9.11.
The Expand Database dialog box.

You can use the following Transact SQL command to expand a database:

ALTER DATABASE database_name
[ON {DEFAULT | database_device} [= size]
[, database_device [= size]]...]
[FOR LOAD]

Expanding the Database Log

You can easily expand the size of the database log after it has been created. The concept behind expanding the database log is the same as for expanding the database: space can be added to the device currently in use by the log, or another device can be assigned to the log (see "Expanding the Database," earlier in this chapter, for more information).

Follow these steps to increase the size of the database log:

  1. From the Enterprise Manager, access the Server Manager dialog box and select a server.

  2. From the toolbar, click the Manage Databases button; the Manage Databases dialog box appears. Double-click a database to display the Edit Database dialog box.

  3. From the Edit Database dialog box, select the Database tab. Click the Expand button. The Expand Database dialog box appears.

  4. Select a log device.

  5. In the Size text box, enter the amount of additional space you want allocated to the database log (see Figure 9.12).

    Figure 9.12.
    Expanding the database log.



    CAUTION: Do not use the master device to gain additional database log space. Doing so can cause a major headache if you ever have to recover the master device.
  6. Click Expand Now to save your changes.


TIP: Always back up the master database after you increase the size of a database. Doing so makes it easier to recover your database should the master database become damaged.

You can use the following Transact SQL command to expand the database log:

ALTER DATABASE database_name
[ON {DEFAULT | database_device} [= size]
[, database_device [= size]]...]
[FOR LOAD]

Shrinking a Database

Follow these steps to decrease the size of a database:

  1. From the Enterprise Manager, access the Server Manager dialog box and select a server.

  2. From the toolbar, click the Manage Databases button; the Manage Databases dialog box appears. Double-click a database to display the Edit Database dialog box.

  3. From the Edit Database dialog box, select the Database tab. Click the Shrink button. The Shrink Database dialog box appears (see Figure 9.13).

  4. Enter the new database size in megabytes and click OK.

Figure 9.13.
Shrinking a database.


You cannot make the database smaller than the amount of space currently occupied by data and objects. (Refer to "Determining Database Size and Available Space," earlier in this chapter, for more information on database size.) Also, you can only decrease the size of the database; you cannot decrease the size of the database's log.


TIP: Always back up the master database after you create a new database. Doing so makes it easier to recover your database should the master database become damaged.

Use the following Transact SQL command to shrink a database:

dbcc shrinkdb (database_name [,new_size [[,'MASTEROVERRIDE']])


NOTE: The capability to shrink a database is new to SQL Server 6.x.

Renaming a Database

You can rename any database after it has been created. Only the sa can rename a database. Follow these steps to rename a database:

  1. Set the database you are going to rename to single-user mode. (Refer to "Setting Database Options," earlier in this chapter, for more information about single-user mode.)

  2. From the Enterprise Manager, access the Query dialog box and select the Query tab.

  3. Enter the following syntax:

    sp_renamedb oldname, newname

  4. Run the query.

  5. Reset the database to multi-user mode.


CAUTION: When renaming a database, watch out for SQL statements that explicitly refer to the database name; for example, SELECT * FROM pubs..authors. If you rename the pubs database to pub_db, you must remember to change the statement to SELECT * FROM pubs_db..authors. Some common areas in which you should look for database references are views, stored procedures, triggers, BCP scripts, and embedded SQL commands in applications.

Dropping a Database

When you drop, or delete, a database, you physically remove the database from the device(s) on which it resides and you destroy all objects contained within the database. Any device space allocated to the database is reclaimed. Only the sa and database owner can drop a database. Follow these steps to drop a database:

  1. From the Enterprise Manager, access the Server Manager dialog box, select a server, and open the Databases folder. Right-click the database you want to drop and select Delete from the shortcut menu (see Figure 9.14).

    Figure 9.14.
    Dropping a database.

  2. At the prompt, click Yes to delete the database.

You can use one of the following Transact SQL commands to shrink a database:

DROP DATABASE database_name [, database_name...]
sp_dbremove database[,dropdev]


TIP: Always back up the master database after you drop a database. Doing so makes it easier to recover your database should the master database become damaged. New to SQL Server 6.x is the capability to drop a damaged database with the DROP DATABASE command. In SQL Server versions before 6.x, you could use only the DBCC DBREPAIR (database_name, DROPDB) command. You can also use sp_dbremove to drop a database (sp_dbremove is new to version 6.x).



The Two Most Common Database Errors

Without a doubt, you will encounter the following errors when you work with SQL Server:

Msg 1105, Level 17, State 2

Can't allocate space for object `Syslogs' in database `[dbname]' because the `logsegment' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

Msg 1105, Level 17, State 1

Can't allocate space for object `[object name]' in database `[database name]' because the `default' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

DBAs new to SQL Server are frequently confused about why they get these error messages. The confusion occurs because you must preallocate space to the database and database log. If you do not allocate sufficient space, you get these error messages. Remember that the amount of remaining database space and log space has nothing to do with the amount of space left on your hard drive. Remaining database and log space is based on the amount of unused space allocated to the database or log.

The following sections explain in greater detail what each error means and how best to correct the error. The Database Log Ran out of Space Whenever you fill up the transaction log, you receive the following error message:

Msg 1105, Level 17, State 2
Can't allocate space for object `Syslogs' in database `[dbname]' because the `logsegment' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

To resolve the error, you must dump the log or increase the size of the log before continuing. (See "Expanding the Database Log," earlier in this chapter, for more information on increasing the size of the database log.)

To help avoid this error, do one of the following:

The Database Ran out of Space Whenever you deplete the available database space, you get this error message:

Msg 1105, Level 17, State 1
Can't allocate space for object `[object name]' in database `[database name]' because the `default' segment is full. If you ran out of space in Syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment.

To resolve this error, you must increase the size of the database or reduce the amount of space used by existing objects. (Refer to "Expanding the Database," earlier in this chapter, for more information on increasing the size of the database.)

Follow these suggestions to help avoid this error in the future:

Beyond the Basics

The following sections list helpful tips and tricks that can help improve the management of databases. These tips can help simplify database maintenance and improve database recoverability.

Tip 1: Place the Transaction Log on a Separate Device

For most databases, you will want to place the transaction log on a separate device. By segregating the transaction log and database onto separate devices, you gain the following benefits:

Tip 2: Document the Database

Always document the configuration of the database after it has been modified. This is in addition to dumping the master database. The easiest way to document the configuration of a database is to use the sp_helpdb command. I recommend saving the output from the command to a text file (preferably somewhere other than the server's hard drive).

Tip 3: Take Advantage of the model Database

Use the model database to simplify object creation. The model database allows you to define a template for the creation of new databases. When you create a new database, SQL Server copies the contents of the model database into the newly created database. This makes a handy mechanism for copying frequently used database options and objects into a new database. Anything you want automatically copied into a new database should be placed in the model.

NOTE: The model database is automatically created when you install SQL Server. It cannot be deleted. Changes to the model database will not impact existing databases. The model database is used only when creating new databases.

Following are common types of objects and settings that can be stored in the model database:

CAUTION: Be careful when you increase the size of the model database. It will become the minimum size for all new databases. Whenever SQL Server creates a new database, it copies the contents of the model database into the new database. Therefore, the new database cannot be smaller in size than the model database.



Segments


Segments
allows you to gain performance by placing frequently accessed tables and indexes on their own devices (see Figure 9.15). Using segments is an old trick that DBAs employ to improve read/write access to frequently used tables. For segments to be effective, they must have their own physical disk controller.

Figure 9.15.
Segments.

TIP: Before going to the trouble of using segments, you should explore Windows NT-based alternatives. Very few SQL Servers for NT installations use segments because they can be tedious to implement, difficult to maintain, and can complicate database recovery. Windows NT offers easier-to-use alternatives, such as Windows NT stripe sets--RAID 0 (striping) and RAID 5 (striping with parity)--and hardware disk arrays. Refer to Chapter 8, "Managing Devices," for more information on striped sets and hardware disk arrays. Windows NT striping performs as well as segments and is much easier to implement and maintain.



Creating a Segment


Use the sp_addsegment command to create a segment:

sp_addsegment segname, logical_name

In this statement, segname is the name you are going to assign to the segment; logical_name is the name of the device you are going to use for the segment. (The device must already exist. See Chapter 8 for more information on creating devices.)

To create a segment named seg_sales and using device1, use the following command:

sp_addsegment seg_sales, device1

NOTE: Each database in SQL Server can have up to 32 segments.



Creating a Table on a Segment


Use the CREATE TABLE command with the ON segment_name option to create a table on a segment:

CREATE TABLE [database.[owner].]table_name
({col_name column_properties [constraint [constraint [... constraint]]]
| [[,] constraint]}
[[,] {next_col_name | next_constraint}...]
)
[ON segment_name]

To create the sales table on the seg_sales segment, use the following command:

CREATE TABLE sales
(sales_id integer,
sales_amount money)
ON seg_sales

Creating an Index on a Segment

Use the CREATE INDEX command with the ON segment_name option to create an index on a segment:

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON [[database.]owner.]table_name (column_name [, column_name]...)
[WITH
[FILLFACTOR = x]
[[,] IGNORE_DUP_KEY]
[[,] {SORTED_DATA | SORTED_DATA_REORG}]
[[,] {IGNORE_DUP_ROW | ALLOW_DUP_ROW}]]
[ON segment_name]

For example, to create a clustered index on the seg_sales segment for the sales table, use the following command:

CREATE CLUSTERED INDEX sales_id_idx
ON sales(sales_id)
ON seg_sales

Increasing the Size of a Segment

Use the sp_extendsegment command to increase the size of a segment:

sp_extendsegment segname, logical_name

For example, to extend the seg_sales segment to device2, use the following command:

sp_extendsegment seg_sales, device2

Viewing Information about a Segment

Use sp_helpsegment to list information about a particular segment or all the segments in a database:

sp_helpsegment [segname]

For example, to view information about the seg_sales segment, use the following command:

sp_helpsegment seg_sales

Use sp_helpdb to view which segments are in use for a particular database:

sp_helpdb [dbname]

To view segment information within the sales database, use the following command:

sp_helpdb sales

Dropping a Segment

Use the sp_dropsegment command to drop a segment:

sp_dropsegment segname

NOTE: You cannot use the sp_dropsegment command if an object resides on the segment you are trying to drop. You must drop the object before dropping the segment.

For example, to drop the seg_sales segment, use the following command:

sp_dropsegment seg_sales

Database-Removable Media

New to SQL Server 6.x is the capability to create database-removable media. This enables you to distribute databases on media such as CD-ROM, floppy drives, WORM drives, or optical drives. You can also create new databases from removable media.

NOTE: The database-removable feature provides new possibilities for archiving and distributing databases.



Creating a Removable Database Media

Use the sp_create_removable command to create a removable database:

sp_create_removable dbname, syslogical, `sysphysical', syssize,
loglogical, `logphysical', logsize, datalogical1, `dataphysical1', datasize1
[... , datalogical16, `dataphysical16', datasize16]

For example, to create a 4M sales database for distribution on removable media, with a 2M device for the system catalog, and a 2M device for the transaction log, use the following command:

sp_create_removable sales, salessys, `c:\mssql\data\salessys.dat',2,
saleslog,'c:\mssql\data\saleslog.dat',2,
salesdat,'c:\mssql\data\salesdat.dat',4

Stranger than Fiction!
The sp_create_removable example in the SQL Server documentation is incorrect.

Incorrect Example
: sp_create_removable inventory, invsys, `c:\mssql\data\invsys.dat, 2',
invlog,'c:\mssql\data\invlog.dat',4
invdata,'c:\mssql\data\invdata.dat',10,

Correct Example: sp_create_removable inventory, invsys, `c:\mssql\data\invsys.dat',2,
invlog,'c:\mssql\data\invlog.dat',4,
invdata,'c:\mssql\data\invdata.dat',10



Certifying a Database for Removable Distribution

When you are ready to distribute the database, use the sp_certify_removable command. This command takes the database offline, thus making it available for distribution:

sp_certify_removable dbname[, AUTO]

The optional AUTO parameter automatically drops all database users and transfers ownership of the database to the sa.

To certify the sales database for distribution, use the following command:

sp_certify_removable sales, AUTO

TIP: Be sure to save the information returned from the sp_certify_removable procedure. Whenever you distribute a removable database, you should include the output from the sp_certify_removable command. This information will be needed by anyone who plans to install the database. In SQL Server version 6.5, sp_certify_removable automatically generates a text file containing the information returned from the procedure. The information is saved in a file named CertifyR_[dbname].txt in the LOG subdirectory in the SQL Server installation directory structure. In version 6.0, the output from sp_certify_removable must be manually saved to a text file.

The following is sample output from the sp_certify_removable command:

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:\mssql\data\saleslog.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. Physical file name used by database
----------------------------------------------------------------------------------
salessys System + Log 1 2 MB c:\mssql\data\salessys.dat
salesdat Data 2 4 MB c:\mssql\data\salesdat.dat
Database is now offline
Closing device `salesdat' and marking it `deferred'.
Device option set.
Closing device `salessys' and marking it `deferred'.
Device option set.

Installing and Activating a Removable Media Database

When you are ready to install a removable media database, use the sp_dbinstall command. This command copies the system tables and transaction log from the removable media to the hard drive and optionally copies the data device to the hard drive:

sp_dbinstall database,logical_dev_name,'physical_dev_name',size,
`devtype'[,'location']

To install the system catalog tables and transaction log from the removable media, use the following syntax:

sp_dbinstall sales,salessys,'e:\salessys.dat',2,
`SYSTEM','c:\mssql\data\salessys.dat'

To install the data tables from the removable media, use the following syntax:

sp_dbinstall sales,salesdat,'e:\salesdat.dat',4,
`DATA','c:\mssql\data\salesdat.dat'

NOTE: The sp_dbinstall command is run for each database device. For this example, the sales database used two devices: a system plus log device and a data device. To install the entire database to the server's hard drive, run the sp_dbinstall option twice--once with the devtype parameter set to SYSTEM and once with the devtype parameter set to DATA.

After the database has been successfully installed, you must place it online. To place a database online, use sp_dboption with the offline parameter:

sp_dboption dbname, offline, {TRUE | FALSE}

For example, to bring the sales database online, use the following syntax:

sp_dboption sales, offline, FALSE

NOTE: Combining the offline and FALSE parameters brings a database online. To take the database offline, use the offline and TRUE parameters.

Deleting a Database Installed from Removable Media

Use the sp_dbremove command to delete a database installed from removable media:

sp_dbremove database[,dropdev]

NOTE: The optional parameter dropdev drops any devices used by the removable database media. It does not delete the actual device from the hard drive.

Following is an example of the sp_dbremove command:

sp_dbremove sales, dropdev

Between the Lines

Following are some important notes to remember when managing databases:

Summary

Basic database management techniques combined with supplemental tips provide you with the skill set to intelligently manage a database. Remember that intelligent database management is the key to keeping your database up and running. In the next chapter, you learn how to manage users.


DISCLAIMER


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select
Talk to Us.

© 1997, QUE Corporation, an imprint of
Macmillan Publishing USA, a Simon and Schuster Company.