Creating and Altering Databases

• Create and alter databases. Considerations include file groups, file placement, growth strategy, and space requirements.

The file format in SQL Server 2000 has not significantly changed from the previous version (SQL Server 7). SQL Server uses a set of files to store the data, indexes, and log information for a database. A primary file also has some header information in it, providing SQL Server with necessary information about a database. Each database has a minimum of two files associated with it, one for the data and a second for the log. It is also possible to create multiple files for each of these purposes as described in the following paragraphs. File placement, and object placement within these files, plays an important role in the responsiveness of SQL Server. A database consists of two or more files with each file used for only a single database. A single file cannot be shared by multiple databases.

Each database has one or more files used to store indexes and data. The first file created for this purpose is referred to as the primary file. The primary file contains the information needed to start up a database and is also used to store some or all of the data. If desired, secondary files might be created to hold some of the data and other objects. Some databases might be large enough or complex enough in their design to have multiple secondary files used for storage.

Normally the log is maintained in a single file. The log file is used to store changes to the database before these changes are recorded in the data files themselves . The storage of information into log files in this manner enables SQL Server to use these files as an important part of its recovery process. Every time the SQL Server is started, it uses the log files for each of the databases to determine what units of work were still being handled at the time the server was stopped .

The file names given to all data and log files can be any desired name, although it is recommended that you select a name that gives some indication as to the content of the file. The file extensions for the primary data file, secondary data file(s), and log files can also be any chosen set of characters . It is recommended for consistency and standardization that the extensions be .mdf, .ndf, and .ldf for the primary, secondary, and log files, respectively.

Creating Database Files and Filegroups

All files needed for a database can be created through a single activity using SQL Server's Enterprise Manager, or with a single CREATE DATABASE Transact SQL statement. Either of these methods can be used to initialize all files and create the database and logs in a single step. The number of files you create and the placement of the files are addressed a little later in this chapter. (See the sections, "Using Filegroups" and "File Placement.")

The default names for the primary database and transaction log files are created using the database name you specified as the prefixfor example, NewDatabase _ Data .mdf and NewDatabase _ Log .ldf . These names and locations can be changed if desired from the default values provided for the new database file. The Transact SQL (T-SQL) syntax for creating a database is as follows :

[View full width]

 
 [View full width] 
 CREATE DATABASE databasename   [ON[<filespec>[,...n]][,<filegroup>[,...n ]]][LOG ON{<filespec>[,...n ]}] [COLLATE collationname][FOR LOAD  FOR ATTACH]  <filespec>::=   [PRIMARY]   ([NAME=logicalfilename,][FILENAME='physicalfilename'] [,SIZE=size][,MAXSIZE={size  UNLIMITED}]    [,FILEGROWTH=growthincrement])   [,...n ]<filegroup>::=FILEGROUP filegroupname <filespec>[,...n ]] 

In the procedures that follow, you have the opportunity to create a database one step at a time. There are two techniques that can be used for these procedures. The first option uses the Create Database Wizard tool and Enterprise Manager in Step by Steps 3.1 and 3.2. The second option available provides for the syntax for the creation of a database using T-SQL code.

STEP BY STEP

3.1 Creating a Database Using the Create Database Wizard

1. Expand a server group , and then select the server in which to create a database.

2. On the Tools menu, click Wizards.

3. Expand Database.

4. Double-click Create Database Wizard.

5. Complete the steps in the wizard.

Or, if you prefer to use the Enterprise Manager:

STEP BY STEP

3.2 Creating a Database Using the Enterprise Manager

1. Expand a server group and then the server where the database is to be placed.

2. Right-click Databases, and then click New Database.

3. Enter a name for the database.

4. To specify how any specific file should grow, switch to the Data Files or Transaction Log tabs, highlight the file, select Automatically Grow File, then choose In Megabytes or By Percent and specify a value. To specify the file size limit, select Unrestricted Filegrowth or Restrict Filegrowth (MB) and specify a value.

