Table of Contents

Chapter 8

Managing Database Files

Certification Objectives *

Creating Data Files, Filegroups, and Transaction Log Files *

SQL Server Enterprise Manager Database Property Page *

SQL Server Enterprise Manager Create Database Wizard *

Transact-SQL *

From the Classroom *

How Do You Organize Your Data? *

From the Classroom *

Growing Database Files *

Specifying Growth Characteristics *

Expanding or Shrinking the Data or Log Space that is Allocated to a Database *

Adding Files to a Database *

Creating Filegroups *

Changing the Default Filegroup *

Changing the Name of a Database *

Changing the Configuration Settings for a Database *

Detaching and Attaching Databases *

Certification Objectives

Data is everywhere around us. At some point in time, all this data it needs to be stored. This is the reason that databases exist in the first place; as a repository for data. In this chapter we will examine how to create databases and other database files using SQL Server 7.0. We will also examine how to modify a database after it already exists. You might encounter a situation where you no longer require some of the data you have accumulated. In those cases, we will discuss how to drop files and databases out of SQL Server 7.0. On the other hand, your databases may grow so large or (for performance reasons) you may want to manage them using multiple disk drives. In the last section of the chapter we will discuss managing databases on multiple disks.

Creating Databases and Database Files

As you learned in Chapter 1, a SQL Server 7.0 database consists of one or more tables that stores data. It can also include other objects, such as views, indexes, stored procedures, and triggers, that are defined to support the activities that are performed with the data. The data within a database is usually related to a particular process, such as inventory information for a store or employee information for a business.

SQL Server 7.0 has three methods available for a system administrator to create databases:

Creating Data Files, Filegroups, and Transaction Log Files

The procedure for creating a database involves deciding the name of the database, its owner, the size, and the files and filegroups that are used to store the database. Filegroup is a new term used in SQL Server 7.0. Microsoft defines a filegroup as a named collection of one or more files that form a single unit of allocation and administration.

SQL Server Enterprise Manager Database Property Page

As mentioned earlier in the chapter, there are three methods of creating databases in SQL Server 7.0. The first method we will discuss is using the database property page within SQL Server Enterprise Manager as shown in Figure 8-1. There are three tabs available when creating a new database using this method: General, Transaction Log, and Options. The General tab is used to give your database a name, the location it will be located at, as well as to set the initial size for it. There are also options available for allowing the file to grow that we will discuss later in the chapter. In Figure 8-1, you can see that I have created a database with the name of Book. As soon as I typed in the name, SQL Server created a file named Book_Data and located it in the default data path. SQL Server also set an initial size (in megabytes) for the Book database at one megabyte. The initial size of the database is determined from the default size specified for the model database. One other item that SQL Server accomplished was to place the database in the Primary filegroup. When a database is created, the primary filegroup contains the primary data file and any other files that are not put into another filegroup.

Figure 1: The General Tab of the Database Properties Page for Creating a New Database

The Transaction Log page, as shown in Figure 8-2, shows information about the Transaction log associated with a database. SQL Server automatically creates a transaction log, in this case Book_Log, when a new database is created. On this tab you can change the location of the transaction log as well as the size of it. The initial size of the transaction log is determined from the default size specified for the model database.

SQL Server uses the transaction log of each database to recover transactions. The transaction log consists of a serial record of all modifications that have taken place in the database as well as which transaction performed each modification. The transaction log records the start of each transaction and records the changes to the data and enough information to undo the modifications made during each transaction if it becomes necessary.

Exam Watch: , Although there may be more than one, there must be at least one log file for each database. The minimum size for each log file is 512KB.

Figure 2: The Transaction Log Tab of the Database Properties Page for Creating a New Database

The last tab available when creating a new database from SQL Server Enterprise Manager is the Options tab, as shown in Figure 8-3. This tab has several options available that are broken down into two major categories: Access and Settings. Options available under the Access category are used to select the following: whether the database is read only, for DataBase Owner (DBO) only use, or available to a single user at a time. Options available under the Settings category are used for various functions including truncating the transaction log when checkpoint checking occurs, and shutting the database down cleanly after the last user exits.

Figure 3: The Options Tab of the Database Properties Page for Creating a New Database

