Teach Yourself Microsoft SQL Server 6.5 in 21 Days

Previous chapterNext chapterContents


Day 4

Devices and Databases


Yesterday in "SQL Server Tools and Utilities," you learned about the various programs that ship with SQL Server. You learned about Enterprise Manager and how to register and manage SQL Servers. You will use Enterprise Manager a lot in today's lesson.

Today's subject deals with devices and databases. You first will learn about the two types of devices. The discussion then focuses on the default devices and databases created when SQL Server is installed. Creating and setting options for databases is the next topic. Finally, you will learn about how database objects, such as tables and indexes, are stored in the database.

Introduction to Devices

There are two basic types of devices: backup devices (also called dump devices) and database devices.

Both types of devices are directly associated with a file. Backup devices are simply files that hold backups of a database; database devices are simply files that have a name, size, and device number assigned to them. Details on the two type of devices follow.

Backup Devices (Dump Devices)

Backup devices are used to store database and transaction log backups. Earlier versions of SQL Server referred to these type of devices as dump devices, but SQL Server 6.5 refers to these type of devices primarily as backup devices. Backup devices can point to a file on a hard disk, to a backup tape, to floppy disks, and can even represent a network path to a file on remote servers.

Backup devices and how to best use them are covered in depth in Day 8, "Backing up and Restoring," and are not covered further in today's lesson.

Database Devices

A database device is a set of configurations for a file that will contain one or more databases. Database devices are used to store databases and database transaction logs. A database device has the following characteristics:

You need to keep a couple of issues in mind as you work with database devices: There is a limitation of 255 database devices per SQL Server. A database device can be made larger, but it cannot be made smaller.

Device Logical Names

Device names must be unique within SQL Server, must be a maximum length of 30 characters, must contain no spaces or punctuation, and must start with letters or numbers.

Check with your database administrator for device naming conventions. It is recommended that you identify your devices in some manner--for example, placing the word log in all devices that will hold transaction logs.

Valid device names follow: payroll_data

payrolldata

payroll_log payroll_indianapolis_data Here are some invalid device names: payroll data

payroll"data payroll!

Virtual Device Numbers

Each database device must have its own VDEVNO as SQL Server will display it. The VDEVNO is simply an identifier used internally by SQL Server. If you use SQL Enterprise Manager to create the device, it selects an available number for you. If you use the DISK INIT statement, you need to use sp_helpdevice to find an available number. Valid numbers are 1 through 255. 0 is reserved for the master device, and numbers 127 and 126 are reserved for the msdb and msdb log devices, respectively.

The SQL Server stored procedure sp_helpdevice displays information on all the devices. You can get more detailed information on a single device by executing

sp_helpdevice logicaldevicename

To execute sp_helpdevice and find a valid device number, follow these steps:

1. Start ISQL.

2. Execute the sp_helpdevice command.

3. Scroll over to the right until the device _ no column is visible.

4. Note which numbers are in use and select one that is not.

sp_helpdevice

device_name    physical_name
-----------    -------------
diskdump       null
master         D:\MSSQL\DATA\MASTER.DAT
MSDBData       D:\MSSQL\DATA\MSDB.DAT
MSDBLog        D:\MSSQL\DATA\MSDBLOG.DAT
RECEIVERDEV    D:\MSSQL\DATA\RECEIVERDEV.DAT

Size of the Database Device

The amount of storage reserved in the initialization of the device is measured in 2KB blocks or pages for the DISK INIT statement and in megabytes for the SQL Enterprise Manager Manage Devices dialog. How large you should make the device depends on several factors. You must know the size of the database(s) that will be placed on the device, as well as how much you think the database(s) will grow. You also need to know how much available space you have and whether this device will store more than one database. You'll learn how to estimate the size of a database later today.


WARNING: You must understand the different measurements required by different SQL Server statements. Sometimes you'll use megabytes, and other times you'll measure according to pages. Using an incorrect type of measurement could have serious complications. So, unless you work with SQL Server measurements every day, (or you like to restore your database frequently), refer to the SQL Server Books Online if you have questions about which measurement to use.

Default Database Devices

When SQL Server is installed, three default database devices are created:

These three system devices are located in the \mssql\data folder. When you install SQL Server, you can specify the path and size for the master device, although using default folders is always a good idea.

Note that a device has a logical name, as well as a complete path to the physical file. The logical name is used in most commands; the physical path is used only when creating devices. Devices also have a number, which SQL Server uses to track the individual devices.

The default devices are installed initially to hold the system databases; usually, you shouldn't tamper with these devices. SQL Server enables you to easily create new database devices, which you then can use to hold your user databases.

Next, you'll learn about creating database devices.

Creating Database Devices Using Transact-SQL

You use the DISK INIT command to create database devices using Transact-SQL.

The mandatory parameters of the DISK INIT command follow:

DISK INIT
NAME = `logical_name',
PHYSNAME = `physical_name',
VDEVNO = virtual_device_number,
SIZE = number of 2k blocks

