Core Database Administration

Updated : July 19, 2001

Core database administration tasks involve creating, manipulating, and supporting databases. In Microsoft SQL Server 2000, a database is a collection of data and the objects that represent and interact with that data. Tables, views, stored procedures, triggers, and constraints are typical database objects.

A single database server instance can have up to 32,767 databases, and each database can have over 2 billion objects. These are theoretical limits, of course, but they demonstrate that SQL Server can handle just about any chore. To perform most administration tasks, you'll need to log in to the database using an account that has the Sysadmin role, such as the local sysadmin account (sa). Detailed information on roles and SQL Server security is found in Chapter 5.

On This Page

Database Files and Logs
Database Administration Basics
Creating Databases
Setting Database Options
Managing Database and Log Size
Manipulating Databases
Altering a Database
Tips and Techniques

Database Files and Logs

Each SQL Server database has a transaction log associated with it. A transaction log is a history of modifications to the database, and SQL Server uses it to ensure database integrity. All changes to the database are first written to the transaction log and then applied to the database. If the database update is successful, the transaction is completed and recorded as successful. If the database update fails, SQL Server uses the transaction log to restore the database to its original state (which is called rolling back the transaction). This two-phase commit process makes it possible for SQL Server to automatically restore a database in case of power failure, server outage, or other problems that occur when you enter a transaction.

SQL Server databases and transaction logs are contained in separate database files. This means that each database always has at least two files associated with it— a data file and a log file. Databases also can have secondary data files. SQL Server uses three types of database files:

• Primary data files Every database has one primary data file. These files store data and maintain records of other files used in a database. By default, these files end with the .mdf extension.

• Secondary data files These files store additional data for a database. By default, these files end with the .ndf extension.

• Transaction log files Every database has at least one transaction log file. This file contains information necessary to restore the database. By default, log files end with the .ldf extension.

Note: SQL Server also uses backup devices. Backup devices can be physical devices, such as tape drives, or files that are stored on a local drive or a network share. SQL Server data and log files can be stored on either FAT or NTFS partitions but can't be stored on any compressed file system.

Database files are set when you create or modify the database. By allowing for multiple database files, SQL Server can create databases that span multiple disk drives and that can grow in size as needed. Although the size of a SQL Server database is often measured in GBs, with all editions of SQL Server except the Personal Edition, databases can range in size from 1 MB to a theoretical limit of 1,048,516 TBs. With the Personal Edition, databases have a maximum size limit of 2 GB.

As you set out to work with databases, keep in mind that SQL Server is designed to expand databases automatically as necessary. This means that master, tempdb, msdb, and other critical databases won't run out of space under normal conditions—provided, of course, that there's file space on the configured drives and that you don't set a maximum database size manually. System databases are the most important ones on the server. You should never directly update tables in system databases. Instead, you should use the appropriate management tools or stored procedures to modify the databases if you need to. The only exception is the model database, which you can update with settings for new databases.

Database Administration Basics

You do most of your database administration work through Enterprise Manager. You'll use Enterprise Manager to perform many common database administration tasks, including

• Viewing database information

• Checking user and system databases

• Examining database objects

The sections that follow examine each of these tasks.

Viewing Database Information in Enterprise Manager

SQL Server organizes information using a top-down hierarchy that goes from server groups to servers to databases to objects. Accordingly, you must work your way down to the database level in order to view the databases installed on a particular server instance. If you have registered a server instance and have connected to it previously, you can view its databases by completing the following steps:

1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server group you want to work with. If the SQL Server service is stopped, you'll need to restart it before accessing the server.

2. Click the plus sign (+) next to the server you want to work with and then select the Databases folder.

   Note: If you haven't authenticated the server connection, you may need to provide a SQL logon account and password. You may also need to reestablish a connection with the server. In either case, enter any necessary information and then click OK/Yes to continue.

3. You should see a list of the databases available on the server. Now select the database you want to work with in the left pane.

   With the Taskpad enabled (by choosing Taskpad from the View menu, if necessary), the right pane should provide access to three different views:

   • General Displays database, maintenance, total size, and other important database information; also provides quick access links to start key administration tasks, such as backup database and restore database. Move the mouse pointer over a yellow category button to display a shortcut menu.

   • Table Info Displays the available user tables and indexes in the databases. The user tables are listed alphabetically along with their associated indexes and clustered indexes. You'll find the total table size and the number of rows in a particular table as well.

   • Wizards Provides quick access to the most commonly used database administration wizards. The wizards are organized into task-related categories, such as those used to manage SQL Server and those used to set up replication. Click a wizard title to start the wizard.

4. To view database information, click any of the view links in the right pane, either General, Table Info, or Wizards. Figure 4-1 shows the General view.

   

   Figure 4-1: The General view provides a summary of the selected database and also gives quick access to start key administration tasks. Move the mouse pointer over a category button to display a shortcut menu.

Viewing Database Information Using SQL

You can also use Transact-SQL to examine database information. Transact-SQL is an enhanced version of the standard structured query language that SQL Server uses. Start Query Analyzer and then use the following command:

sp_helpdb <dbname>

where dbname is the name of the database you want to examine.

When you view database information in this way, you get an overview of the database as well as a listing of current data and log files. Table 4-1 gives a summary of this information.

Table 4-1 Database Properties Viewable Using T-SQL

Column Name	Description
compatibility_level	The current compatibility level of the database. 80 indicates SQL Server 2000 compatibility.
created	The date the database was created.
db_size	The total size of the database including all data and log files.
dbid	The unique identifier for the database on the current server.
filegroup	The filegroup associated with the database file. Filegroups allow you to group sets of database files together.
fileid	The unique identifier for the file in the current database.
filename	The full filename and path.
growth	The number of megabytes or percent the file grows by.
maxsize	The maximum file size. Unlimited means there is no limit.
name	The name of the database or file (without a file extension).
owner	The database owner.
size	The current size of a file.
status	The database status.
usage	The way the file is used, such as data only or log only.

Checking System and Sample Databases

A new SQL Server installation includes the system and sample databases listed in Table 4-2. System databases are critical to the proper operation of SQL Server, and backing up and maintaining these databases is a key part of administration. Sample databases, on the other hand, are meant only to provide examples and don't need regular maintenance. The sample databases take up only 6 MB of disk space, and rather than deleting them, you may want to keep them around for testing and for use in demonstrations.