To use T-SQL to create a database, use this code:

 CREATE DATABASE MyDatabase ON (NAME = 'DataStore',    FILENAME = 'd:\data directory\DataStore_MyDatabase.mdf',    SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB) LOG ON (NAME ='LogStore',    FILENAME = 'e:\log directory\LogStore_MyDatabase.ldf',    SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB) 

You can use the CREATE DATABASE statement to create a database from script. Saving the script enables you to re-create a similar database on another server in the future. Any SQL Server object can have its creation script saved. The advantages of saving these scripts are discussed later in this chapter. Using the CREATE DATABASE statement to create a database using multiple files and log files would look similar to this:

 CREATE DATABASE Example ON PRIMARY (NAME = ExampleData, FILENAME = 'c:\mssql\data\sampdat.mdf',           SIZE = 10MB,           MAXSIZE = 20MB,           FILEGROWTH = 2MB),         (NAME = ExampleIndexes, FILENAME = 'c:\mssql\data\sampind2.ndf', SIZE = 10MB,           MAXSIZE = 20MB,           FILEGROWTH = 2MB),         (NAME = ExampleArchive, FILENAME = 'c:\mssql\data\samparch.ndf', SIZE = 10MB,           MAXSIZE = 20MB,           FILEGROWTH = 2MB) LOG ON (NAME = ExampleLog1, FILENAME = 'd:\mssql\log\samplog1.ldf', SIZE = 10MB,           MAXSIZE = 20MB,           FILEGROWTH = 2MB),         (NAME = ExampleLog2, FILENAME = 'd:\mssql\log\samplog2.ldf', SIZE = 10MB,           MAXSIZE = 20MB,           FILEGROWTH = 2MB) 

Important issues with regard to appropriate use of the CREATE DATABASE statement are as follows:

• The default growth increment measure is MB, but can also be specified with a KB or a % suffix. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs.

• A maximum of 32,767 databases can be defined on a server.

• The minimum size for a log file is 512K.

• Each database has an owner. The owner is the user who creates the database. The database owner can be changed through sp_changedbowner .

• The Master database should be backed up after a user database is created.

• The default unit of measure for the size and maxsize settings is MB if you supply a number, but no measure is provided. If no options are supplied, maxsize defaults to unlimited and the filegrowth is 10%.

At the time that you create the database and its associated files, you provide values to determine the initial file sizes, whether and how the files will grow, as well as some other basic database and file properties. The initial settings are used as a basis for future file system activities. If at a later date the initial settings are in need of alteration, you can perform this activity through the Enterprise Manager or by using the ALTER DATBASE T-SQL statement.

NOTE

Selecting a Secure Partition When interacting with a Windows 2000 or Windows NT Server operating system, ensure that all data is stored on an NTFS partition with appropriate security measures. NTFS provides for a flexible file system while maintaining a complete permission set for files and folders stored on disk. Using NTFS partitions helps prevent file tampering and allows for more flexible disk administration.

Using the Model Database

When you create a database for the first time, that database initially takes most of its attributes from the Model database. The Model database is a system database that SQL Server uses as a kind of template for database creations. It is a good and common practice to set the properties and contents of the Model database based on the majority of new databases that are to be created.

IN THE FIELD: OBJECT AND CODE REUSE

In practice, many objects are stored in the Model database to minimize the need to re-create these objects every time a database is created. Common elements placed in the Model often include specialized user-defined functions and data types that are present and frequently used by the development staff in their coding. In theory, objects are created for use in a single database, but all developers realize that object and code reuse is an important facet of easing the development process.

Often an object, such as a user-defined function, standard security role, or corporate information table, can be found in most if not all databases within a company. A property value, such as recovery level, might also have a standard implementation across all servers in the enterprise. If an object or property value is going to be present in most of the user databases, placing the object into the Model database or setting a property accordingly can save you the work of performing the activity as a post-creation task.

Using a Collation Sequence