Here is a sample that uses DISK INIT:

DISK INIT
NAME = `PAYROLL',
PHYSNAME = `c:\MSSQL\data\payroll.dat',
VDEVNO = 6,
SIZE = 5120

This example creates a 10MB physical file (5120 * 2KB) called payroll.dat on the C: drive in the folder MSSQL\data. It has the device number 6 and appears in SQL Enterprise Manager as PAYROLL in the device hierarchy.

Here is an additional, non-mandatory parameter of the DISK INIT statement:

[, VSTART = virtual_address]

This parameter should be left out, because it defaults to 0. You should not specify this parameter unless you are doing advanced troubleshooting. See SQL Server Unleashed, by Sams Publishing, for more details.

Each time a DISK INIT statement is issued, a row is added to the sysdevices table in the master database. Therefore, it is important that you back up the master database after each use of DISK INIT.

Only the System Administrator (SA) has permission to execute the DISK INIT statement. This permission cannot be transferred.

DISK INIT Error Messages

A few problems can occur with SQL Server when it comes to disk initialization. The most common problems are insufficient storage space, invalid file or directory names, or duplicate filenames.

Usually, the error message states that the DISK INIT command encountered an error. You can view the SQL Server Error Log, or you can inspect the hard drive using operating system tools (Windows Explorer on NT) to see whether the cause is due to any of the preceding reasons.


TIP: One of the most common errors occurs when you attempt to re-create a device you have used previously. If you used Enterprise Manager to drop the device, the physical file was not deleted when SQL Server dropped all internal references to the device. To make a device that uses the same filename again, you must use the operating system utilities (such as Explorer) to delete the file.

Creating Database Devices Using SQL Enterprise Manager

To create a device in SQL Enterprise Manager, you first take one of these actions:

In the Manage Database Devices dialog, click the first button on the toolbar to display the New Database Device dialog. Then follow these steps:

1. Enter the logical name in the Name text box (remember that no spaces or hyphens are allowed and no more than 30 characters).

2. Enter the physical name in the Location text box. (Enterprise Manager defaults to the \mssql\data folder if you select the drive on which SQL Server is installed.)

3. In the Size text box, enter the size in megabytes (remember that Enterprise Manager uses megabytes for sizes).

4. Click Create Now to create the device.

Removing Database Devices Using Transact-SQL

You can remove a device by using the sp_dropdevice command or by using SQL Enterprise Manager.

The following code shows the syntax for dropping or removing a device by using the sp_dropdevice command:

sp_dropdevice logical_name  [,DELFILE]

Dropping the device does not delete the file; it merely removes the record of the device from the sysdevices table of the master database. To delete the physical operating system file, issue the sp_dropdevice command with the DELFILE command, or use the operating system utility to delete the file. Here are two examples:

sp_dropdevice payroll
sp_dropdevice accounting1, DELFILE


TIP: If databases still exist on the device, the device cannot be removed except through the operating system services; this is dangerous! You first must remove the databases.

Storing SQL Server itself and all the database devices on NTFS partitions enables you to secure the files using the Windows NT security model.

Removing Database Devices Using Enterprise Manager

To remove database devices by using Enterprise Manager, follow these steps:

1. Expand the Device folder in Enterprise Manager.

2. Right-click on the device you want to remove.

3. Choose Delete, as shown in Figure 4.1.

Or, you can use this procedure:

1. Select the device you want to delete.

2. Press Delete.

Figure 4.1. Deleting a device.

Expanding Database Devices Using Transact-SQL

A device cannot be reduced in size. However, it may be expanded. To expand a device, use the DISK RESIZE command or Enterprise Manager. The following shows the syntax for the DISK RESIZE command:

DISK RESIZE
NAME = logical device name
SIZE = final device size

The final device size is the final size in 2KB pages--not the increase in size. In other words, if your device PAYROLLDATA is 10MB or 57202KB blocks and you want to add 5MB to its size, you can use this command:

DISK RESIZE
NAME = PAYROLLDATA
SIZE = 7680f

Expanding Database Devices Using Enterprise Manager

To expand a device using Enterprise Manager, follow these steps:

1. Expand the Device folder.

2. Double-click the device you want to expand.

3. Change the final size to whatever size (in megabytes) you want, as shown in Figure 4.2.

Figure 4.2. Editing Devices.

Specifying a Default Device

Devices are created to store databases on them. Devices can be specified as default devices, meaning that databases are created on them if a particular device is not specified when a database is created.


NOTE: Only the SA can change the default status of a database device.

The status of a device can be set by using Transact-SQL or by using Enterprise Manager.


TIP: Each database should be assigned one or more specific devices in a logical manner to improve performance and ease maintenance. In other words, don't use the default device when you create databases.

One of the best ways to deal with default devices is to un-tag the master device as a default device and make sure that no new devices are tagged as default devices. This forces anyone creating databases to list specific devices on which the databases should be created instead of taking the lazy way out (by putting databases on default devices).

Setting a Default Device Using Transact-SQL