Table 4-2 Summary of System and Sample Databases

Database Name	Database Type	Description
master	System	Maintains information on all databases installed on the server. This database is modified anytime you create databases, manage accounts, or change configuration settings. Back up the master regularly.
model	System	Provides a template for all new databases. If you want new databases to have certain properties or permissions, put these changes in the model database and then all new databases will inherit the changes.
tempdb	System	Provides a temporary workspace for processing queries and handling other tasks. This database is recreated each time SQL Server is started and is based on the model database.
pubs	Sample	Provides a sample database and is often used to demonstrate SQL/Transact-SQL commands. Northwind Sample Provides a sample database with application programming interface (API) examples.
msdb	System	Used by the SQL Server Agent service when performing handling alerts, notifications, and scheduled tasks. You can access all the information in this database using Enterprise Manager options.

Examining Database Objects

The key elements of a SQL Server database are referred to as objects. The objects you can associate with a database are:

• Constraints

• Defaults

• Indexes

• Keys

• Stored procedures

• Extended stored procedures

• Tables

• Triggers

• User-defined data types

• User-defined functions

• Views

You can also associate users, roles, rules, and full-text catalogs with databases.

To examine objects within a database, complete the following steps:

1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server group you want to work with.

2. Click the plus sign (+) next to the server you want to work with again and then, if necessary, authenticate yourself or establish a connection, or both.

3. Work your way down to the database level. Expand the Databases folder and then expand the entry for the database you want to work with.

4. You should see a list of available database objects. In the left pane, click the element you want to view.

More Info Each of these objects is covered in detail in the appropriate chapter. For example, you'll find more information on tables in Chapter 6.

Creating Databases

SQL Server uses the model database as the basis of new databases. If you want new databases to have the same setup, you should modify the model database and then create the necessary new databases. Otherwise, you'll need to man- ually modify the settings of each new database. The easiest way to create a new database is to use Enterprise Manager. You can also create databases using Transact-SQL.

Creating Databases in Enterprise Manager

In Enterprise Manager you set database properties with buttons and input fields and let SQL Server do all the behind-the-scenes SQL work. You create a database using the default options by completing these steps:

1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server group you want to work with.

2. Click the plus sign (+) next to the server you want to work with again and then, if necessary, authenticate yourself or establish a connection, or both.

3. Right-click the Databases folder and then from the shortcut menu, choose New Database. This opens the Database Properties dialog box shown in Figure 4-2.

4. Click the General tab and type a name for the database. Although database names can have up to 128 characters, you'll be better able to track the database if it has a short but descriptive name.

   

   Figure 4-2: Use the Database Properties dialog box to create new databases and to update the settings of existing databases. If you want to create databases with a wizard instead, choose the Wizards option on the Tools menu. In the Wizards dialog box expand Database and then double-click Create Database Wizard.

   Note: The names of database objects are referred to as identifiers. Identifiers can contain from 1 to 128 characters (except for local temporary tables, which can have from 1 to 116 characters) and must follow the specific naming conventions for the identifier class to which they belong. Generally, if the identifier name uses spaces or begins with a number, you must use brackets ([]) or double quotes (" ") to delimit the name when referencing it in Transact-SQL commands.

5. Click OK. SQL Server creates the database.

To customize the creation process, follow steps 1 through 4 (but not 5) in the previous example and then continue with these steps:

1. On the General tab, use the Collation Name selection menu to choose a collation for the database. Microsoft Windows collation names have two components: a collation designator and a comparison style. The collation designator specifies the alphabet or language whose sorting rules are applied with dictionary sorting and the code page to use when storing non-Unicode character data. The comparison style specifies additional collation style as identified by the following abbreviations:

   • CI Case insensitive

   • CS Case sensitive

   • AI Accent insensitive

   • AS Accent sensitive

   • KS Kanatype sensitive

   • WS Width sensitive

   • BIN Binary sort order

2. On the Data Files tab, type a name in the File Name field for the primary data file associated with this database. By default, SQL Server bases the data filename on the database name. For example, if you type Sample as the database name, the data file is named Sample_Data.

3. In the Location field, type the full path to the data file. The primary data file should end with the .mdf file extension. By default, SQL Server uses the default data location you selected when you installed the server. Click Location to find a new path or enter a new path directly.

4. In the Initial Size field, type an initial size for the database in megabytes. Use a size that makes sense for the amount of data that the database will store. By default, new databases have the same size as the model database. The size range for databases is from 1 MB to many TB.

   Tip Setting the initial database size to a reasonable value cuts down on the overhead that may be associated with growing the database. Whether you grow the database manually or SQL Server grows it automatically, the database is locked until the growth is complete. This can cause delays in processing queries and handling transactions.

   Note: Keep in mind that you can't shrink a database to be smaller than it was when you created it. However, you can shrink individual data and log files to be smaller than their original size by using the DBCC SHRINKFILE statement. With DBCC SHRINKFILE, you must shrink each file individually, rather than trying to shrink the entire database.

5. The File Group field shows which filegroup the data file belongs to. By default, all files are placed in the primary group. While the primary data file must be in the primary group, other data files can be placed in different filegroups. Filegroups provide additional options for determining where data is stored and how it's used, as well as how data is backed up and restored.

   Tip Filegroups are designed primarily for large databases and advanced administration. If your database might grow to 1 GB or larger, consider using filegroups. Otherwise, you really don't need to use filegroups. That said, the primary reason to use filegroups is to improve database response time. You do this by allowing database files to be created across multiple disks or to be accessed by multiple disk controllers, or both.

6. Secondary data files provide an additional location for data. If you want to configure secondary data files, start on a new line and then repeat steps 1 through 4. Secondary data files should end with the .ndf file extension.

7. After you configure data files, you need to configure file properties for the database. By default, data files are set to grow automatically by 10 percent each time the data files need to be expanded, and there's no limit on the maximum file size.