It is now time for you to create a database using the database property page in SQL Server Enterprise Manager by completing Exercise 8-1.

Exercise 8-1: Creating a Database Using the Database Properties Page in SQL Server Enterprise Manager

  1. Click the Start button and select Programs|Microsoft SQL Server 7.0|Enterprise Manager.
  2. Select a SQL Server Group and click the + to expand it.
  3. Select a server and click the + to expand it.
  4. Right click on Databases and choose New Database from the menu.
  5. Enter the name Book for the new database. Notice that the primary database file is created for you using the .mdf extension.
  6. Click the Initial Size (MB) cell and change it to five.
  7. Select the Transaction Log tab. Notice that the transaction log was automatically created for you using the .ldf extension.
  8. Click the OK button. The Book database has been created.

SQL Server Enterprise Manager Create Database Wizard

The second method available to create a database in SQL Server 7.0 uses the Create Database Wizard. The Create Database Wizard is accessed from the Creating databases selection available on the Setting Up Your Database Solution page as shown in Figure 8-4.

fig 8-4.gif (25956 bytes)

Figure 4: Creating Databases Choice on the Setting Up Your Database Solution Page

Once you select the Creating databases choice from the Setting Up Your Database Solution page, you will be presented with the Create Database Wizard as shown in Figure 8-5. The wizard has several screens in which it steps you through the process of creating the database.

Figure 5: The First Screen of the Create Database Wizard

The best method to see how the Create Database Wizard works is by actually using it to create a database on your system. Exercise 8-2 gives you the opportunity to use the Create Database Wizard.

Exercise 8-2: Using the Create Database Wizard to create a database

  1. Click the Start button and select Programs|Microsoft SQL Server 7.0|Enterprise Manager.
  2. Select a SQL Server Group and click the + to expand it.
  3. Select a server. The Microsoft SQL Server Getting Started Taskpad appears in the right pane.
  4. Click the Setting Up Your Database Solution selection.
  5. Click the Creating Databases selection. The Create Database Wizard starts.
  6. Click the Next> button.
  7. Type Book2 in the Database Name: dialog box. Do not change the path shown in the Location: dialog box.
  8. Click the Next> button.
  9. Change the Initial Size (MB) to five 5 for the database file.
  10. Click the Next> button.
  11. Accept the defaults for file growth and click the Next > button.
  12. Change the Initial Size (MB) to five 5 for the transaction log.
  13. Click the Finish button. Once the database has been created a Wizard Complete dialog box is displayed showing that it was successful.
  14. Click the OK button.
  15. A dialog box is displayed asking whether you would like to create a maintenance plan. Click the No button. Maintenance plans are covered in detail in Chapter 11.

The Create Database Wizard is very useful for administrators that are new to creating databases on SQL Server.

Transact-SQL

The third method available to create a database in SQL Server is to use a Transact-SQL statement. This method is more likely to be used by an experienced SQL Server administrator. The correct syntax for a Transact-SQL statement to create a database is:

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

Refer to Table 8-1 to identify the purpose of each keyword and argument from the Transact-SQL statement. The table lists the keywords and arguments in the order that they appear in the SQL statement.

Keyword/Argument

Purpose

Database_name

The name for the database you are creating. Remember that database names must be unique on a SQL Server.

ON

Explicitly defines the disk files used to store the data portions of the database. ON is followed by a comma-delimited list of <filespec> items that define the data files for the primary filegroup. The <filespec> list can be followed by an optional, comma-delimited list of <filegroup> items that define user filegroups and their files.

PRIMARY

The associated <filespec> list defines the files in the primary filegroup. The primary filegroup contains all of the database system tables. It also contains all objects not assigned to user filegroups. The first <filespec> entry in the primary filegroup creates the primary file, which is the file containing the logical start of the database and system tables. A database can have only one primary file. It is important to note that if PRIMARY is not specified then the first file listed in the CREATE DATABASE statement becomes the primary file.

n

A placeholder that indicates multiple files can be specified for the new database.

LOG ON

Explicitly defines the files used to store the database log files. The keyword is followed by a comma-delimited list of <filespec> items defining the log files. If LOG ON is not specified in the Transact-SQL statement, then the following occurs: a single log file is automatically created with it’s name generated by the system. Its size is 25% of the sum of the sizes of all the data files for the database.