A collation sequence is a set of rules governing over the characters that are used within a database and the means by which characters are sorted and compared. In SQL Server 2000 this sequence can be set on a database-by-database basis. In previous versions of SQL Server, the collation sequence was a server-wide setting. You therefore had to either perform a whole series of rebuilding actions to create a database that did not use the server collation, or install the database on a separate server altogether.

In SQL 2000 you can specify a non-default collation for any database on the server. This means that one database does not have to have the same characters or sorting rules as the rest of the databases on the server. If all but one or two of your databases have the same set of characters, then a single server can now implement the functionality that would have previously taken two separate machines.

To create a database with a non-default collating sequence, provide the COLLATE clause on the CREATE DATABASE command. You might also select the collation name from the drop-down box in the Enterprise Manager when you create the database from the GUI.

Be careful in the use of multiple collating sequences because it makes the transfer and entry of data more complex. It might also limit the application development environment and techniques normally used for data entry and editing.

Altering Database Properties

A number of the database properties affect the way in which some SQL Server commands operate . You can use the Enterprise Manager to make appropriate adjustments to some of the database properties. Alternatively you can use the ALTER DATABASE T-SQL statement to script these changes.

In altering a database, you can add or remove files and filegroups and/or modify attributes of the files and filegroups. ALTER DATABASE also enables you to set database properties, whereas in previous versions these properties could only be changed using the sp_dboption stored procedure.

WARNING

Be Sure of Your Collation Sequence After the collation sequence is set, it can be changed only through rebuilding of the database. If possible, collation decisions should be made during the logical design of the system so that you don't have to rebuild. Although collations can be different, if you want to change the sequence post creation, you will have to rebuild the database.

Using Filegroups

In a lot of database scenarios, you will not implement more than one data file and one log file. In a number of instances, however, you might want to implement a filegroup . Filegroups enable a group of files to be handled as a single unit, and thus make implementations that require multiple files easier to accommodate. With filegroups, SQL Server provides an administrative mechanism of grouping files within a database. You might want to implement filegroups to spread data across more than one logical disk partition or physical disk drive. In some cases, this provides for increased performance as long as the hardware is sufficient to optimize reading and writing to multiple drives concurrently (see the section on "File Placement"). You might also have a performance gain through the appropriate placement of objects within these groups.

NOTE

Setting Options Using T-SQL The system-stored procedure sp_dboption can still be used to set database options, but Microsoft has stated that in future versions of SQL Server this functionality might not be supported.

You can create a filegroup when a database is created, or you might add them in later when more files are needed or desired. After a filegroup has been assigned to a database, you cannot move its files to a different filegroup. Therefore, a file cannot be a member of more than one filegroup. SQL Server provides for a lot of flexibility in the implementation of filegroups. Tables, indexes, text, ntext, and image data can be associated with a specific filegroup, allocating all pages to one specific group. Filegroups can contain only data files; log files cannot be part of a filegroup.

Objects can easily be moved from one filegroup to another. Using the appropriate property page, you just select the new filegroup into which you wish to move the object.

Placement of Objects Within Filegroups

Placement of individual objects can aid in organizing data and at the same time provide for improved performance and recoverability. Many different objects can be assigned to separate files or filegroups.

For reasons given in the next few paragraphs, you might want to place the following objects into separate filegroups:

• Indexes

• A single table

• Text, ntext, or image columns

If you place indexes into their own filegroup, the index and data pages can be handled as separate physical read elements. If the associated filegroups are placed onto separate physical devices, then each can be read without interfering with the reading of the other. This is to say that while reading through an index in a sequential manner, the data can be accessed randomly without the need for manipulating the physical arm of a hard drive back and forth from the index and the data. This can improve performance and at the same time save on hardware wear and tear.

Placing an entire table onto its own filegroup offers many benefits. If you do so, you can back up a table without having to perform a much larger backup operation. Archived or seldom-used data can be separated from the data that is more readily needed. Of course the reverse is true: A table that needs to be more readily available within a database can be placed into its own filegroup to enable quicker access. In many instances, planned denormalization (the purposeful creation of redundant data) can be combined with this feature to obtain the best response.