To set a device's default status, use this syntax:

sp_diskdefault logical_devicename, default on | default off

To set the master database default status to off, use this syntax:

sp_diskdefault master, default off

Setting a Default Device Using SQL Enterprise Manager

To set a default device using SQL Enterprise Manager, you can follow one of two procedures:

1. Expand the Database Devices folder.

2. Select the device you want to set.

3. Double-click the device.

Or,

1. Right-click the device and choose Edit from the pop-up menu.

2. Check or clear the Default check box on the Edit Database Devices dialog box.

Multiple Default Devices

More than one device may have its default status turned on. If a database is created without a device specified, (thus using the default device), SQL Server selects the first default device in alphabetical order, uses all the space on it, uses the space on the second device (if necessary), and so on until the database is created or you run out of default devices.

Database Device Mirroring

You can use several options to help ensure the reliability of the data for SQL Server and database devices. Because SQL Server is fully compatible with Windows NT, SQL Server can use Windows NT's capability to protect hard drives from failure. Windows NT's use of hard drive mirroring, duplexing, and RAID 5 (stripe with parity) are all covered in Day 8, "Backing up and Restoring."

SQL Server enables you to mirror a single file by giving you the capability to mirror a database device to another hard drive. Each time data is written to or deleted from the original device, it also is written to the mirrored copy.

If the original (or mirrored copy) becomes corrupt, SQL Server continues to use the remaining database device.


WARNING: Although device mirroring sounds good, it is highly recommended that you use more complete and robust hard drive protection, such as that supplied by Windows NT or dedicated RAID 5 hardware. Support for device mirroring may be dropped from future versions of SQL Server.

Databases

SQL Server stores data and related objects in a database. A database is stored on one or more devices. In the next section, you'll look at the default databases that SQL Server comes installed with, as well as creating new databases and resizing existing ones.

Default System Databases

When SQL Server is installed, four system databases (and one sample database) are created and placed on the system devices (master, msdbdata, and msdblog). The default databases follow:

The master Database

The master database contains all the internal settings used by SQL Server to keep track of devices, databases, user logins, and configurations. Backing up and restoring the master database is covered in Appendix A, "Restoring the Master Database."

The msdb Database

The msdb database is used to store tasks, alerts, and operators used in automating certain SQL Server operations. The msdb database and its role in automation is covered in Day 18, "SQL Server Automation."

The tempdb Database

The tempdb database is used to hold temporary objects such as temporary tables, and is also used by SQL Server as a sorting area for queries. Configuring the tempdb database is covered in Day 20, "Performance Tuning and Optimization."

The Model Database

The Model database is the template database used whenever any new databases are created. It consists of 18 system tables that hold information about the database and all objects, users, and security it contains. Because this database serves as a template for all new databases, the 18 system tables occur in each user database. Table 4.1 lists the system tables and the information they contain.

Table 4.1. System tables.

Table Information
sysalternates Each SQL Server user mapped to a database user
syscolumns Each column in a table or view, and each parameter in stored procedure
syscomments Each view, rule, default, trigger, and stored procedure that has a definition statement
sysdepends Each procedure, view, or table referenced by a procedure, view, or trigger
sysindexes Each index, each table without a clustered index, and each table with text or image data
syskeys Each foreign, primary, or common key
syslogs The Transaction Log
sysobjects Each table, view, stored procedure, rule, trigger, default, and log (in tempdb, you will find rows for both permanent and temporary objects)
sysprocedures Each view, rule, trigger, default, and stored procedure
sysprotects User permission information
syssegments Each segment
systypes Each default system-supplied and user-defined datatype
sysusers Each user allowed in the database
sysreferences Each reference constraint created
sysconstraints Each constraint created
sysarticles Each article created for replication
syspublications Each publication created
syssubscriptions Each subscription subscribed to


NOTE: Any changes made to the model database affect only new databases--not any databases that already exist.

The pubs Database

The pubs database is a sample database installed by default. Because it is a good example of a relational database, many of the examples and exercises in this book are written to use the pubs database.


TIP: If the pubs database becomes corrupted or is deleted, you can reinstall it by running the Instpubs.sql script found in the \mssql\install folder.

Creating Databases

After you create your database devices, you are ready to create your databases. Creating a database in SQL Server consists of specifying where the database will reside, its size, and (optionally) its database options.

It is important to understand the database components that are placed in the space allocated for the database. Database components can be grouped into the following two major categories:

A database and its Transaction Log may reside on the same database device or on separate database devices. In addition, a device may contain multiple databases.

Transaction Logs

A Transaction Log records the transactions against the database. Each transaction is recorded first in the Transaction Log for that database, cached in RAM, and then eventually recorded in the actual database. This gives SQL Server the advantages of using a data cache (speed), without the corresponding disadvantage of having data reside only in volatile RAM. If SQL Server stops unexpectedly, data can be recovered from the Transaction Log and used to bring the database up to date. You can not (and should not) prevent the creation or use of the Transaction Log.