FOR LOAD

This argument is mainly present for compatibility with older versions of SQL Server. The database is created with the database option dbo use only turned on, and the status is set to loading. SQL Server 7.0 does not require this argument because the RESTORE statement can recreate a database as part of the restore operation.

FOR ATTACH

Using FOR ATTACH indicates that you are attaching a database from an existing set of files. There must be a <filespec> entry specifying the first primary file. The only other <filespec> entries needed are those for any files that have a different path than when the database was first created or last attached. The database being attached must have been created using the same code page and sort order as the SQL Server.

NAME

The logical name for the file defined by <filespec>. The NAME parameter is not required when FOR ATTACH is specified.

logical_file_name

The name used to reference the file in any Transact-SQL statements executed after the database is created. Logical_file_name must be unique in the database.

FILENAME

The operating-system file name for the file defined by <filespec>.

os_file_name

The path and file name used by the operating system when it creates the physical file defined by <filespec>. The path for os_file_name must stipulate a folder on the server in which SQL Server is installed.

SIZE

The size of the file defined in <filespec>.

size

The initial size of the file defined in the <filespec>. The default is MB, but you can use either MB or KB suffixes to specify megabytes or kilobytes. The minimum value for size is 512KB and the default is 1MB if size is not specified. The size specified for the primary file must be at least as large as the primary file of the model database.

MAXSIZE

The maximum size to which the file defined in the <filespec> can grow.

max_size

The maximum size that the file defined in the <filespec> can grow. The default is MB but you can use either MB or KB suffixes to specify megabytes or kilobytes. If max_size is not specified then the file will grow until the disk drive is full.

UNLIMITED

The file defined in <filespec> grows until the disk drive is full.

FILEGROWTH

The growth increment of the file defined in the <filespec>. Note that the FILEGROWTH setting for a file cannot exceed the MAXSIZE setting.

growth_increment

The amount of space added to the file each time new space is needed. If the value is 0 then no growth occurs. The value can be specified in MB, KB, or %. If a number is specified without a suffix, then it defaults to MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. If FILEGROWTH is not specified, then it defaults to 10% and the minimum value is 64K. The size specified is rounded to the nearest 64K.

Table 1: Keywords, Arguments, and the Purpose for Transact-SQL Statement to Create a Database

From the Classroom

How Do You Organize Your Data?

In SQL Server you can store one or more tables in each Database File. How do you decide which tables to put in which database files? You cannot, for example, simply put each database in its own file because some tables are needed by more than one application and must be accessible to other databases. On the other hand you cannot store each table as a separate file as you would have too many files to manage. You may want to store your data in several files as show in the following table:

Information Type Where to store
Generic Lookup Tables, such as tax tables, that do not change frequently A utility file.
Major Applications An individual file for each application.

This method will allow you to customize your backup schedule for each file and to separate applications if your business needs change.

by David Smith, MCSE + Internet

Now that you are familiar with the various keywords and arguments used to create a database, let’s take a look at a few examples. The first example creates a database named Marketing. Since the keyword PRIMARY is not used, the first file (Marketing_dat) defaults to being the primary file. Since neither MB or KB are specified in the SIZE parameter for the Marketing_dat file, it defaults to MB and is allocated in megabytes. The Marketing_log file is allocated in megabytes because the MB suffix is explicitly stated in the SIZE parameter.

CREATE DATABASE Marketing
ON

( NAME = Marketing_dat,
FILENAME = 'f:\mssql7\data\marketingdat.mdf',
SIZE = 20,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Marketing_log',
FILENAME = 'f:\mssql7\data\marketinglog.ldf',
SIZE = 10MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )

The next example creates a database called Sales with three 300 MB data files and two 200 MB log files. The primary file is the first file in the list and is specified with the PRIMARY keyword. The log files are specified following the LOG ON keywords. The extensions used in the FILENAME option for the files are:

