Lesson 3: Managing Databases

[Previous] [Next]

When data modification activity increases or when the size of data increases, the size of the database and log files may need to increase. SQL Server 7 automatically increases the size of database files if the database options are set to do so. You can manually increase or decrease the size of database files by using the ALTER DATABASE, DBCC SHRINKDATABASE, and DBCC SHRINKFILE statements.

You should regularly monitor the size and amount of activity occurring in your transaction log in order to ensure that the transaction log does not run out of space. The SQL Server Performance Monitor provides objects for monitoring the transaction log size and activity.

After this lesson, you will be able to

• Describe options that can be set for a database
• Grow or shrink a database
• Monitor the size of the transaction log
• Grow or shrink database files

Estimated lesson time: 75 minutes

Database Options

After you have created a database, you can view information about the database and change various database options.

Database options determine the characteristics of a database. For example, you can make a database read-only or specify that log entries be removed from the transaction log each time a checkpoint occurs.

Viewing Database Information

You can use SQL Server Enterprise Manager and Transact-SQL to get information about databases.

• To view database information using SQL Server Enterprise Manager

1. Expand your server group, and then expand your server.
2. Expand Databases, and then click the sample_ssem database.
3. In the details pane, click the Space Allocated tab to view database and transaction log space information.
4. In the Console tree, click the Northwind database.
5. In the details pane, click the Tables & Indexes tab to view table and index space information.

The following table lists commonly used system stored procedures that display information about databases and database options.

System stored procedures	Description
Sp_dboption	Lists all available options.
Sp_helpdb	Reports on all databases on a server. Provides database name, size, owner, ID, creation date, and options.
Sp_helpdbdatabase_name	Reports on a specified database only. Provides database name, size, owner, ID, creation date, and options. Additionally, reports details about each data and log file.
Sp_spaceused [objname]	Summarizes the storage space used by the current database or by a table in the current database.

NOTE

---

The size reported by sp_helpdb and sp_spaceused is the total current size of the database including the size of the log files. To determine the size of the data in the database, subtract the size of the log files from the size of the database.

Exercise: Viewing Information About Databases Using Transact SQL Statements

In this exercise, you will use system stored procedures to view information about previously created databases. You will find the script for this exercise in C:\Sqladmin\Exercise\Ch05\Dbinfo.sql.

• To view information about databases using Transact-SQL statements

1. Open or switch to SQL Server Query Analyzer.
2. Execute the following system stored procedure to generate a list of all databases.

EXEC sp_helpdb

3. Execute the following system stored procedure to display information about the sample_ssem database.

EXEC sp_helpdb sample_ssem

4. Execute the following system stored procedure to display information about use of space in the sample_ssem database.

USE sample_ssem EXEC sp_spaceused

5. Execute the following system stored procedure to display information about space usage for the authors table in the pubs database.

USE pubs EXEC sp_spaceused authors

Setting Database Options

Most database options can be set using SQL Server Enterprise Manager. All database options can be set using the sp_dboption system stored procedure. You can configure database options for only one database at a time. If you want the options applied to future databases, change the model database.

The following table lists some of the more frequently used options:

Database option	Description
dbo use only	Limits use of the database to the database owner only—typically used during development.
read only	Defines a database as read-only—typically used to set security for decision-support databases.
Select into/bulk copy	Allows a database to accept non-logged operations—used during bulk copying of data or when using SELECT INTO to conserve transaction log space.
single user	Restricts database access to one user at a time—used when performing maintenance.
trunc. log on chkpt.	Causes the transaction log to be truncated (committed transactions are removed) every time the checkpoint process occurs—used during development to conserve transaction log space. Caution: If you set this option, you will need to perform full database backups to ensure recovery in the event of a server or media failure. Since this option negates the usefulness of transaction log backups, it is seldom enabled in a production database.
autoshrink	Determines whether the database size shrinks automatically.

Exercise: Viewing and Changing Database Options Using Transact-SQL Statements

In this exercise, you will use the sp_dboption system stored procedure to view and change database options. You will find the script for this exercise in C:\Sqladmin \Exercise\Ch05\Dboption.sql. To view and change database options using Transact-SQL statements

1. Switch to SQL Server Query Analyzer (if necessary).
2. Execute the sp_dboption system stored procedure to view a list of database options:

EXEC sp_dboption

3. Execute the sp_dboption system stored procedure to view a list of database options that are enabled for the sample_ssem database (an empty list is the correct result):

EXEC sp_dboption sample_ssem

4. Execute the sp_dboption system stored procedure to have the sample_ssem transaction log truncated whenever a checkpoint occurs:

EXEC sp_dboption sample_ssem, 'trunc. log on chkpt.', 'true'

5. Execute the sp_dboption system stored procedure to verify that the transaction log of the sample_ssem database will be truncated whenever a checkpoint occurs:

EXEC sp_dboption sample_ssem

Managing Data and Log File Growth

When your database grows, or when data modification activity increases, you may need to expand the size of the data or log files. You can control the size of a database by

• Configuring the data and log files to grow automatically
• Manually increasing the current or maximum size of existing data and log files
• Manually adding secondary data files or log files