See Day 8, "Backing up and Restoring," for more information on backing up and restoring the database and the Transaction Log.

When the database is created, the syslogs table, which is the Transaction Log, is placed in the same database device as the tables and data unless a separate database device is specifically assigned to the Transaction Log.


WARNING: It is highly recommended that you put the Transaction Log on its own database device. Doing so not only improves performance, but it makes data recovery--in case of a catastrophic failure--more flexible and robust. In certain kinds of failures, recovery is not possible unless the Transaction Log is on a separate device.

Although it is not necessary to place the Transaction Log on a separate device, doing so offers several advantages:

How big should the log be? This is a difficult factor to predict. If you make your log too small, you run the risk of filling the log prematurely. All processing comes to a halt until the log is cleared. If you make your log too large, you waste resources.

Ideally, the Transaction Log should be large enough to hold all the transactions that transpire between the times the log is cleaned out without wasting too much space. A good number to start with is 25 percent of the size of the database. If you will be updating large amounts of data in a single transaction, the log must be large enough to hold the entire transaction (which includes data as well as instructions) and might need to be several times the size of the data to be updated. Of course, you could break up the transactions into several, smaller sized transactions to keep the Transaction Logs smaller.

You must manage the space used by the Transaction Log by occasionally removing old transactions from the log. You easily can schedule this process (see Day 18, "SQL Server Automation"). Day 8, "Backing up and Restoring," covers the Transaction Log in more detail and how to maintain adequate space for it.

Creating Databases Using Transact-SQL

The syntax for creating databases using the SQL commands follows:

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

database_name needs to be 30 characters or less, it should contain no spaces or punctuation, and it must be unique on the SQL Server. The database name may be typed frequently, so it's probably to your advantage to create short, meaningful names.

database_device specifies on which device (or devices) to put the database. If you do not specify which device to create the database on, the database is placed on the default device (see Day 5, "Creating Tables," for a discussion of default database devices). You may place a database on one or many devices.

size is the amount of space on each device that should be allocated for the database; it is indicated in megabytes.

LOG ON specifies a separate device on which to place the Transaction Log.

The FOR LOAD parameter is used only if you are creating the structure of the database so that you can restore it from a backup (for example, the database may have been lost or corrupted). To restore the database from a backup, it must be restored to an existing database. Specifying the FOR LOAD option creates a database that can be used only to load from a backup--normal operations will not work on the database until it is loaded from a backup.

CREATE DATABASE payroll
ON payroll = 14
CREATE DATABASE accounting
ON accounting1 = 10,
      accounting2 = 10,
      accounting3 = 12

The first example creates a 14MB database on device payroll. The second example creates a 32MB database on devices accounting1, accounting2, and accounting3. Both examples do not specify a separate device for the Transaction Log, so the syslogs table is placed on the databases device.

Do you get the idea that database design is a combination of administrative tasks, data design, and use? This is a factor that many system administrators and database designers overlook. All three factors are important elements to consider.

Here are some examples of the CREATE DATABASE command:

CREATE DATABASE payable
ON payable_data = 50
LOG ON payable_log = 10

This example creates a 50MB database on the payable_data device and places its Transaction Log (the syslogs table) on the payable_log device.

Creating Databases Using Enterprise Manager

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

1. Expand the Database folder in the SQL Enterprise Manager window.

2. Right-click the folder and choose Edit to display the Manage Database dialog box.

3. Click New Database.

4. In the Name box, enter the name of the database (see Figure 4.3).

5. Select the database device for data from the Data Device drop-down list box.

6. In the corresponding Size box, enter the size in megabytes. The default is the size remaining on the device.

7. In the Log Device drop-down list box, select the log device (the default selection is none--leave this option selected if you want the log to be on the same device as the data).

8. In the corresponding Size box, enter the size in megabytes of the log. The default is the size remaining on the device.

9. If you want to create the database for load, enable the Create for Load check box.

10. Click Create Now to create the database. Or, you can click Schedule to schedule database creation for a later time (discussed in detain in Day 18, "SQL Server Automation").

Figure 4.3. Creating a new database.

Creating a Database on Multiple Devices Using Enterprise Manager

When creating a new database with SQL Enterprise Manager, you can select only one device for the data and one device for the log. If you want the database to be spread over multiple devices, you must use the CREATE DATABASE statement and specify the multiple devices. Or, you can use Enterprise Manager to create the database on a single device and then expand the database across multiple devices, as described in the following steps:

User-defined databases can be assigned a database device. If no device is defined, they are placed on the default device.

Follow these steps to spread the database over several devices:

1. Expand the Database folder.

2. Right-click on the database you want to edit.

3. Choose Edit.

4. Click Expand.

5. Select the database device to expand onto.

6. Enter the number of megabytes to reserve for this database.

7. Click Expand Now.

Database Options

You may set optional information on the database. These options are set to false in the default model database and in each user database created from model. If you set database options in model, they impact each new database created. Optionally, you can set the options for each database after it is created.