Placing text, ntext, and image data in their own filegroup can improve application performance. Consider an application design that allows the data for these column types to be fetched only upon user request. Frequently, it is not necessary for a user to view pictures and extensive notes within a standard query. Not only does this accommodate better-performing hardware, but it can also provide faster query responses and less bandwidth saturation, because data that is not required is not sent across the network.

Considerations for Backup and Restore

Filegroups can provide for a more effective backup strategy for larger database environments. If a large database is placed across multiple filegroups, then the database can be backed up in smaller pieces.

This is an important aspect if the time to perform a full backup of the entire database is too lengthy.

To perform a backup in this manner, you would create a schedule to back up the individual filegroups (after an initial full database backup). In between each of the filegroup backups you then schedule log backups . Using this strategy enables you to break up an exceedingly large and long backup into more manageable increments .

After a determination has been made to use a filegroup strategy for storing data, always ensure that when a backup is performed against a filegroup that the indexes are also backed up at the same time. This is easily accomplished if the data and indexes are stored in the same filegroup. If they are located on separate filegroups, ensure that both the data and index filegroups are included in a single backup operation.

File Placement

After the decision has been made to go with filegroups, then comes the next major decision in the physical design: where to put the filegroups. Also, although logs are not stored into filegroups, they are stored in files and the placement of these files is very important.

WARNING

SQL Server Does Not Enforce Backup Be aware that SQL Server does not enforce backup of data and index filegroups in a single operation. You must ensure that the files associated with the indexes tied to a particular data set are backed up with the data during a filegroup backup.

Considerations in the placement within the file system depend on a number of variables . The first consideration is sequential versus random access. When a file is being read sequentially, the moving parts of the physical data device do less work ( assuming no fragmentation). A large read/write process can use multiple physical devices at one time if they are placed on appropriate RAID hardware. Of course, there is also a software implementation of RAID that might not outperform the hardware one but is still beneficial.

Another consideration for file placement is system recoverability. When files are spread amongst multiple physical volumes , a fuller and faster recovery becomes possible in the event of hardware failure. Also, many other operations can benefit from appropriate file placement. The next four topics look at these considerations and discuss some of the instances where they each might be implemented.

Sequential/Random Access Considerations

Many processes performed within SQL Server can be classified as sequential or random. In a sequential process, the data or file can be read in a forward progression without having to locate the next data to be read. In a random process, the data is typically more spread out, and getting at the actual physical data requires multiple accesses .

NOTE

Log Files Logs are not stored in filegroups. You can, however, use multiple log files and place them in different locations to obtain better and more varied maintenance and allow more storage space for log content.

Where possible, it is desirable to keep sequential processes running without physical interruption caused by other processes contending for the device. Using file placement strategies to keep random processes separate from sequential ones enables the configuration to minimize the competition over the placement of the read/write heads.

In an ideal configuration (somewhat tongue in cheek), you might want to separate the operating system from its page file. You would then place the log onto its one drive, separate from the data, with the data configured over a RAID volume as described in the following section. Take the seldom-used data (column or table data) and separate it from data that will be accessed more frequently. Place the indexes off on their own volume as well, and for about $150.00$200,000.00, you have the optimum performance in a database server. In fact, while you're at it, why not throw in a couple of extra network cards and a few processors?

Obviously, in most production environments the database team must balance an ideal configuration with the company bottom line. For many of these volume placements, a definitive cost must be budgeted.

As a minimum requirement for almost any implementation, you should separate the normal sequential processing of the log files from the random processing of the data. You also improve recoverability by separating the data from the log and placing them on separate physical volumes. If the volume where the data is stored is damaged and must be restored from backup, you will still have access to the last log entries. The final log can be backed up and restored against the database, which gives something very close to 100% recoverability right to the point of failure.