8. Click the Transaction Log tab shown in Figure 4-3. You can now configure one or more transaction log files in much the same way that you configured the data files. Type the file name, location, initial size, and filegroup information. Be sure to name the log files with the .ldf file extension.

   

   Figure 4-3: Use the Transaction Log tab to configure the size, location, and features of the transaction log.

   Real World The Autogrow feature is a good one, and I heartily recommend using it so databases don't run out of space. That said, however, the default option for file growth can lead to problems. With a 10 percent growth rate, a 500 MB database grows by a whopping 50 MB each time a data file needs to be expanded, and a server with multiple databases may run out of space as a result of the growth factor. I prefer to set the growth in megabytes, with 1 MB as a minimum growth size. When you grow in megabytes, you know exactly how much the database will grow each time. You may also want to configure an alert to notify you when the database grows to a certain size. Configuring alerts is covered in Chapter 12.

   Note: Sizing the transaction log can be tricky. You don't want to rob the system of needed space for data, but at the same time you want to make sure that the transaction logs aren't continually getting resized (as I noted previously, the file gets locked when it's being expanded). I recommend 2–3 MB as a minimum for most databases and 25 percent of total data file size on a moderately active database. Note also that placing transaction logs on separate drives from data files can usually improve database performance.

9. Set file properties for the transaction logs. Here, the default options usually work fine and you don't need to change anything.

10. Click OK to complete the creation process. Afterward, you should set options and permissions for the database. Setting options is covered in the section of this chapter entitled "Setting Database Options." Setting permissions is covered in Chapter 5, "Microsoft SQL Server 2000 Security."

Creating Databases Using T-SQL

Another way to create a database is to use the CREATE DATABASE command. This command has options that are similar to those in the Database Properties tab, and the best way to learn how the command works is to first create databases in Enterprise Manager and then try CREATE DATABASE.

The syntax and usage for CREATE DATABASE is shown in Sample 4-1.

Sample 4-1 CREATE DATABASE Syntax and Usage

Syntax

CREATE DATABASE database_name
   [ ON [PRIMARY]
   [ <filespec> [,..n] ]
   [, <filegroup> [,..n] ]
   ]
   [ LOG ON { <filespec> [,..n]} ]
   [ COLLATE collation_name ]
   [ FOR LOAD | FOR ATTACH ]
   <filespec> ::=
   ( [ NAME = logical_file_name, ]
   FILENAME = 'os_file_name'
   [, SIZE = size]
   [, MAXSIZE = { max_size | UNLIMITED } ]
   [, FILEGROWTH = growth_increment] ) [,..n]
   <filegroup> ::=
   FILEGROUP filegroup_name <filespec> [,..n]

Usage

USE MASTER
GO
CREATE DATABASE Sample
ON
PRIMARY ( NAME = Sample1,
FILENAME = 'c:\Microsoft SQL    Server\mssql$engone\data\sampledat1.mdf',
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%),
( NAME = Sample2,
FILENAME = 'c:\Microsoft SQL    Server\mssql$engone\data\sampledat2.ndf',
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%)
LOG ON
( NAME = SampleLog1,
FILENAME = 'c:\Microsoft SQL    Server\mssql$engone\data\samplelog1.ldf',
SIZE = 3MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5MB)
GO

Setting Database Options

New databases inherit options from the model database. After you create a database, you can modify these settings at any time by using Enterprise Manager or Transact-SQL.

Setting Database Options in Enterprise Manager

To set database options in Enterprise Manager, complete the following steps:

1. Start Enterprise Manager and then in the left pane (Console Root) click the plus sign (+) next to the server group you want to work with.

2. Click the plus sign (+) next to the server you want to work with again and then click the plus sign (+) next to the Databases folder.

3. Right-click the database you want to work with and then from the shortcut menu, choose Properties. This opens the Properties dialog box shown in Figure 4-4. For details see the following section, "Database Options for Enterprise Manager and Transact-SQL."

4. In the Properties dialog box, click the Options tab. You can now configure options for the database by selecting or clearing the appropriate check boxes.

5. Click OK when you're finished. Your changes take effect immediately, and you don't need to restart the server.

   

   Figure 4-4: After you create a database, you should set its options in the Properties dialog box.

Database Options for Enterprise Manager and Transact-SQL

You use database options to configure individual databases. In most SQL Server editions, all database options are set to FALSE by default, except for Auto Create Statistics, Auto Update Statistics, and Torn Page Detection, which are set to TRUE by default in the model database. If you change these options in the model database, your default options will be different. In the following list, database Properties dialog box options are listed with initial caps, or by the related SQL command in lowercase, or both.

• ANSI NULL Default (ANSI null default) When TRUE, changes the database default to NULL when no value is specified. You can override this setting by explicitly stating NULL or NOT NULL when you create user-defined data types or column definitions.

• ANSI nulls When TRUE, any comparison to a null value evaluates to NULL. Otherwise, comparisons of non-Unicode values evaluate to TRUE only when both values are NULL.

• ANSI warnings When TRUE, SQL Server displays warnings when it otherwise may not. For example, if TRUE, divide-by-zero errors are displayed. Otherwise they aren't.

• arithabort When TRUE, terminates a query when an overflow or divide-by-zero error occurs. If the error occurs in a transaction, the transaction is rolled back. When FALSE, a warning message can be displayed, but queries and transactions continue as if no error occurred.

• Auto Close (autoclose) When TRUE, the database is closed and resources are freed up when the last user connection ends and all database processes are completed. The database reopens automatically when a user tries to use the database again. In the SQL Server Desktop Edition, this option is set to TRUE by default. All other editions set this option to FALSE by default. When FALSE, the database remains open even if no users are currently using it.

• numeric roundabort When TRUE, an error is generated when a loss of precision occurs in an expression. When FALSE, losses of precision do not generate error messages, and the result is rounded to the precision of the column or variable storing the result.

  Tip With the Desktop Edition, Autoclose is a useful feature that allows databases to be treated like any other file. When the database is closed, you're free to move, copy, or change it.

• Auto Create Statistics (auto create statistics) When TRUE, statistics are automatically created by SQL Server for columns used in a where clause. These statistics are used to better determine how to evaluate a query, which in turn can improve query performance.

• Auto Shrink (autoshrink) When TRUE, data and log files can be reduced in size and compacted automatically. The idea is that when records are deleted or purged, you can let SQL Server automatically reduce the size of data or log files, or both. However, log files are reduced in size only when you back up the transaction log or set the Truncate Log On Checkpoint option to TRUE. In the SQL Server Desktop Edition, this option is set to TRUE by default. All other editions set this option to FALSE by default. Note also that you can't set this option on a read-only database.

  Note: Several caveats apply to Autoshrink. The Autoshrink option is applied only when more than 25 percent of a file contains unused space. This in turn causes SQL Server to reduce the file size so that only 25 percent of file space is free or to set the file size to its original size setting, whichever is greater. The process that shrinks the database is the server process ID 6; it checks the database size at 30-minute intervals. As with Autogrow, the database is locked when SQL Server shrinks files, which can reduce query response time. Because of this, I don't recommend using this option and prefer to periodically run the DBCC SHRINK-DATABASE command or to schedule this task on a recurring basis, as explained in the section of this chapter entitled "Compacting and Shrinking a Database Manually."

• Auto Update Statistics (auto update statistics) When TRUE, existing statistics are automatically updated if data in the related tables changes. Otherwise, existing statistics aren't automatically updated and you can only update them manually.

• concat null yields null When TRUE, concatenating a string containing NULL with other strings results in NULL. Otherwise, the null value is treated as an empty string.

• cursor close on commit When TRUE, open cursors are closed automatically when a transaction is committed. This behavior is in compliance with SQL-92 but isn't set to TRUE by default. As a result, cursors remain open across transaction boundaries and close only when the related connection is closed or when the cursor is explicitly closed.

  Note: SQL-92 is the most widely used version of the SQL standard and is sometimes referred to as ANSI SQL.

• dbo use only When TRUE, only the database owner can access the database. Use this option when you're modifying a database and temporarily want to block access to it. To set this option using the Properties dialog box, select Restrict Access and then choose Members of db_owner, dbcreator, or sysadmin.

• default to local cursor When TRUE, cursors are created with local scope unless otherwise specified, and as a result, the cursor name is valid only within this scope. When FALSE, cursors not explicitly set to LOCAL are created with a global scope and can be referenced in any stored procedure, batch, or trigger that the connection executes.

• merge publish When TRUE, you can use the database for merge replication publications. If you use the Replication Wizard to configure replication, this setting is changed for you automatically.

• offline When TRUE, the database is offline and you can mount or dismount it as necessary. You'll often want to use this option with removable media, such as CDs.

• published When TRUE, permits the tables of a database to be published for replication. The Replication Wizard also sets this option.

• Read-only (read only) When TRUE, you can read data but not modify it. Use this option to prevent users from changing data and modifying database configuration settings. When this option is set, several caveats apply: automatic recovery is skipped at system startup, locking doesn't take place, and the database won't shrink.

• Recursive Triggers (recursive triggers) When TRUE, a trigger can execute recursively. Triggers can be executed directly or indirectly. With a direct trigger, a trigger in Table A1 modifies Table A1, which in turn causes the trigger to fire again. With an indirect trigger, a trigger in Table A1 could modify data in Table A2, which in turn has a trigger that modifies data in Table A1, and this causes the original trigger to fire again.

• select into/bulkcopy When TRUE, certain SQL commands aren't logged in the transaction log. These commands include using SELECT INTO with a permanent table, running fast bulk copy, using UPDATETEXT or WRITETEXT without logging, and using a table load. If you set this option to TRUE and execute any command that bypasses the transaction log, you can't recover the database from transaction logs and BACKUP LOG commands are prohibited. Instead, use BACKUP DATABASE to back up the entire database and then later you can back up from the log (provided you don't run any more commands that bypass the transaction log).

• Single User (single user) When TRUE, only one user at a time can connect to the database. Before you can set this option, all active connections to the database must be closed. If necessary, kill the user processes as explained in Chapter 3.

• subscribed When TRUE, the database can be subscribed to a replicated (or published) database.

• torn page detection When TRUE, SQL Server automatically detects incomplete I/O operations known as torn pages. If SQL Server detects a torn page during a user connection, it raises an I/O error and terminates the user connection. If it detects a torn page during recovery, it marks the database as suspect. In either case, you may want to restore the database from backup and apply any backup transaction logs.

  Tip You can use battery-backed disk caches to ensure that data is successfully written to disk or not written at all. But don't set torn page detection to TRUE.

• trunc. log on chkpt. When TRUE, the transaction log can be automatically truncated. Basically, this allows the log to be cleared out once transactions have been committed. After the transaction log has been cleared out, you can perform BACKUP/RESTORE only at the database level (and not with the transaction log). In the SQL Server Desktop Edition, this option is set to TRUE by default. All other editions set this option to FALSE by default.

  Tip Checkpoints occur at various times. A checkpoint is issued for each database when the SQL Server service shuts down normally. Checkpoints don't occur when the shutdown with nowait statement is used. A checkpoint is executed in a single database when a database is changed with sp_dboption. SQL Server also automatically issues a checkpoint on a database as necessary to ensure that the designated recovery interval can be achieved and when the log becomes 70 percent full.

  Note: The transaction log must be large enough to store all active transactions. Otherwise, you can't roll back transactions. In a deployment environment, you should use this option only when you can rely solely on database backups and don't supplement with transaction log backups. Note also that the tempdb database is always truncated on CHECKPOINT, regardless of the setting of this option.

• Use Quoted Identifiers (quoted identifier) When TRUE, identifiers must be delimited by double quotation marks ("…") and literals must be delimited by single quotation marks ('…'). All strings that are delimited by double quotation marks are interpreted as object identifiers and don't have to follow the Transact-SQL rules for identifiers.

Viewing, Changing, and Overriding Database Options

Although Enterprise Manager makes it easy to set database options, you'll often want to view or change options using SQL commands. To do this, you can use the sp_dboption stored procedure, individual SET commands, or the ALTER DATABASE command. Key tasks you'll want to perform with the sp_dboption and SET commands are

• Displaying an options list To display a list of available options, type EXEC sp_dboption.

• Viewing database option settings To view the current option settings for a database, type EXEC sp_dboption <dbname> where dbname is the name of the database you want to examine, such as EXEC sp_dboption 'Subs'.

• Changing database options To set or change a database option, type EXEC sp_dboption <dbname>, <option>, {TRUE | FALSE} where dbname is the name of the database you want to examine and option is the name of the option to set, such as EXEC sp_dboption 'Subs', 'trunc. log on chkpt.', TRUE.

• Overriding database options Use SET options for individual sessions or database drivers to override default settings. You can also check options using properties of the Databaseproperty function. See the "Default Connection Options" section of Chapter 2 for more information.

Using ALTER DATABASE is covered in the section of this chapter entitled "Altering a Database."

Note: The sp_dboption stored procedure should not be used to modify the master or tempdb databases. It is only supported for backward compatibility and primarily should be used to display database options. Whenever possible, use ALTER DATABASE to modify database options instead.

Managing Database and Log Size

With SQL Server 2000, you can manage database and log size either automatically or manually. You can configure either technique in Enterprise Manager or through Transact-SQL. This section looks primarily at configuration through Enterprise Manager.

Configuring SQL Server to Automatically Manage File Size

To configure automatic management of database and log size in Enterprise Manager, complete the following steps:

1. Start Enterprise Manager and then, using the entries in the left pane, work your way down to the Databases folder.

2. Right-click the database you want to work with, and then from the shortcut menu, choose Properties.

3. Click the Data Files tab. Under File Properties, select the Automatically Grow File check box, and then set the file growth in megabytes or as a percentage of file size.

4. Click the Transaction Log tab. Under File Properties, select the Automatically Grow File check box, and then set the file growth in megabytes or as a percentage of file size.

5. Optionally, click the Options tab and select the Auto Shrink check box. Autoshrink compacts and shrinks the database periodically.

6. Click OK when you're finished. Your changes take effect immediately; you don't need to restart the server.

Note: See the section of this chapter entitled "Creating Databases in Enterprise Manager" for tips and advice on sizing databases and transaction logs.

Expanding Databases and Logs Manually

Sometimes you'll want to increase the size of a database or log file. You can do this by completing the following steps:

1. Start Enterprise Manager and then, using the entries in the left pane, work your way down to the Databases folder.

2. Right-click the database you want to work with and then from the shortcut menu, choose Properties.

3. To expand a database, click the Data Files tab and then enter a larger file size in the Space Allocated field for a primary or secondary data file.

   Tip You could also create and size a new secondary file for the database. The advantage to using a new file rather than an existing file is that SQL Server doesn't need to lock what may be an active database file in order to expand the database.

4. To expand a log, click the Transaction Log tab and then enter a larger file size in the Space Allocated field. (You could also create and size a new transaction log file.)

   Tip With data and log files, the new file size must be larger than the current size. If it isn't, you'll get an error. The reason for this is that shrinking the database is handled in a different way. See the following section, "Compacting and Shrinking a Database Manually," for details.

5. Click OK to make the changes. SQL Server locks the database while expanding it, which blocks access.

More Info You can also expand files using Transact-SQL. The command you use is ALTER DATABASE. For more information, see the section of this chapter entitled "Altering a Database."

Compacting and Shrinking a Database Manually

Compacting and shrinking a database is a bit different from expanding it, and you'll often want finer control over the process than you get with the Autoshrink option. Fortunately, you can manually manage this process, and you can also schedule this process on a recurring basis.

To manually compact or shrink database files (both data and log files) in Enterprise Manager, complete the following steps:

1. Start Enterprise Manager and then, using the entries in the left pane, work your way down to the Databases folder.

2. Right-click the database you want to work with and from the shortcut menu, choose All Tasks. From the All Tasks menu, choose the Shrink Database option.

3. You should see the Shrink Database dialog box shown in Figure 4-5. The Database Size area shows the total amount of space allocated to all database files and the amount of free space. Use this information to help you decide whether you really want to shrink the database.

4. Use Maximum Free Space In Files After Shrinking to set the percentage of free space in the database. To squeeze all extra space out of the database, use a value of 0 percent, but be aware that the next write operation may cause the database to grow automatically.

   

   Figure 4-5: Shrinking a database is easy with Enterprise Manager's Shrink Database dialog box. Just make your selections and click OK, or schedule the task on a recurring basis.

5. To reorganize data pages and move them to the beginning of the data files, select Move Pages To Beginning Of File Before Shrinking. This compacts the data pages but doesn't remove empty data pages.

   Note: Log files aren't reduced in size immediately. Instead, the size is reduced when the transaction log is backed up or the log is truncated, whichever occurs first. Also, you normally can't shrink a database smaller than the model database (which is the database template).

6. Click OK to begin or continue on to step 7 for scheduling. SQL Server locks the database while shrinking it, which blocks access.

7. The property settings you make in this dialog box are saved and unique to the current database. If you want to use these properties to shrink the database on a recurring basis, select Shrink The Database Based On This Schedule and then click Change. You can now schedule this task as explained in Chapter 12, "Database Automation and Maintenance."

To manually compact or shrink individual database files in Enterprise Manager, complete the following steps:

1. Start Enterprise Manager and then, using the entries in the left pane, work your way down to the Databases folder.

2. Right-click the database you want to work with and from the shortcut menu, choose All Tasks. From the All Tasks menu, choose the Shrink Database option.

3. You should see the Shrink Database dialog box shown previously in Figure 4-5. The Database Size area shows the total amount of space allocated to all database files and the amount of free space. Use this information to help you decide whether you really want to shrink the database.

4. Click Files. This displays the Shrink Database Files dialog box.

5. Use the Database File selection menu to choose the data or log file that you want to shrink.

   Choose a shrink action:

   • Compress pages and then truncate free space from the file

   • Truncate free space from the end of the file

   • Empty the file (data will migrate to other files in the filegroup)

   • Shrink file to __ MB

6. If you want to use these properties to shrink the data or log file at a later date and time, select Shrink The File Later and then select a date and time.

7. Click OK.

Another way to shrink a database is to use Transact-SQL. Two commands are provided:

DBCC SHRINKDATABASE
( database_name [, target_percent]
[, {NOTRUNCATE | TRUNCATEONLY}]
)
DBCC SHRINKFILE
( {file_name | file_id }
{ [, target_size]
| [, {EMPTYFILE | NOTRUNCATE | TRUNCATEONLY}]
   }
)

You use DBCC SHRINKDATABASE to shrink all data files in the database and DBCC SHRINKFILE to shrink a specific data file. By default, these commands also compact the database. You can override this option with TRUNCATEONLY or specify that you only want to compact the database with NOTRUNCATE.

The following command compacts and then shrinks the Customer database to 30 percent free space:

DBCC SHRINKDATABASE ( Customer, 30 )

The following commands compact and then shrink an individual file in the Customer database to 5 MB free space:

USE Customer
DBCC SHRINKFILE ( Customer_Data, 5 )

Note: The DBCC SHRINKFILE command is the only technique you can use to shrink individual data and log files to be smaller than their original size. With DBCC SHRINKFILE, you must shrink each file individually, rather than trying to shrink the entire database. Additionally, the truncation options for DBCC SHRINKDATABASE and DBCC SHRINKFILE only apply to data files and are ignored for log files. You don't truncate transaction logs with these commands.

Manipulating Databases

Other core administration tasks that you'll often need to perform include restricting, renaming, dropping, detaching, copying, and moving databases. These tasks are examined in the sections that follow.

Temporarily Restricting Database Access

As a database administrator, you'll often need to temporarily restrict access to a database during maintenance. The best way to do this is to put the data- base in single-user mode or to allow only members of db_owner, dbcreator, and sysadmin to access the database. The sections that follow explain both of these options.

Setting Single-User Mode

You'll use single user mode to perform database maintenance and recovery. Before changing to single-user mode, ask all users to disconnect from the database and then ensure that any open Enterprise Manager connections to the database are closed. If necessary, kill the user processes as explained in Chapter 3. Afterward, follow these steps to use Enterprise Manager to place the database in single-user mode:

1. Start Enterprise Manager and then, using the entries in the left pane, work your way down to the Databases folder.

2. Right-click the database you want to work with and, from the shortcut menu, choose Properties. Then in the Properties dialog box, click on the Options tab.

3. Select Restrict Access and then choose Single User.

4. Click OK.

You can also use T-SQL to place a database in single-user mode. Simply follow these steps:

1. Start Query Analyzer and then connect to the database server instance that you want to work with.

2. Use sp_dboption to put the database in single-user mode. The following example puts a database called SupportDB in single-user mode:

use master exec sp_dboption SupportDB,'single user',true

Setting Members Only Access

To allow only members of db_owner, dbcreator, and sysadmin to access the database, follow these steps:

1. Start Enterprise Manager and then, using the entries in the left pane, work your way down to the Databases folder.

2. Right-click the database you want to work with. From the shortcut menu, choose Properties. In the Properties dialog box, click on the Options tab.

3. Select Restrict Access and then choose Members Of Db_owner, Dbcreator, Or Sysadmin.

4. Click OK.

Renaming a Database

You can rename user databases with the sp_renamedb stored procedure. To do this, complete the following steps:

1. Ask all users to disconnect from the database. Ensure any open Enterprise Manager connections to the database are closed. If necessary, kill the user processes as explained in Chapter 3.

2. Start Query Analyzer and then put the database in single-user mode. The following example puts a database called Customer in single-user mode:

use master exec sp_dboption Customer, 'single user', true

**Tip** You execute commands in Query Analyzer by clicking Execute Query or by pressing F5. With ISQL or OSQL, you can execute commands by entering the *go* statement.

3. Rename the database using the sp_renamedb stored procedure. In the following example, the Customer database is renamed cust:

exec sp_renamedb 'Customer', 'cust'

4. After you run the SQL commands, set the renamed database back to multiuser mode. The following example sets the cust database to multiuser mode:

exec sp_dboption cust, 'single user', false

5. Be sure that all commands, applications, and processes that use the old database name are pointed to the new database name. If you don't do this, you'll have problems.

Dropping and Deleting a Database

In SQL Server 2000, dropping and deleting a database are the same thing. When you drop a database, you remove the database and its associated files from the server. Once you drop a database, it's permanently deleted and you can't restore it without using a backup. To delete references to a database without removing the database files, use sp_detach_db, as described in the following section of this chapter, "Attaching and Detaching Databases."

You can drop a database by completing the following steps:

1. Close all user connections to the database.

2. Start Enterprise Manager and then, using the entries in the left pane, work your way down to the Databases folder.

3. Right-click the database you want to drop and then select Delete.

4. When prompted, choose Yes to delete the database.

   Note: You can't drop a database that's being used by SQL Server or by other users. For example, if you're restoring the database or the database is published for replication, you can't delete it. You also can't delete the database if there are any active user sessions.

5. Optionally, back up the master database as explained in Chapter 11, "Database Backup and Recovery." Backing up the master database ensures that the most current system information is stored and that information for the old database won't be accidentally restored with the master database.

You can also delete a database with the DROP DATABASE command. The syntax and usage for this command is shown in Sample 4-2.

Sample 4-2 DROP DATABASE Syntax and Usage

Syntax

DROP DATABASE database_name [,..n]

Usage

DROP DATABASE 'Customer', 'Agencies', 'Resources'

Attaching and Detaching Databases

The sp_detach_db and sp_attach_db stored procedures are designed primarily for use in moving database files or disabling databases without deleting their files. When you detach a database, you remove references to the server in the master database but don't delete the related database files. Detached databases aren't displayed in Enterprise Manager and aren't accessible to users. If you want to use the database again, you can reattach it with the sp_attach_db or sp_attach_single_file_db stored procedure. Attaching a database creates a new database that references data stored in existing data and log files.

Detaching a Database

When you detach a database, you can specify whether you want to update the statistics before you do. Updating statistics makes the database easier to use with read-only media; otherwise, you really don't need it. To update statistics, set the skipchecks flag to TRUE. Detach a database using sp_detach_db, as shown in Sample 4-3.

Sample 4-3 sp_detach_db Syntax and Usage

Syntax

exec sp_detach_db [@dbname =] 'dbname'
   [, [@skipchecks =] 'skipchecks']

Usage

exec sp_detach_db 'sample', 'true'

Tip You can't detach system databases, and you can only detach user databases when they aren't in use. Furthermore, before detaching a user database, you may want to close all current connections, put the database in single-user mode, and then run the detach operation.

Attaching a Database with Multiple Files

When you reattach a database with sp_attach_db, you can specify up to 16 files to associate with the database. This filename list must include the primary file, which contains pointers to the original locations of all other database files, and all files that have changed location.

Attach the database using sp_attach_db, as shown in Sample 4-4.

Sample 4-4 sp_attach_db Syntax and Usage

Syntax

exec sp_attach_db [@dbname =] 'dbname',
   [@filename1 =] 'filename_n' [,...16]
exe sp_attach_db 'Sample',
   'c:\Microsoft SQL Server\mssql$engone\data\sample_data.mdf',
   'c:\Microsoft SQL Server\mssql$engone\data\sample_log.ldf'

Attaching a Database with Only a Data File

You may not need old transaction logs in a new database. Because of this, you may want only to restore a data file and let SQL Server create a new log file for you. To do this, use the sp_attach_single_file_db stored procedure, as shown in Sample 4-5.

Sample 4-5 sp_attach_single_file_db Syntax and Usage

Syntax

exec sp_attach_single_file_db [@dbname =] 'dbname',
   [@physname =] 'physical_name'

Usage

exec sp_attach_single_file_db 'Customer',
   'c:\Microsoft SQL Server\mssql$engone\data\customer_data.mdf'

Copying Databases with Attach

One of the key reasons for using sp_attach_db is to create a copy of an existing database. When you do this, you copy the database's existing files to a new location and then attach them with sp_attach_db. The steps you take are the following:

1. Start Query Analyzer, connect to the database server instance you want to work with, and then run the sp_helpdb stored procedure for the database you want to copy, such as

exec sp_helpdb Customer

2. Write down the absolute file path location of all data and log files for the database.

3. Use SQL Server Service Manager to stop the SQL Server service for the database server instance you want to work with.

4. Start Windows Explorer and then access the folders containing the database's data and log files. Use Copy to copy each file in turn and then use Paste to insert the files at the desired location. Be sure to rename the files if you need to.

5. Use SQL Server Service Manager to start the SQL Server service you previously stopped.

6. In Query Analyzer, connect to the destination database server instance and then use sp_attach_db to create a new database using the files you just copied, such as

exec sp_attach_db 'Customer2', 'c:\Microsoft SQL Server\mssql$engone\data\cust2_data.mdf', 'c:\Microsoft SQL Server\mssql$engone\data\cust2_log.ldf'

7. In Enterprise Manager, configure the new database as necessary.

Moving Databases with Detach and Attach

You can use the sp_detach_db and sp_attach_db stored procedures to move database files to new locations. For example, if you create a database and put all the files in one location, you may want to put the data files and transaction logs on separate drives later. You would do this by detaching the database, moving database files as necessary, and then reattaching the database so that it points to the new file locations.

Moving a database is much like copying a database. You move a database by completing the following steps:

1. Close all active connections to the database you want to move and then put the database in single-user mode by typing

exec sp_dboption Customer, 'single user', true

2. Start Query Analyzer and then run the sp_helpdb command for the database you want to copy, such as

exec sp_helpdb Customer

3. Write down the absolute file path location of all data and log files for the database.

4. Use sp_detach_db to detach the database by typing

exec sp_detach_db 'sample', 'true'

5. Start Windows Explorer and then access the folders containing the database's data and log files. Use Cut to cut files you want to move and then use Paste to insert these files at the desired location.

6. In Query Analyzer, use sp_attach_db to reattach the database. Be sure to reference the new location for files, such as

exec sp_attach_db 'Customer', 'c:\cust\data\cust_data.mdf', 'c:\cust\data\cust_data2.ndf', 'd:\cust\logs\cust2_log.ldf'

Moving a Database to Another Server

You can also move databases to a new server. You detach the database, move the related data and log files to the new server, and then reattach them. You can follow the same procedure described in the previous section, "Moving Databases with Detach and Attach."

Altering a Database

Enterprise Manager gives you an easy way to modify the configuration of a database. Another way to modify a database is to use ALTER DATABASE.

You can use ALTER DATABASE to

• Set database options. You can use it instead of the sp_dboption stored procedure.

• Add new data and log files to a database. All the files must be placed in the same filegroup.

• Modify properties of data and log files, such as by increasing file size, changing the maximum size, or setting file growth rules.

• Add a new filegroup to a database.

• Modify the properties of an existing filegroup, such as by determining whether the filegroup is read-only or read-write and which filegroup is the default.

• Remove files and filegroups from a database. These elements can be removed only when they don't contain data.

The ALTER DATABASE command is designed to make one database change at a time and has the syntax shown in Sample 4-6. The examples in the listing show how you could use ALTER DATABASE to perform key administration tasks. You can use Query Analyzer or ISQL/OSQL. Execute commands with either the Execute Command button or the go statement, respectively.

Sample 4-6 ALTER DATABASE Syntax and Usage

Syntax

ALTER DATABASE database
{ ADD FILE <filespec> [,...n] [TO FILEGROUP filegroup_name]
   | ADD LOG FILE <filespec> [,...n]
   | REMOVE FILE logical_file_name [WITH DELETE]
   | ADD FILEGROUP filegroup_name
   | REMOVE FILEGROUP filegroup_name
   | MODIFY FILE <filespec>
   | MODIFY FILEGROUP fgrp_name fgrp_property [NAME =       new_fgrp_name]
   | SET <optionspec> [,...n] [WITH <termination]
   | COLLATE <collation_name>
}
<filespec> ::=
(NAME = logical_file_name
   [, NEWNAME = 'new_logical_name' ]
   [, FILENAME = 'os_file_name' ]
   [, SIZE = size]
   [, MAXSIZE = { max_size | UNLIMITED } ]
   [, FILEGROWTH = growth_increment ])
<optionspec> ::=
   < state_option >
   | < cursor_option >
   | < auto_option >
   | < sql_option >
   | < recovery_option >
   < state_option > ::=
      { SINGLE_USER | RESTRICTED_USER | MULTI_USER }
      | { OFFLINE | ONLINE }
      | { READ_ONLY | READ_WRITE }
   < termination > ::=
      ROLLBACK AFTER integer [ SECONDS ]
      | ROLLBACK IMMEDIATE
      | NO WAIT
   < cursor_option > ::=
      CURSOR_CLOSE_ON_COMMIT { ON | OFF }
      | {CURSOR_DEFAULT LOCAL | GLOBAL }
   < auto_option > ::=
      { AUTO_CLOSE ON | OFF }
      | { AUTO_CREATE_STATISTICS ON | OFF }
      | { AUTO_SHRINK ON | OFF }
      | { AUTO_UPDATE_STATISTICS ON | OFF }
   < sql_option > ::=
      ANSI_NULL_DEFAULT { ON | OFF }
      | ANSI_NULLS { ON | OFF }
      | ANSI_PADDING { ON | OFF }
      | ANSI_WARNINGS { ON | OFF }
      | ARITHABORT { ON | OFF }
      | CONCAT_NULL_YIELDS_NULL { ON | OFF }
      | NUMERIC_ROUNDABORT { ON | OFF }
      | QUOTED_IDENTIFIER { ON | OFF }
      | RECURSIVE_TRIGGERS { ON | OFF }
   < recovery_option > ::=
      RECOVERY { FULL | BULK_LOGGED | SIMPLE }
      | TORN_PAGE_DETECTION { ON | OFF }

Usage: Adding a File to a Database

ALTER DATABASE Customer
ADD FILE
( NAME = Customerdata2,
   FILENAME = 'c:\mssql\data\customerdat2.ndf',
   SIZE = 10MB,
   MAXSIZE = 500MB,
   FILEGROWTH = 5MB )

Usage: Adding a Filegroup

ALTER DATABASE Customer
ADD FILEGROUP Secondary

Usage: Adding Files and Placing Them in a Filegroup

ALTER DATABASE Customer
ADD FILE
( NAME = Customerdata3,
   FILENAME = 'c:\mssql\data\customerdat3.ndf',
   SIZE = 10MB,
   MAXSIZE = UNLIMITED,
   FILEGROWTH = 5MB),
( NAME = Customerdata4,
   FILENAME = 'c:\mssql\data\customerdat4.ndf',
   SIZE = 10MB,
   MAXSIZE = UNLIMITED,
   FILEGROWTH = 5MB)
TO FILEGROUP Secondary

Usage: Setting the Default Filegroup

ALTER DATABASE Customer
MODIFY FILEGROUP Secondary DEFAULT
Usage: Modifying a File

ALTER DATABASE Customer

MODIFY FILE
(NAME = Customerdata3,
   SIZE = 20MB)

Usage: Removing a File from a Database

USE Customer
DBCC SHRINKFILE (Customerdata3, EMPTYFILE)
ALTER DATABASE Customer
REMOVE FILE Customerdata3

Note: The EMPTYFILE option of DBCC SHRINKFILE empties a file by moving its data to other files in the same filegroup. You can then use the REMOVE FILE option of ALTER DATABASE to delete the file.

Tips and Techniques

All great administrators have a few tricks up their sleeves to help manage databases more efficiently and to keep things running smoothly. Here are a few tricks to keep in mind.

Moving and Resizing tempdb

The tempdb database contains temporary tables created by users or by SQL Server, or both. Unlike in previous versions of SQL Server, you can't put tempdb in memory, and SQL Server 2000 doesn't store complete transactions for temporary tables either. With temporary tables, SQL Server 2000 stores only enough information to roll back a transaction and not enough to redo a transaction.

The tempdb database is created each time you start the SQL Server service, which ensures that the database starts clean. As with other databases, the default structure of tempdb is based on the model database. This means that each time you start SQL Server, a snapshot is taken of the current model database and applied to tempdb.

By default, the tempdb primary data file has a size of 8 MB and is set to automatically grow the database by 10 percent when necessary. On a busy server, this 8 MB can fill up quickly, and as a result the server may need to frequently expand tempdb. Unfortunately, when tempdb is being expanded, SQL Server locks the database. This can slow down queries and make the server seem unresponsive. To improve the performance of tempdb, you can do a couple of things:

• Permanently expand tempdb to accommodate space needs during busy periods. To do this, follow the steps described in the section of this chapter entitled "Expanding Databases and Logs Manually." Even if the model database is smaller, tempdb will retain this new size.

• By default, tempdb is stored in the same location as other data. Unfortunately, you can't move a system database, but you can create a secondary data file for tempdb and put this file on its own drive.

Creating Secondary Data and Log Files

Secondary data and log files can improve the performance of busy databases and can help make large databases easier to manage. One occasion to create secondary files is when you want to distribute the load over several drives. For example, you could place the primary file on drive D, secondary files on drive E, and transaction logs on drive F. See the section of Chapter 1 entitled "SQL Server 2000 and Your Hardware" for more tips on drives and RAID arrays.

Another reason to create secondary files is to make it easier to restore a large database. For example, if you have a 10 GB database in a single file, you can restore the database only on a 10 GB drive, which you may not have at 3 A.M. on a Tuesday if a drive fails. Instead, create several smaller files for the database, such as five 2 GB files, and then you can restore these files to several smaller drives if necessary.

You can create secondary data or log files by completing the following steps:

1. Start Enterprise Manager and then, using the entries in the left pane, work your way down to the Databases folder.

2. Right-click the database you want to work with and from the shortcut menu, choose Properties.

3. To set a secondary data file, click the Data Files tab and then, in the Database Files area, type a new filename, location, initial size, and filegroup.

4. To set a secondary log file, click the Transaction Log tab and then, in the Transaction Log Files area, type a new filename, location, and initial size.

5. Click OK to make the changes.

Preventing Transaction Log Errors

The transaction log is essential to the smooth running of SQL Server. If the log fills up or otherwise fails, SQL Server can't process most types of queries. To ensure that the transaction log runs smoothly, you may want to use these techniques:

• To reduce the load on the transaction log, use SQL commands that aren't logged. This invalidates the transaction logs, as explained in Chapter 11.

• To ensure that the log is cleaned out periodically, set the database option Truncate Log On Checkpoint. This invalidates the transaction logs, as explained in Chapter 11.

• To prevent the log from running out of space, don't set a maximum file size but do increase the frequency of the log backup and watch the amount of free drive space closely.

• To make sure you can recover transactions, increase the permanent size of the log and increase the frequency of the log backup.

Resolving a Filegroup Is Full Error

When a data file can't be written to, you'll see a Filegroup Is Full error. This error usually occurs when the data file has reached its maximum size or you've run out of file space. To reduce the chances of this error reoccurring, you can use the following techniques:

• Don't set a maximum file size.

• Watch the amount of free drive space closely.

• Schedule data files to be compacted periodically.

• Remove unused tables, indexes, or objects.

Creating a New Database Template

The model database is used as the basis of all new databases. If you modify the options and properties of the model database, any new databases created on the server will inherit these options and properties.

Click to order