Using Automatic File Growth

Using the ALTER DATABASE statement or SQL Server Enterprise Manager, you can opt to have database files expand automatically by a specified amount whenever necessary. The automatic file growth option reduces the administrative tasks involved in database size management and also reduces the possibility of a database running out of space unexpectedly.

This option allows you to specify the initial size, maximum size, and growth increment of each file. If you do not specify a maximum size, a file can continue to grow until it uses all available space on the disk.

The syntax for altering a database is as follows:

ALTER DATABASE database {    ADD FILE <filespec> [TO FILEGROUP filegroup][FOR RESTORE]     | ADD LOG FILE <filespec>     | DROP FILE logical_file      | CREATE FILEGROUP filegroup_name     | DROP FILEGROUP filegroup     | MODIFY FILE <filespec> } <filespec> ::= (NAME = 'logical_file_name' [, FILENAME = 'os_file_name' ] [, SIZE = size] [, MAXSIZE = { max_size | UNLIMITED } ] [, FILEGROWTH = growth_increment] ) 

The MODIFY FILE Option

The MODIFY FILE option allows you to change options for any existing file. In the file specification (filespec) for MODIFY FILE, specify only the name and the option you want to change. You can change only one option at a time; to change more than one option, execute multiple ALTER DATABASE statements. You may not specify the filename.

Expanding Database Files

If an existing file is not configured to grow automatically, you can still increase its size. If you increase the size setting beyond the file's current maximum size without increasing the MAXSIZE statement, the maximum size will be set equal to the new size. A value of 0 for the growth increment (FILEGROWTH) indicates that it does not grow automatically.

The SIZE option sets the minimum size of a file. The file can grow larger but cannot shrink smaller than its designated size. You cannot reduce the file size using the ALTER DATABASE statement. To reduce the minimum size of a file, use the DBCC SHRINKFILE statement.

Adding Secondary Data Files or Log Files

Another way to expand the size of a database is to create secondary data files. The maximum size of a single data file is 32 terabytes, and the maximum size of a single log file is 4 terabytes. It is unlikely that you will need to add files due to insufficient space. Rather, use secondary data files or log files to make use of separate physical disks when you do not use the disk-striping capabilities of RAID systems.

Example: Increasing File Size and Adding a Secondary Data File

The following example increases the current data file size and adds a secondary data file to the sample database.

ALTER DATABASE sample MODIFY FILE ( NAME = 'sample_data', SIZE = 20MB) GO ALTER DATABASE sample ADD FILE (NAME = 'sample_data2' , FILENAME='c:\mssql7\data\sample2.ndf', SIZE=10MB , MAXSIZE=20MB) GO 

Exercise: Modifying a Database Using SQL Server Enterprise Manager

In this exercise, you will add a data file to the sample_ssem database and change the maximum size specification of a data file.

• To modify a database using SQL Server Enterprise Manager

1. Expand your server group, and then expand your server.
2. Expand Databases, right-click the sample_ssem database, and then click Properties.
3. In the File Name column, click the next empty row and enter the filename sample_ssem_data2. This is the data file that will contain the additional space. Note that the file location is generated automatically and given the .NDF extension.
4. Change the Space Allocated column value to 2 MB. This is the initial size of the file.
5. To specify that the file should grow by fixed increments, change File Growth to In Megabytes, and change the value to 2.
6. To allow the file to grow as more data space is needed, leave Maximum File Size set to Unrestricted Filegrowth.
7. Click OK to accept your changes and have them applied to the database.

Expanding a Transaction Log

If your transaction log runs out of space, SQL Server cannot record transactions and does not allow changes to your database. When a database grows, or when data modification activity increases, you may need to expand the transaction log.

Monitoring the Log

Monitoring the log helps you determine when it is necessary to expand it. You can monitor the transaction log manually with either SQL Server Enterprise Manager or Microsoft Windows NT Performance Monitor.

SQL Server adds a number of objects and counters to the Windows NT Performance Monitor. Use the Percent Log Used counter of the SQL Server: Database Manager object to monitor the amount of space currently in use in the transaction log of each database. The following table lists the SQL Server: Log Manager object counters you can use to monitor advanced performance statistics for the transaction logs of individual databases.

SQL Server: Log Manager object counter	Displays
Log Bytes Per Flush	Number of bytes in the log buffer when the buffer is flushed
Log Flushes	Number of log flushes
Log Flush Wait Time	Total wait time (in milliseconds)
Log Flush Waits	Number of commits that are waiting on log flush

Expanding the Transaction Log Manually

If the log is not configured to grow automatically, you can still expand the transaction log manually with either SQL Server Enterprise Manager or the ALTER DATABASE statement.

Handling Increased Log Activity

Situations that produce increased transaction log activity include

• Performing a logged bulk data load into a table that has indexes (all inserts, index changes, and extent allocations are logged).

TIP

---

Under certain conditions, it is possible to have non-logged inserts when performing bulk data loads using the bcp utility and the SELECT INTO/bulk copy database option. In this case, if you drop indexes before bulk loading, only extent allocations are logged.