An interesting and flexible strategy is to provide a separate drive solely for the log. This single volume does not have to participate in RAID architecture, but RAID might be desired for full recoverability. If you give the log the space of an entire volume, you give the log more room to grow and accumulate more of the log over time without the need for periodic emptying. Less frequent log backups are needed and the best possible log performance is achieved.

RAID Considerations

RAID (Redundant Array of Independent/Inexpensive Disks) is a technology where two or more disk drives can be configured in such a manner as to provide

• Larger volumes, because space on multiple disks is combined to form a single volume.

• Improved performance, by interacting with more than one physical disk at a time (disk striping).

• Safeguarding data, by providing mechanisms (mirror or parity) for redundant data storage.

RAID is classified under many categories, each category assigned as a number. For more information about RAID hardware or other interesting information, visit the web site of the RAID advisory board: http://www.raid-advisory.com/CIC.html. This book is concerned with only three RAID levels, 0 (zero), 1, and 5, although there are many different other qualifications for RAID.

RAID 0 (stripe set) provides for multiple simultaneous read/write access across two or more disks. There is no data redundancy and thus no fault tolerance. A striped implementation is valid when strong backups exist and recovery time is not relevant. It might also be considered if the data is considered somewhat trivial and loss of data is unimportant. Parity sets provide optimum performance with no waste space allocated to data redundancy. Microsoft recommends a 64K stripe size, which should be considered if you are using RAID 0.

RAID 1 (mirror) provides the exact duplication of one volume onto another. This solution offers quick recoverability but has a performance cost. Everything written to one volume is then written a second time to the alternate volume. A mirror implementation is valid for operating system drives or any other system where speed is not as important as recovery time in the event of a failure. In most implementations, if the first drive fails, the system has little or no downtime because it can operate fully on the mirror drive. Mirrors are a more costly form of fault tolerance than parity sets, losing a full 50% of available space to data redundancy.

An alternative form of mirroring, duplexing, involves not only the duplication of hard drives but also redundant drive controllers. In using duplexing , you achieve fault tolerance over the loss of the controller as well as hard drive failure. To achieve up-to-the minute recovery in any failure, you might want to place your log files on a mirror or duplexed volume.

RAID 5 (parity set) provides the best read performance while still giving the recoverability through data redundancy. In a parity set, the data is written across all the available drives in segments referred to as stripes . In each stripe, all but one drive will contain data, with the remaining drive containing the parity check information. Each time data is written, a checksum is calculated and written to the parity segment. If a failure causes the loss of a disk drive, the parity segment can be used to enable the stripe set to be regenerated. RAID 5 is usually referred to as a poor man's mirror because the more drives that are included in the set, the more cost-effective this solution. For example, if three drives are used, a third of the available space is lost to redundancy. If ten drives are used there is only a 10% loss of usable space.

IN THE FIELD: RAID: SOFTWARE VERSUS HARDWARE

Even though software implementations of RAID must be known to pass certification exams and will be found in production systems, they are not nearly regarded as reliable as hardware RAID. For any high-volume, mission-critical application, it is therefore preferred to set up data redundancy mechanisms at the hardware level.

Recoverability in the Event of Failure

Two primary concerns in most data environments are data recoverability in the event of the inevitable failures and considerations for minimal downtime. In the industry, one of the optimum ratings to strive for is the elusive "five nines" (99.999). This rating means that over any given period of time ( generally accepted standard of 365 days minimum), the server remained online and servicing the end user 99.999 percent of the time. In other words, the total downtime for an entire year is a little over 5 minutes.

In an attempt to achieve as little downtime as possible, it is essential to consider a strategy that involves multiple servers and redundant other hardware, as well as other issues on each machine. Data redundancy, adequate backups, and some form of disaster recovery plan must all be a part of a complete solution. Most of the topics surrounding server clustering fall out of the scope of this book, although the partitioned views will be discussed at length within the section "Multiple Server Implementations," later in this chapter. Other multi-server functionality, such as data replication, is addressed in Chapter 11, "Implementing and Understanding Replication Methodologies."