This section discusses the database options and their consequences.

Columns Null By Default

By default, columns created in SQL Server tables do not permit Null values. If this option is set to true, any column created in a SQL Server table in that database allows Null values.

DBO Use Only

When this option is set to true, only the database owner (DBO) may work with this database. This option often is set when loading data by bulk copy methods, during development, or when you want to keep other users out of the database. A common error is to forget to turn off this option after the procedure is complete.

No Checkpoint on Recovery

By default, a checkpoint record (see "Truncate Log on Checkpoint" later in this section) is written to the Database Log after the completion of a recovery process. This is necessary in order to maintain data integrity, but in some circumstances, it interferes with database operations. If the database is replicated on another SQL Server, for example, the replicated database should have this option set to true, because a recovery of a replicated database would not match its subscribed database on the other server.

Offline

Removable databases (databases that can be copied to a CD-ROM and then sent to another SQL Server) can be created on a SQL Server, but they must be taken offline before being placed on removable media. An offline database is not accessible to SQL Server or its users and thus cannot be modified.

Published

The database may be a source database for replication.

Read Only

Database users may select or look at data in the tables, but they cannot make modifications. This option often is used in decision support databases to improve performance. When a database is set to Read Only, no locking mechanisms can be applied, so time is not spent locking and unlocking rows of data.

SELECT INTO/BULKCOPY

Normally, when data is added to a table, each add transaction is logged. When data is added to a table by using the SELECT INTO statement or by using the Bulk Copy Program (BCP), a significant performance improvement can be gained by first setting this option to true. Using BCP with this option is covered in Day 9, "Importing and Exporting Data."

Single User

Database access is limited to one user at a time.

Subscribed

The database becomes a destination point for replication.

Truncate Log on Checkpoint

A system-generated checkpoint writes to disk all cached pages that have been modified. If this switch is set to true, the Transaction Log is truncated after each system-generated checkpoint.

Setting this option to true can have consequences for data recoverability, because the Transaction Log no longer can be backed up or restored. See Day 8, "Backing up and Restoring," for more details on backing up and restoring the Transaction Log.


NOTE: System databases have the following options set. The master, model, and pubs databases have the Truncate Log on Checkpoint option set to true. The tempdb database has the Select Into/Bulk Copy option set to true.

Setting Database Options Using sp_dboption

Use the sp_dboption stored procedure to set database options. Only the SA and the database owner can set database options. The sp_dboption must be issued from the master database.

The syntax for the sp_dboption command follows:

sp_dboption database_name, option, {true | false}

This sets the payroll database to single-user mode:

use master
go
sp_dboption payroll, "single user", true
go

This sets the accounting database so that only the DBO can use it:

use master
go
sp_dboption accounting, "dbo use only", true
go

Setting Database Options Using Enterprise Manager

You can use Enterprise Manager to change major database options. If the option you want to change is not listed when using Enterprise Manager, you must use Transact-SQL (see "Creating Databases Using Transact-SQL," earlier in this chapter).

To change options using SQL Enterprise Manager, follow these steps:

1. Expand the Database folder in SQL Enterprise Manager.

2. Double-click the database for which you want to change options.

Or,

1. Right-click the database and choose Edit.

2. Select the Options tab on the Edit Database dialog.

3. Select the check box for the option you want to make true (see Figure 4.4).

Figure 4.4. Setting database options.

Database Information

You can get information about current databases by directly examining the system databases. Alternatively, several system-stored procedures exist for obtaining information about databases.

The sp_helpdb Stored Procedure

The sp_helpdb stored procedure is very handy to check the status of databases.

sp_helpdb database

If you issue this command without a parameter, the sp_helpdb command provides information about all the databases on the server. If you include a database after the command, sp_helpdb provides more detailed information about only the database named.

sp_helpdb payroll

name        db_size    owner    dbid     created    status
----        -------    -----    ----     -------    ------
payroll     12         sa       9        Jan 9      no options set
device fragments     size    usage        free kbytes
----------------     ----    -----        -----------
payroll_data         2 MB    data         480

The device fragments information is a list of database devices that tells you about specific allocations of space on each device and whether that space is used for data, the log, or both.

The sp_helplog Stored Procedure

The sp_helplog stored procedure shows information on the Transaction Log.

sp_helplog
 `in database `master' the log starts on device master'

The format of sp_helplog is different than sp_helpdb. It takes no parameters. You must switch to the right database before issuing the sp_helplog command to get log information about that particular database.

System Tables Used in Tracking Databases

SQL Server uses three system tables (all contained in the master database) to track devices and databases.

The sysdevices System Table

Information in sysdevices is used to build information about the database in sysusages. cntrltype indicates whether the device is a database device or a backup (dump) device. A value of 0 indicates a database device. low represents the first virtual page number and high represents the last virtual page number. You determine the number of pages on a device by calculating high - low + 1. Multiply this solution by 512 to determine the number of megabytes.

select cntrltype, low, high, name
from sysdevices
where cntrltype = 0
order by low