CREATE DATABASE Sales
ON
PRIMARY ( NAME = Sales1,
FILENAME = 'd:\mssql7\data\salesdat1.mdf',

SIZE = 300MB,
MAXSIZE = 600,
FILEGROWTH = 40),
( NAME = Sales2,
FILENAME = 'd:\mssql7\data\salesdat2.ndf',
SIZE = 300MB,
MAXSIZE = 600,
FILEGROWTH = 40),
( NAME = Sales3,
FILENAME = 'd:\mssql7\data\salesdat3.ndf',
SIZE = 300MB,
MAXSIZE = 600,
FILEGROWTH = 40)
LOG ON
( NAME = Saleslog1,
FILENAME = 'd:\mssql7\data\saleslog1.ldf',
SIZE = 200MB,
MAXSIZE = 400,
FILEGROWTH = 20),
( NAME = Saleslog2,
FILENAME = 'd:\mssql7\data\saleslog2.ldf',
SIZE = 200MB,
MAXSIZE = 400,
FILEGROWTH = 20)

Exam Watch: - When a database is created, all the files that comprise the database are filled with zeros to overwrite any existing data left on the disk by previously deleted files. Although this means that the files take longer to create, it prevents Windows NT from clearing out the files when data is written to the files for the first time during normal database operations. This increases the performance of your database.

From the Classroom

Growing Database Files

A new feature of SQL 7.0 is the ability to grow your database files. In previous versions of SQL your application would stop running when its file was full, even though you still had lots of disk space free. In SQL 7.0 your database files can gown until you are out of disk space.