Though most of the topics related to recoverability fall into the realm of administration, you need to give some consideration to these processes when you put together a physical design. The following three sections explain these considerations as they pertain to database design.

System and Data Recovery

Recovering from outages and minimizing data loss in the event of hardware failures involves prior planning, adequate backups, and the setting of appropriate database and server options. On the server, the recovery interval and service startup options can be adjusted to lessen the time it takes for a SQL Server to be online and operational after a power failure or other serious service interruption. In each database, the recovery model can be set to determine the log usage and amount of lost data activity upon failure. Backups are one of the most important aspects of recovery. Backups must be maintained in a diligent and thorough manner. Finally, a plan of action that is regularly practiced must be part of a workable solution.

Operating System Service Properties

In SQL Server 2000, two aspects of the server allow for a successful server database restart in the event of failure. The operating system's services can be configured to automatically start upon computer startup and can also be set up to respond to service interruptions. To set service properties, you must locate the MSSQLSERVER service. This service can be found in your administrative tools, Services for Windows 2000, or Control Panel Services for NT. For the configuration options as displayed using the Windows 2000 services properties, see Figures 3.1 and 3.2. The database recovery interval can be set for the number of minutes each database takes to start up after an outage or controlled server startup. You can find the Recovery Interval option in the Enterprise Manager by right-clicking the server, selecting Properties from the pop-up menu, and navigating to the Database Settings tab.

Figure 3.1. General properties for operating system services.

Figure 3.2. Recovery properties for operating system services.

Use of Recovery Models

Some of the database properties that were available in previous releases of SQL Server have been combined to form what is referred to in SQL Server 2000 as recovery models . Setting the appropriate model can enable most if not all data activity to be recovered in the event of system failures. Three models are supported: Simple, Bulk-Logged, and Full.

NOTE

User Accounts for Services Separate user accounts can be identified for each of the SQL Server services. Conversely, the same account can be used for all services and several servers. As a standard implementation, it is usually best to use the same account. You might want to use separate accounts for each server, particularly if you want each server to send and receive email as a separate identity.

The Simple recovery model ensures higher performance during bulk copy operations and maintains a smaller database log. However, the model does not support transaction log backups and, therefore, there will be data loss in the event of a failure because the database can be restored only to the point of the last full or differential database backup.

Bulk-Logged recovery also allows for high-performance bulk procedures that use minimal log space. Some data might be lost, but because you can perform log backups, usually the only loss will be bulk operations since the last backup.

If recoverability to a specific point in time with as little data loss as possible is the goal, then the Full recovery model should be set. The Full recovery model makes the heaviest use of the database logs.

Backup and Disaster Recovery

Usually backup and disaster recovery falls into the realm of the database and network administrators. In a total systems design strategy, a complete plan of recovery activity that includes a variety of scheduled backups and other tasks is documented and tested . This disaster recovery plan will be updated as needed, because test recovery and practicing the plan is sure to point out anything that might be otherwise missed. Though not a specific detail of implementation, the topic of recoverability would not be complete without at least the mention of a system-wide plan. Consider a regular exercise of simulating failures to test the plan.

Standby Servers and Log Shipping

A warm backup server or standby server is a lower-cost implementation that is often selected as an alternative to replication or clustering. The premise is to back up the production server on a regular basis, restoring it to a second machine that can be put into production in the event of failure in the first computer. A standby server can also assist in taking some of the workload from the production machine if it is used as a read-only query server.

In SQL Server 2000 you can use the Maintenance Plan Wizard to implement a standby server configuration. The wizard prompts you through the configuration of backups and regularly scheduled log shipments to the standby machine.

Space Requirements

Over time, the size of the database will need to be adjusted to accommodate new data or data removal. The configuration of the ideal server in any given situation will vary greatly. The applications that a server is intended to handle usually point toward the size of machine needed and its associated peripherals.

For a general guideline or minimum starting point, consider the following:

• Multiple processors . Preferred in most database environments (keep in mind that licensing models change).

