Microsoft SQL Server 7.0 System Administration Training Kit
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
- Expand your server group, and then expand your server.
- Expand Databases, and then click the sample_ssem database.
- In the details pane, click the Space Allocated tab to view database and transaction log space information.
- In the Console tree, click the Northwind database.
- 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
- Open or switch to SQL Server Query Analyzer.
- Execute the following system stored procedure to generate a list of all databases.
- Execute the following system stored procedure to display information about the sample_ssem database.
- Execute the following system stored procedure to display information about use of space in the sample_ssem database.
- Execute the following system stored procedure to display information about space usage for the authors table in the pubs database.
| EXEC sp_helpdb |
| EXEC sp_helpdb sample_ssem |
| USE sample_ssem EXEC sp_spaceused |
| 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
- Switch to SQL Server Query Analyzer (if necessary).
- Execute the sp_dboption system stored procedure to view a list of database options:
- 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):
- Execute the sp_dboption system stored procedure to have the sample_ssem transaction log truncated whenever a checkpoint occurs:
- 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 |
| EXEC sp_dboption sample_ssem |
| EXEC sp_dboption sample_ssem, 'trunc. log on chkpt.', 'true' |
| 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
- Expand your server group, and then expand your server.
- Expand Databases, right-click the sample_ssem database, and then click Properties.
- 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.
- Change the Space Allocated column value to 2 MB. This is the initial size of the file.
- To specify that the file should grow by fixed increments, change File Growth to In Megabytes, and change the value to 2.
- To allow the file to grow as more data space is needed, leave Maximum File Size set to Unrestricted Filegrowth.
- 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.
- Transactions that perform many modifications (INSERT, UPDATE, and DELETE statements) to a table within a single transaction. This typically occurs when the statement lacks a WHERE clause, or when the WHERE clause is too general, causing a large number of records to be affected.
- Text or image data in a table being added to or modified using the UPDATE statement. These files are typically large and can cause the transaction log to fill quickly. To avoid this situation, use the WRITETEXT or UPDATETEXT statements. If used correctly, these statements perform non-logged text and image updates to conserve transaction log space.
NOTE
Increased activity can dramatically increase the size of the transaction log. Once increased, space can be freed up quickly by backing up or truncating the log, but it is difficult to reduce the log back to its original size. Log shrinking is a deferred operation that cannot be forced to occur.
Example: Increasing the Log File Size
The following example increases the current log file size for the sample database.
| ALTER DATABASE sample MODIFY FILE ( NAME = 'sample_log', SIZE = 10MB) GO |
Exercise: Using Transact-SQL to Increase the Size of the Transaction Log
In this exercise, you will increase the maximum size of the log file to 20 MB for the sample_ssem database. You will find the script for this exercise in C:\Sqladmin\Exercise\Ch05\Modismpl.sql.
- To increase the maximum size of the transaction log using Transact-SQL statements
- Switch to SQL Server Query Analyzer.
- Execute the following ALTER DATABASE statement to change the maximum size of the sample_ssem database log file to 20 MB:
- View the database properties in SQL Server Enterprise Manager or use sp_helpdb to verify that the database has been properly modified.
| ALTER DATABASE sample_ssem MODIFY FILE (NAME = 'sample_ssem_log', MAXSIZE=20MB) |
NOTE
SQL Server Enterprise Manager does not automatically refresh information that has not been changed in SQL Server Enterprise Manager. To keep your information current, use the Refresh option on the various folders often.
Shrinking a Database or a File
When too much space has been allocated, or space is no longer needed, you can either shrink the entire database or shrink specific data files in the database.
There are three ways to shrink a database:
- Remove free space from database files, using the DBCC SHRINKDATABASE statement. When removing free space, you cannot shrink the size of a file below its minimum size (the SIZE specified in the CREATE DATABASE or ALTER DATABASE statements used to create or modify the file).
- Reduce the minimum size of database files, using the DBCC SHRINKFILE statement. You can also use DBCC SHRINKFILE to empty files so that they can be removed with the ALTER DATABASE statement.
- Set the database to shrink automatically.
Shrinking an Entire Database
You can shrink an entire database by using SQL Server Enterprise Manager or by executing the Database Consistency Checker (DBCC) statement, SHRINKDATABASE. This statement shrinks the size of all data files in the database. Log files are shrunk using a deferred shrink operation, which will occur some time after the log has been backed up or truncated. You cannot force the log files to shrink, even after backing up or truncating the log.
The syntax for the DBCC SHRINKDATABASE command is as follows:
| DBCC SHRINKDATABASE (database_name [, target_percent] [, {NOTRUNCATE | TRUNCATEONLY]) |
The target_percent Option
The target_percent option specifies the percentage of free space to be left in the data files after the database has been shrunk. Database files will not shrink below their original size even if this means that the target_percent is not achieved.
Using target_percent, with or without the NOTRUNCATE option, causes used pages to be relocated from the end of the files to the front of the files. The freed space either goes to the operating system (the default) or remains in the file (if NOTRUNCATE is specified). Using the TRUNCATEONLY option causes space at the end of the files to be released to the operating system without moving any pages. The target_percent option is ignored when the TRUNCATEONLY option is used.
The following example sets a target of 25 percent free space for the database, using the statement
| DBCC SHRINKDATABASE (sample, 25) |
The following table shows the results when this target is achieved.
| Data file | Original size | Current size | Space used | Size after shrinking | Percentage free |
|---|---|---|---|---|---|
| Sample_data | 20 MB | 30 MB | 15 MB | 20 MB | 25 % |
| Sample_data2 | 10 MB | 15 MB | 9 MB | 12 MB | 25 % |
| Total | 30 MB | 45 MB | 24 MB | 32 MB | 25 % |
The next table shows the results when the target is not achieved because doing so would require shrinking the files smaller than their original sizes. These results do not show log files, as they would not be affected.
| Data file | Original size | Current size | Space used | Size after shrinking | Percentage free |
|---|---|---|---|---|---|
| Sample_data | 20 MB | 30 MB | 12 MB | 20 MB | 40 % |
| Sample_data2 | 10 MB | 15 MB | 3 MB | 10 MB | 70 % |
| Total | 30 MB | 45 MB | 15 MB | 30 MB | 50 % |
Exercise: Shrinking a Database
In this exercise, you will use the DBCC SHRINKDATABASE statement to reduce the size of the sample_ssem database so that it contains only 25 percent of the current available space. You will find the script for this exercise in C:\Sqladmin\Exercise\Ch05 \Shrinkdb.sql.
- To shrink a database
- Switch to SQL Server Query Analyzer.
- Execute the following statement to reduce the size of the sample_ssem database to contain only 25 percent free space.
| DBCC SHRINKDATABASE (sample_ssem, 25) |
NOTE
You will not see any changes to the database, because the files are still their original size, and shrinking a database will not decrease files below their original size.
Shrinking a Data File in the Database
You can shrink a data file in a database either by using SQL Server Enterprise Manager or by executing the DBCC SHRINKFILE statement (shown in the following example).
| DBCC SHRINKFILE ({file_name | file_id} [, target_size] [, { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY}]) |
These are the only ways to reduce a file to less than its original size.
The target_size Option
The target_size option specifies the size of the data file in megabytes, expressed as an integer. If target_size is not specified, or the target_size is less than the amount of space in use, DBCC SHRINKFILE reduces the size as much as possible.
The EMPTYFILE Option
The EMPTYFILE option migrates all data from the specified file to other files in the same filegroup. Once the file is emptied, SQL Server no longer allows data to be placed in the file. The empty file is dropped using the ALTER DATABASE statement with the REMOVE FILE option.
Example: Shrinking a Data File
This example shrinks the size of the sample_data data file of the sample database to 10 MB:
| DBCC SHRINKFILE (sample_data, 10) |
Exercise: Shrinking a Database File
In this exercise, you will use the DBCC SHRINKFILE statement to reduce the size of the secondary database file in the sample_ssem database to 1 MB. You will find the script for this exercise in C:\Sqladmin\Exercise\Ch05\Shrinkfl.sql.
- To shrink a database file
- Switch to SQL Server Query Analyzer.
- Execute the following statement to reduce the size of the sample_ssem_data2 database file to 1 MB. Make sure that you release the freed space to the operating system.
- View the database properties to verify that the database file size has been reduced to 1 MB.
| USE sample_ssem DBCC SHRINKFILE (sample_ssem_data2, 1) |
Shrinking a Database Automatically
You can also set a database option to recover unused space automatically by setting the database autoshrink option to true. This option can also be changed with SQL Server Enterprise Manager or sp_dboption.
Consider the following guidelines before you shrink a database or a data file:
- The resulting database must be larger than the model database and large enough to hold the existing data.
- Before you shrink a database or a data file, back up both the database and the master database.
- The DBCC SHRINKDATABASE and SHRINKFILE statements execute on a deferred basis, so you may not see the size reduction immediately.
Lesson Summary
When data modification activity increases or when data files grow, the size of the data and log files may need to be expanded. SQL Server 7 provides a way for these files to grow automatically. To manually increase or decrease the size of database files, use the ALTER DATABASE statement and the DBCC SHRINKDATABASE statement.
It is good practice to monitor the activity of your transaction log. The SQL Server Performance Monitor provides an excellent tool to do this.