The problem of a database stopping due to low disk space will still happen in SQL 7.0, just a few weeks or months after it would have in SQL 6.5. Since it is now possible for database files to grow automatically, it is very important to proactively monitor your free disk space on a daily basis. If you have more than 4 or 5 servers you should consider a server monitoring tool such as RoboMon by Heriox Corporation (http://www.robomon.com/) to monitor your free disk space and error logs.

by David Smith, MCSE + Internet

 

Specifying Growth Characteristics

From the Classroom

Growing Database Files

A new feature of SQL 7.0 is the ability to grow your database files. In previous versions of SQL your application would stop running when its file was full, even though you still had lots of disk space free. In SQL 7.0 your database files can gown until you are out of disk space.

The problem of a database stopping due to low disk space will still happen in SQL 7.0, just a few weeks or months after it would have in SQL 6.5. Since it is now possible for database files to grow automatically, it is very important to proactively monitor your free disk space on a daily basis. If you have more than 4 or 5 servers you should consider a server monitoring tool such as RoboMon by Heriox Corporation (http://www.robomon.com/) to monitor your free disk space and error logs.

by David Smith, MCSE + Internet

SQL Server 7.0 can expand a database automatically, based on growth parameters that are defined for the database. If a database has already utilized all of the space allocated to it and it cannot automatically grow, then error 1105 is generated. I highly recommend that you specify a maximum size that each file is allowed to grow. This prevents the file from increasing to the point that no space is left on your disk drive.

A database can be set to grow using either megabytes, or by a percentage of the existing database filesize. When a database is set to grow, the size must be increased by at least 1 MB. Permission for specifying growth characteristics for a database defaults to the database owner, and is automatically transferred with database ownership. The new space is immediately available to the data or log file, depending upon which growth characteristic is modified.

Just as there are three different methods to create a database on SQL Server, there are also three methods to control file growth. You can limit file growth from the following areas: the SQL Server Enterprise Manager database property page, the Create Database Wizard (when the database is first created), or by specifying the maximum size for the file by using the MAXSIZE parameter of the CREATE DATABASE Transact-SQL statement.

Modifying Database Files

Are you stuck with the choices that were made during database creation? Well, you already have learned that a database can be set to automatically grow, but can it be changed in any other manner? The answer is yes. Several changes can be made to the original definition of the database after it is created. Let’s examine the ways that database files can be modified.

Expanding or Shrinking the Data or Log Space that is Allocated to a Database

Manually allocating additional file space on an existing database file or allocating space on another new file can be used to expand a database. We will discuss adding a new file to a database later in the chapter. To manually expand the database you simply change the size using the database property page in SQL Server Enterprise Manager as shown in Figure 8-6.

Figure 6: Changing the Space Allocated for a Database

SQL Server has the capability to allow each file within a database to be shrunk to remove the unused pages. This applies to both data and log files. The three methods of shrinking files are:

Two of the three methods of shrinking files occur automatically. When the database is setup to automatically shrink, the activity occurs in the background and does not affect any user activity that is occurring within the database. If you use the sp_dboption system stored procedure, then shrinking will take place. This will occur whenever a significant amount of free space is present in the database, as it is not possible to configure a percentage of free space to be removed using this method. If you need to configure the amount of free space to be removed, (for example 75 percent of the current free space in the database) then you need to use the database property page within SQL Server Enterprise Manager. ( Ssee Figure 8-7.).

Figure 7: Setting the Database to Shrink by 75 percent of the Free Space

Adding Files to a Database

Data and log files can be added to expand a database. They can also be deleted to shrink a database as well. We will discuss that in a later section of this chapter. Whenever you add a file to a database, it is immediately available for use. The reason for this is because SQL Server 7.0 uses a proportional fill strategy across all the data files within each filegroup, writing an amount of data proportional to the free space in the file. By using the proportional fill strategy, all data files have a tendency to become full at about the same time.

The story is different in regards to log files. Since log files cannot be part of a filegroup; they are separate from one another. As the transaction log grows, the first log file fills, then the second, and so on, using a fill-and-go strategy rather than a proportional fill strategy. Therefore, when a log file is added, the transaction log will not use it until the other files have been filled first.

When adding files to the database, you can specify the same properties for the file as you did when the database was first created. For example, you can specify the size of the file as well as the percentage of file growth as shown in Figure 8-8. Figure 8-8 shows a second file named Nrthwind2 added to the Northwind database. The file is 25MB and grows automatically by 10 percent.

Figure 8: Adding a Second File to a Database

Exercise 8-3 gives you the opportunity to add a file to the Book database that you created in Exercise 8-1.

Exercise 8-3: Adding a File to a Database

  1. Click the Start button and select Programs|Microsoft SQL Server 7.0|Enterprise Manager.
  2. Select a SQL Server Group and click the + to expand it.
  3. Select a server and click the + to expand it.
  4. Select Databases and click the + to expand it.
  5. Right click the Book database and select Properties from the menu.
  6. Click on the blank File name block underneath Book_Data and type Books. Accept the default location, space allocated, and filegroup. Notice that the filename ends with a .ndf file extension.
  7. Click the OK button. The Books data file is added to the Book database.

Creating Filegroups

It is possible for you to create filegroups even after the database has been created. For example, you may need to create filegroups if you add more files to the database. If you do add filegroups you must remember that a file cannot be a member of more than one filegroup. There are three types of filegroups:

Figure 9: A User-Defined Filegroup Named Second for the Northwind Database

Exam Watch: It is impossible to move files to a different filegroup once the files have been added to the database.

Changing the Default Filegroup

I mentioned in the last section that a SQL Server 7.0 database could only have one default filegroup. But what if you decide later that you want a different filegroup to be the default? It is possible to change the default filegroup using a Transact-SQL statement. The following Transact-SQL statement changes the default filegroup on the Northwind database to the Second filegroup.

ALTER DATABASE Northwind
MODIFY FILEGROUP Second DEFAULT

When the default filegroup is changed, the following occurs: any objects that do not have a filegroup specified when they are created are allocated to the data files in the newly designated default filegroup. Another point to consider is the following: changing the default filegroup prevents user objects (that are not specifically created on a user-defined filegroup) from competing with the system objects and tables for data space.

Changing the Name of a Database

It is possible to change the name of a database in SQL Server 7.0 by using Transact-SQL and a system stored procedure. Prior to renaming a database, however, you must make sure that no one is using the database and set the database to single-user mode. The only person allowed to change the name of a database is the system administrator. After changing the name of a database, remember to change it back to multi-user mode. The syntax for the sp_renamedb system stored procedure is the old name followed by the new name. For example, the following Transact-SQL statement changes the name of the camping database to outdoor.

USE master
EXEC sp_renamedb 'camping', 'outdoor'


Changing the Owner of a Database

Just as you can change the name of a database, you can also change the owner of a database using Transact-SQL and a stored system procedure. SQL Server 7.0 allows anyone who has access to connect to SQL Server to become the owner of a database. The sp_changedbowner system stored procedure can only be executed by members of the sysadmin fixed server role or a member of the db_owner fixed database role in the current database. It is important to note that a person cannot become the owner of a database if they already have access to the database through an existing alias or user security account within the database. If this occurs, then you must drop the alias or user from within the database before trying to make them the owner. The following Transact-SQL statement changes the owner of the Book database to Marissa.

USE Book
EXEC sp_changedbowner 'Marissa'

Exam Watch: You cannot change ownership of any of the system databases, such as master, model, or tempdb.

Changing the Configuration Settings for a Database

There are several database options that can be set for each database. By default, only the system administrator or database owner can modify these options. All options are unique to each database and in no way affect any other database. There are two methods of setting database options: using the sp_dboption system stored procedure, or by using the Options tab of the database property page from within SQL Server Enterprise Manager. Only a subset of the options that are available in Transact-SQL are available in SQL Server Enterprise Manager. A checkpoint is automatically issued when you set an option so that modification takes effect immediately. There is not enough room for us to discuss each of the options that is available. Table 8-2, however, shows a sampling of the available options.

Option Purpose Availability
ANSI null default
Allows the control of the database default nullability.
Transact-SQL and SQL Server Enterprise Manager
Autoclose
Allows the database to be closed and shut down cleanly after the last user of the database exits.
Transact-SQL
dbo use only
Sets a database for use only by a member of the db_owner fixed database role in the database. All active users of the database are allowed to continue accessing the database, but no new users are allowed into it.
Transact-SQL and SQL Server Enterprise Manager
merge publish
Allows the database to be used for merge replication publications.
Transact-SQL
Offline
Allows the database to be closed and shutdown cleanly and marked offline. One use of this option is when you need to distribute a database on removable media.
Transact-SQL
read only
Marks a database as read-only. Users can retrieve data from the database, but cannot modify the data.
Transact-SQL and SQL Server Enterprise Manager
single user
Restricts database access to a single user. Any user who accesses the database when single user is in use can continue to use the database. A new user, however, can only access the database if the current user disconnects or switches to another database.
Transact-SQL and SQL Server Enterprise Manager
Subscribed
Allows the database to be subscribed for publication.
Transact-SQL

Table 2: Sample of Database Options that can be Modified Using Transact-SQL and/or SQL Server Enterprise Manager

The syntax for using the sp_dboption system stored procedure is to specify the database name followed by the option name and then the value. The following example uses sp_dboption in a Transact-SQL statement to make the Northwind database read-only.

USE master
EXEC sp_dboption 'Northwind', 'read only', 'TRUE'

To you could use a Transact-SQL statement to turn off the read only attribute. This would allow the users to be able to write to the Northwind database again, as shown in the following example.

USE master
EXEC sp_dboption 'Northwind', 'read only', 'FALSE'

Detaching and Attaching Databases

There is another method of modifying a database that you probably have not thought about as being easily accomplished. What about the idea of moving a database from one server to another server? In the past that would not have been realistic, but SQL Server 7.0 allows the data and log files of a database to be detached and then reattached to another server! You can even reattach it back to the same server if the need arises.

When you detach a database from SQL Server 7.0, the following situation occurs: it removes the database from the SQL Server 7.0 structure but leaves the data and log files intact that comprise the database. These data and log files can then be used to attach the database on any computer running SQL Server. When the database is reattached, it is in exactly the same state as it was when it was detached.

To detach a database from SQL Server, you use the sp_detach_db system stored procedure from within a Transact-SQL statement. The only users that can use sp_detach_db are members of the sysadmin fixed server role. The syntax for sp_detach_db includes the capability to run UPDATE STATISTICS on all of the tables before detaching. If it is set to TRUE then UPDATE STATISTICS will be skipped. If it is set to FALSE then UPDATE STATISTICS will be run. The following example shows the Book database being detached and UPDATE STATISTICS being skipped.

EXEC sp_detach_db Book, true

Two methods exist for reattaching a database to a server. The method you use depends upon the file composition of the data and log files. The first method can be used if your database is comprised of only a single data file and a single log file. The database can be reattached to SQL Server 7.0 without using the log file if the database was cleanly shutdown with no users, as well as no open transactions. After the data file is reattached to a server, SQL Server automatically creates a new log file. The syntax of the sp_attach_single_file_db system stored procedure includes the database name and the file physical name and path. The following example reattaches the Book database to a SQL Server.

EXEC sp_attach_single_file_db @dbname = 'Book',
@physname = 'd:\mssql7\data\Book.mdf'

The second method is to use the sp_attach_db system stored procedure when your database consists of more than one data or log file. The syntax includes the database name as well as the path and filename of each data and log file. sp_attach_db can support a total of 16 filenames. The following example reattaches the Sales database to a SQL Server.

EXEC sp_attach_db @dbname = 'Sales',
@filename1 = 'f:\mssql7\data\Sales.mdf',
@filename2 = 'f:\mssql7\data\Sales_log.ldf'

In order to use either the sp_attach_single_file_db or sp_attach_db system stored procedure, you must have CREATE DATABASE permissions.

On the Job: In today’s world, companies are merging left and right. Having the capability to detach and attach databases is useful if you need to move a database from one company’s server to another company’s server. This alleviates the need to manually recreate the database and then restore from a database backup.

Now that you have seen the different methods of modifying databases, following is a quick reference for possible scenario questions with the appropriate answer:

"The Sales database is rapidly running out of room…" Manually expand it or set it to automatically grow.
"You need to close the database down cleanly after the last user exits.…" Use the autoclose option for sp_dboption from a Transact-SQL statement.
"Martha is the owner of the Sales database and has been replaced in the Sales department by Bill…"

Use sp_changedbowner to make Bill the new Sales database owner.

"The database owner doesn’t want the transaction log file to be bigger than 5 megabytes megabyte…" Add additional 5MB transaction log files to take over when the existing file is full.
"You need to make sure that no one modifies any data in the Sales database…" Use the read only option for sp_dboption from a Transact-SQL statement or select the Read Only box from the Option tab of the database properties page of SQL Server Enterprise Manager.

Dropping Files and Databases

It may become necessary for you to delete a file or database from your SQL Server system. In this section we will first examine removing data and log files from your system. Then we will examine removing databases.

Deleting data and log files removes them from the database. The only way you can remove a file from the database is when there is no data or transaction log information in the file. In other words, the file must be completely empty before it can be removed.

To empty a data file, you use the DBCC SHRINKFILE statement. Using DBCC SHRINKFILE with the EMPTYFILE parameter moves data from the data file to other files in the same filegroup. At that point, SQL Server no longer allows data to be placed in the file, and it can then also be deleted.

Transaction logs are a different story. You cannot move transaction log data from one log file to another in order to delete a log file. You must truncate or back up the transaction log file in order to purge inactive transactions from the log file. The log file can be removed after it no longer contains any active or inactive transactions.

There are two methods available to remove data and log files from a database. Let’s look at the ALTER DATABASE statement first. In the following example the data file named Books is removed from the Book database. Prior to removing the file we will use the DBCC SHRINKFILE statement to make sure it is empty.

USE Book
DBCC SHRINKFILE (‘Books’, EMPTYFILE)
ALTER DATABASE Book
REMOVE FILE Books

As mentioned in the last paragraph, there are two methods available to remove data and log files. The second method is to use the database property page from within SQL Server Enterprise Manager. Exercise 8-4 gives you the opportunity to delete the Books file in the Book database that you created in Exercise 8-3. Because we have not added any data to the file, it can be deleted without using the DBCC SHRINKFILE statement.

Exercise 8-4: Deleting a Data File from a Database

  1. Click the Start button and select Programs|Microsoft SQL Server 7.0|Enterprise Manager.
  2. Select a SQL Server Group and click the + to expand it.
  3. Select a server and click the + to expand it.
  4. Select Databases and click the + to expand it.
  5. Right click the Book database and select Properties from the menu.
  6. We want to delete the Books data file, so we will use the General tab. If you, however, wanted to delete a log file, then you would click on the Transaction Log tab.
  7. Click the box to the left of the Books name and an arrow appears.
  8. Press the Delete key on your system. A red X appears on top of the arrow indicating that the file will be deleted, as shown in Figure 8-10.
  9. Click the OK button. The Books data file is deleted.

 

Figure 10: Deleting the Books Data File from the Book Database

You may decide that you need to not only delete the data or log files, but you may also need to delete the entire database when it is no longer needed. It is important to remember that when a database is deleted, all of its files and subsequent data are deleted from the disk drive on the server. Once a database is deleted, it cannot be retrieved without using a previous backup.

You can use a Transact-SQL statement or the SQL Server Enterprise Manager in order to delete a database. Let’s look at the Transact-SQL statement first. It is very straightforward and allows more than one database to be dropped at a time. The first example deletes the Book database. The second example deletes the Sales and Pubs databases.

DROP DATABASE Book
DROP DATABASE Sales, Pubs

Using SQL Server Enterprise Manager you can delete a database by expanding down to the database you want to delete. Right click on the database and select Delete. You are presented with a confirmation dialog box. Selecting Yes deletes the database. The only way to recover the database would be to restore from a backup.

No matter whether you delete a database using Transact-SQL or the SQL Server Enterprise Manager, I recommend that you back up the master database immediately. This is because deleting a database updates the system tables in master. If for any reason you need to restore the master database, then any database that has been deleted since the last time you backed up the master will still have references in the system tables. Because of the leftover references, this may cause error messages once you restore the master database.

Managing Databases on Multiple Disks

As a database grows bigger and bigger, the greater the hardware requirements that it needs for support grow. There are other items that also dictate the hardware requirements as well: the number of concurrent users/sessions, transaction throughput, and the type of operations within the database. Therefore, in this section we will discuss managing databases on multiple disk drives. RAID (redundant array of inexpensive disks) is used to manage databases on multiple disk drives. RAID is a disk system comprised of an array of multiple disk drives that provide improved performance, reliability, and storage capacity. Fault-tolerant arrays are categorized in six RAID levels, 0 through 5. Each level uses a different algorithm to implement fault tolerance.

SQL Server does not include RAID from within the product itself, but the implementation of RAID on your server can directly affect SQL Server’s performance. Using RAID technology to spread a database over multiple disks can yield performance improvements. RAID exists in two implementations: hardware and software. Hardware RAID delivers improved I/O performance because I/O (input/output) functions (such as striping and mirroring) are efficiently handled in firmware, but is very costly. On the other hand, software based RAID offers lower cost, but consumes processor cycles.

Normally, RAID levels 0, 1, and 5 are used with SQL Server. RAID 0, data striping, is the highest-performance RAID configuration. The issue here is if one disk fails, all the data on the stripe set becomes inaccessible. A common installation technique for relational database management systems is to configure the database on a RAID level 0 drive, and then place the transaction log on a RAID 1 (mirrored) drive. You can get the best disk I/O performance for the database and maintain data recoverability through a mirrored transaction log. Of course, this counts on you making regular backups of your database!

If data must be recovered quickly then you should consider mirroring the transaction log with RAID 1 and placing the database on a RAID 5 disk. RAID 5 provides redundancy of all data on the array, allowing a single disk to fail and be replaced in most cases without system down time. RAID 5 is much slower than RAID 0 or 1 and you should only use it when the SQL Server backup and recovery mechanisms cannot provide the necessary recovery time.

Certification Summary

There are several different methods of creating a database from within SQL Server. These methods range in the expertise needed all the way from the database novice to the database expert. Databases consist of several different items including data files, filegroups, and transaction log files. Databases can be set to grow automatically by a percentage, or in megabytes.

After a database has been created, it can be modified in several ways. The database owner can be changed, files can be added, existing files can be expanded or shrunk, as well as many other modifications. You use either the SQL Server Enterprise Manager or Transact-SQL statements to complete the modifications.

Not only can files be added to a database, but they can also be deleted. Data files and log files must be empty before they can be deleted. The data in data files can be migrated to other data files in the filegroup. Transaction log files cannot migrate data to other log files so they must be truncated and purged of inactive transactions.

The hardware requirement for SQL Server changes as your database grows and there is an increased amount of concurrent users and/or sessions. By managing the placement of data on drives, you can improve performance, implement fault tolerance, or both.

Two-Minute Drill