• RAM . Can you ever have enough RAM? Start out with 1GB and don't be afraid to work your way up.

• OS drive mirror . Two physical disks set up in a physical or software mirror. In some cases, the use of two physical controllers provides for complete disk duplexing.

• Data parity array . A number of separate physical drives. A number of 4 to 6 usually provides an adequate size volume, but this might vary in larger systems.

• Log volume . One disk used for log storage. In some cases, this volume also stores data files for implementations that include archived data storage. You might also want to mirror this volume to ensure up-to-the-minute data recovery.

File Growth Strategies

SQL Server 2000 enables you to set database files so that they expand and shrink automatically as needed, eliminating the need for additional administration. By default, SQL Server enables data files to increase in size as needed for data storage. Therefore, a file can grow to the point where all disk space is exhausted. You can specify that a file is not to grow beyond its creation size or implement a maximum size for file growth. Ensure that disk space is not exhausted by using the MAXSIZE option of the CREATE DATABASE or ALTER DATABASE statements to indicate the largest size to which a file can grow.

NOTE

Licensing Models With the release of SQL Server 2000, the licensing models available included Per Seat or Per Processor. The Per Server model has been discontinued.

In a volatile environment, the database and its related files might frequently increase and decrease in size and this activity might be the desired operation of the server. In most instances, an implementation providing for more stability in the file system is the desired end result. A determination has to be made as to whether the database stays at about the same size or grows or shrinks over time. In most scenarios, a database grows over time and needs to be reduced only when data is archived.

When creating the files, you should set the SIZE , MAXSIZE , and FILEGROWTH parameters so that the database can increase in volume over time. The FILEGROWTH configuration should be implemented in larger increments so that growth within the file system isn't occupying too much of the server's resources. Growth of files occurs in the background and can be minimized by using a larger growth increment. Always provide a MAXSIZE entry even if the entry itself is close to the capacity of the volume.

Shrinking Files

File "shrinking" might be required as an application ages. In most operations, the older the data is, the less valuable its presence is among the mainstream data. As data ages, it is less likely to be queried and thus is passed over by most reads. It might become " wasted space" in the database and unnecessarily consume system resources. A system design usually includes means by which data is aged out into archive tables. After the archival process has completed, there might be a high percentage of empty space in the data files.

You can shrink each file within a database to remove unused pages. This applies to both data and log files. It is possible to shrink a database file manually or as a group. You use the DBCC statement with the SHRINKDATABASE or SHRINKFILE parameters ( DBCC parameters are shown in the Fast Facts section in Part II "Final Review"). Use DBCC SHRINKDATABASE to shrink the size of the data files in the specified database, or you can selectively choose a specific file and shrink its size using DBCC SHRINKFILE .

You can set the database to automatically shrink at periodic intervals by right-clicking the database and selecting the database Properties page from within the Enterprise Manager.

Ongoing System Maintenance

After a database and associated files have been created and the implementation is complete, it's necessary to maintain the system using the periodic application of several commands. It might be necessary to adjust the database and file properties as the database system matures. In addition, DBCC (Database Consistency Checker) has a number of parameters to assist in regular maintenance activities.

As a starting point, use the SQL Server Maintenance Wizard to perform the necessary maintenance tasks. Adjust the scheduling of these tasks as needed to maintain a healthy server. Watch and adjust indexing and data structures because over time they will become fragmented . Indexing and data structures as well as other database objects are discussed more fully after the Review Break.

REVIEW BREAK: Physical Storage

Creating and altering databases involves selecting the physical volume type for each database file, setting the appropriate file properties, placing the objects into the files/filegroups, and ensuring appropriate adjustments are made as the database matures. The type of business needs that the database is being designed to meet helps to indicate the measures needed to ensure adequate performance.

Try to place onto separate volumes any files that might tend to compete with each other for read cycles during a single operation. Place log files away from the data to ensure adequate recovery and make sure that database properties have been set in such a way as to ensure that maintenance tasks can be performed.