The sysusages System Table

sysusages contains information on the first database page number or logical page start within the database, the number of contiguous pages, and the vstart or virtual starting page number. The vstart enables the mapping of database fragments to a specific virtual device. If the vstart value falls between the low and high page numbers for the device in sysdevices, the fragment is located on the device.

The sysdatabases System Table

In sysdatabases information includes the database name, the ID of the database owner, status flags, and the date of the last dump. SQL Server stores the various statuses of a database as the additive sum of all the status flags that apply to the database. If a database is marked as both No Checkpoint on Recovery (16) and Database Not Recovered Yet (64), for example, the value of the status is 80 (16 + 64). Table 4.2 lists the database status flags and their values.

Table 4.2. Database status flags.

Value Status
2 Database in Transition
4 Select Into/Bulk Copy
8 Truncate Log on Checkpoint
16 No Checkpoint on Recovery
32 Crashed During Load
64 Database Not Recovered Yet
128 Database Is in Recovery
256 Database Suspect
1024 Read Only
2048 DBO Use Only
4096 Single User
8192 Database Not Being Checkpointed
16384 ANSI Null Default
32768 Emergency Mode



WARNING: Do not attempt to change the status bits manually! Although it is possible to do so, you have to be very careful that the value you change it to exactly specifies all the options you want (and don't want). It is not recommended that you do this.

Deleting Databases

The DROP DATABASE command is used to delete (or drop) a database. This removes the entries from the system tables as well as all the objects (tables and so on) and the log. The space can be used for other databases. You must be the DBO or SA to drop a database.

The syntax for the DROP DATABASE command follows:

DROP DATABASE database_name [, database_name...]

This command drops the payroll database:

DROP DATABASE payroll

Removing Corrupt or Damaged Databases

If the DROP DATABASE command does not remove the database, you may need to use the sp_dbremove command or the DBCC DBREPAIR (database_name, dropdb) command.

This command removes a corrupt database:

DBCC DBREPAIR (database, dropdb)

Alternatively, you may use this command to remove a database:

sp_dbremove database [,dropdev]

The dropdev parameter removes from sysdevices all references to the devices of which the database being dropped had exclusive use. It does not delete the physical file. The preferred command to use is sp_dbremove.

This command removes the payroll database, even if it normally cannot be dropped:

sp_dbremove payroll

Deleting Databases Using Enterprise Manager

To delete a database using Enterprise Manager, follow these steps:

1. Expand the Database folder in SQL Enterprise Manager.

2. Right-click the database you want to drop.

3. Choose Delete.

Or,

1. Select the database to drop.

2. Press Delete.

Enlarging a Database Using Transact-SQL

You can enlarge a database by using the ALTER DATABASE command. This may be done while the database is in use.

The syntax for ALTER DATABASE follows:

ALTER DATABASE database_name
[ON device_name [=size], [..]]
[FOR LOAD]

Here, size indicates the additional amount of space to allocate--not the finished size of the database. The default increment is 1MB.

This command expands the accounting database on the accounting device by an additional 50MB:

ALTER DATABASE accounting
ON accounting3 = 50

The amount of new space allocated for data or the log depends on the original allocation. If the original allocation during the CREATE DATABASE statement was for data, the new allocation is for data. If the original allocation was for both data and a log, the new allocation is for data and the log. If the original allocation was for the log, the new allocation is for the log as well. If the new allocation is for space on a device that has no fragment of the database, the allocation is assumed to be for data.

If you attempt to enlarge a database by an amount of space that is not available, SQL Server allocates as much as it can, rounding to the nearest .5 MB. It then reports the results.

You can enlarge the size of a database up to 32 times.

Enlarging a Database Using Enterprise Manager

To enlarge a database using Enterprise Manager, follow these steps:

1. Expand the Database folder.

2. Right-click the database you need to expand.

3. Choose Edit.

4. Click Expand in the Edit database dialog.

5. Enter the new size of the database (see Figure 4.5).

Shrinking a Database Using Transact-SQL

You can use the DBCC SHRINKDB command to change the size of the database. It is a good idea to use SQL Enterprise Manager to shrink the database, however, because it automatically shows you the minimum size to which the database can be shrunk. You cannot decrease the size of the database below the space needed to store the current objects. You cannot decrease the size of the log. The database should be in single-user mode.

Figure 4.5. Expanding a database.

The syntax for the DBCC SHRINKDB command follows:

DBCC SHRINKDB (database_name [, new_size [ [ , `MASTEROVERRIDE' ] ] )

Here, the MASTEROVERRIDE part of the statement is used if you need to shrink the master database.

new_size is in 2KB pages and represents the final size of the database.

DBCC SHRINKDB (accounting , 4096)

If you do not specify a size, DBCC SHRINKDB returns the minimum size to which the data can be shrunk. DBCC SHRINKDB shrinks both the data and log portions. To change only the size of one or the other, use the DBCC SHRINKDB statement and then use ALTER DATABASE to increase the size of the data or log.


WARNING: DBCC SHRINKDB is fully logged and recoverable except when used on the master database. Because SQL Server is unrecoverable if the system crashes while shrinking the master database, you should back up the master database before attempting to shrink it.

Shrinking a Database Using SQL Enterprise Manager

To shrink a database using Enterprise Manager, follow these steps:

1. Expand the Database folder.

2. Right-click the database you need to shrink.

3. Choose Edit.

4. Click Shrink in the Edit Database dialog.

5. Answer Yes to the prompt The database must be set to single user mode.

6. The Shrink Database dialog shows the minimum size.

7. In the Database Size box, enter the size to shrink the database to (see Figure 4.6).

Figure 4.6. Shrinking the database.

Moving the Log to a Separate Device

If you created the database and placed it and the log on the same device as the database, you later may move the log to its own separate device. You may do so by using the ALTER DATABASE statement to create log space on a separate device. Using sp_logdevice ensures that all new log allocations of space are placed on the new device. The old log, however, still remains on the old device. To remove all logs from the original device, you must follow these instructions:

1. Expand the database (and thus the Transaction Log) to a new device.

2. sp_logdevice is used to move the Transaction Log to the new device and to move the data back to the original device.

3. Complete enough transactions to fill the extent (eight pages) that the log currently is using. (Run DBCC CHECKTABLE on the syslogs table to determine when a new page is used.) When the total number of pages has increased by 8, new pages are ensured of being on the new device.

4. Ensure that all active transactions on the old database device are complete.

5. Back up the Transaction Log. This removes all old pages from the old log as well as from the new log.

6. Run sp_helplog to verify that the entire log is on the new log device.


TIP: Summary of Sizes for Creating Devices and Databases
To create a device using DISK INIT, specify the size in 2KB pages.
To expand a device using DISK RESIZE, specify the size in 2KB pages.
To create a database using CREATE DATABASE, specify the size in MB.
To shrink a database using DBCC SHRINKDB, specify the size in 2KB pages.
To expand a database using ALTER DATABASE, specify the size in MB.

Documenting the Database-Creation Sequence

Document the sequence in which you CREATE ALTER, RESIZE, or MOVE a database. You will need this information if you need to rebuild a database, load from backup, or create multiple servers with the same configuration.

Keep the following information:

If Transact-SQL was used to create the devices and databases, keep the scripts that were used to create them (DISK INIT, CREATE DATABASE, ALTER DATABASE). Keep these scripts on disk, and keep paper copies. This serves as excellent documentation, and the scripts can be run again to re-create devices and databases.

As you have seen, you can use Enterprise Manager to create and tinker with your databases. Then before going into production, you can use Enterprise Manager to generate Transact-SQL scripts for all objects. Keep those scripts as emergency backups for re-creating your servers. Even during the development process, development and test teams may want to occasionally drop and re-create fresh versions of their databases using these scripts.

Estimating Storage Requirements

To estimate the storage requirements of your database, you need to understand the storage structures of SQL Server. The first place to start is in examining the basic elements of storage and then estimating the storage needed for your particular database. For more detailed information about how space is used in a database, refer to SQL Server 6.5 Unleashed.

The three main parts of a database that use space follow:

The sp_spaceused Stored Procedure

The sp_spaceused stored procedure displays space used by object_name. This procedure is an important tool that you can use to determine how much space is left on your database or on the Database Log if the log is on a separate device. If the database runs out of space, no more data can be added. A good rule of thumb is 25 percent free space in your database at all times. When sp_spaceused is issued from within a user database, it requires no parameters.

The syntax for the sp_spaceused command follows:

sp_spaceused [object_name] [[,] @updateusage = {true | false} ]

Here, @updateusage = true issues a DBCC UPDATEUSAGE command before displaying usage. The DBCC UPDATEUSAGE command reports and then corrects inconsistencies in sysindexes that may result in incorrect information being displayed by sp_spaceused. You should be in single-user mode to run this command. If there are no inconsistencies DBCC UPDATEUSAGE displays no data; if there are inconsistencies, it displays information on what it has found and corrected.

sp_spaceused

database_name        database_size
-------------        -------------
master            8.00 MB
(o rows affected)
reserved        data         index_size        unused
--------        ----         ----------        ------
13860  KB       12260 KB     436 KB            1156 KB

database_size is the total size, including data and log allocations. reserved is based on data use. Log information is not included.


NOTE: sp_spaceused can be off by a couple of extents (an extent is 16KB).

Allocation Units in SQL Server

SQL Server operates on a building block of a 2KB page. That is why most units in SQL are given in 2KB units. When SQL Server allocates pages for objects, it allocates eight pages at a time in order to lower the overhead associated with allocating and tracking individual pages. A set of eight pages is called an extent. An extent is 16KB (8 * 2KB).

SQL Server tracks extents in groups of 32; each group is called an allocation unit. An allocation unit is 512KB (32 * 16KB). Databases are based on allocation units, whereas objects inside a database are based on extents. Objects cannot share extents; any space left in an extent is wasted unless the object becomes larger.

Therefore, even a one-line stored procedure takes an extent in a database, which equates to 16KB. Thirty-two such objects take at least 512KB in a database, even if the objects are very small.

Data Pages

A table is an object that is based on extents (16KB) but has individual 2KB pages. A 2KB page can hold 2,048 bytes of data (1024 * 2). Of the 2,048 bytes available, 32 bytes are used for overhead in linking pages and another approximately 54 bytes are used for Transaction Log recording; this leaves approximately 1,962 bytes (2,048-32-54 = 1962) to store information. Rows of data can never cross pages, so the largest row of data SQL Server 6.5 can hold is approximately 1,962 bytes or characters.

If a single row contains 196 bytes of data, then a total of 10 rows can be held per data page (1962/196) = 10.x. If a single row contains 1,000 bytes of data, then only one row of data can be held per data page (1962/1000) = 1.x. Remember that because rows cannot cross data pages, any extra room on a data page (after fitting all the complete rows) is wasted. In the previous example, if the data could be shortened to 981 bytes, two rows would fit on each data page, cutting the space required for this particular table in half.

A data row size is calculated by adding the size of its columns plus any row overhead. More details on calculating row sizes is provided on Day 5, "Creating Tables." The basics to get you started follow:


NOTE: Text and image datatypes are exceptions to the rule that a row can only be 1,962 bytes or less. These two datatypes allow data sizes up to 2GB!

Summary

You started today's lesson with a look at the two types of devices: backup devices and database devices. Backup devices are used to back up databases and are covered in Day 8, "Backing up and Restoring." Database devices are used to contain databases and consist mainly of a name, a path to a file, and an internal number that SQL Server uses to track the device. Database devices can be expanded but not shrunk.

You then learned how to create databases, with an emphasis on putting the Transaction Log on a separate device for both performance and recoverability reasons. You then learned how to expand and shrink databases.

Database options then were discussed, and you learned how to set options using both Transact-SQL and Enterprise Manager.

You ended the day by learning how to estimate the space needed by tables, data, and objects; you also got an introduction to indexes. Indexes are covered in depth on Day 13, "Indexing."

Q&A

Q Why are they called devices when they are really just files?

A
SQL Server can trace its roots to the UNIX and mainframe environments. In those environments, a device often refers not only to hardware but files or logical definitions.

Q Are my normal Windows NT backups good enough for my databases?


A
Because database devices always are considered "open" whenever SQL Server is running and normal backup software does not back up open files, database devices, and thus your databases, probably are NOT getting backed up during your normal Windows NT backups. This extremely important subject is discussed more in Day 8, "Backing up and Restoring."

Q What is the best way to size my devices and databases?


A
I prefer a single database device for data and another database device for the log for any given database. I prefer to not have to increase the size of my devices or databases very often, so I like to give myself enough room to last at least six months, if not a year.

Q When should I use the default label on my devices?


A
The only time devices tagged as default come into play is when databases are created without specifying the device. Normally, you want to be very specific about which databases are on which devices--not only so that you have control over your resources, but also so that your server can be re-created exactly as it was if you suffer catastrophic failure. In other words, you probably should not use default devices under normal conditions.

Q Which is better--using Enterprise Manager or doing everything using ISQL/w?


A
Both methods work, but I prefer Enterprise Manager for almost all the functions it can do. That said, I also find myself opening ISQL/w sessions frequently to do things that Enterprise Manager can't.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you've learned. Try to understand the quiz and exercise answer before continuing on to the next day's lesson. The answers are provided in Appendix B, "Answers."

Quiz

1. List the tables that record information about databases and devices.

1A. sysdevices, sysdatabases, sysusages


2. How is each table used?

2A. sysdevices holds information about devices, sizes, and file locations. sysdatabases holds information about the user databases, and sysusages is the table that holds information on which databases are held on which devices.


3. Write Transact-SQL statements to create the following devices:

One to hold an accounting database. It must be large enough to hold 30MB of data.

One to hold an employee information database. It must be large enough to hold 10MB of data.

3A.

DISK INIT
NAME = `Accounting',
PHYSNAME = `c:\MSSQL\data\accounting.dat',
VDEVNO = 6,
SIZE = 15000
3B.
DISK INIT
NAME = `Employees',
PHYSNAME = `c:\MSSQL\data\employee.dat',
VDEVNO = 6,
SIZE = 5000
4. Write SQL statements that will create the following databases and their logs:

A database called Accounting that will take 30MB on a device called Accounting_data, with a Transaction Log that is 10MB on a device called Accounting_log.

4A.

CREATE DATABASE accounting
ON accounting_data = 30
log on accounting_log=10
5. Can you create a database across three devices by using SQL Enterprise Manager? If so, how?

5A. Yes, but you initially can create only the database on a single device; then you can use Enterprise Manager to expand it to multiple devices.

Exercises

Use the statements you created to implement the accounting and employee databases.


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.