Lesson 3: Managing User Database Size | MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228

3 4

After you create a user database, you need to manage the size of the user database. Data stored in the data files normally grows over time, and systems tend to get busier over time, resulting in transaction logs that fill more rapidly. Occasionally, you will need to reduce the size of data or transaction log files. SQL Server 2000 provides a number of mechanisms for managing database growth (and shrinkage). In this lesson you learn how to use the automatic methods provided by SQL Server 2000 appropriately. You also learn how to manually increase the size of data files and transaction log files, and how to add additional files. Finally you will learn how to shrink data files and transaction log files.

After this lesson, you will be able to

Use the autogrow capability appropriately
Use the autoshrink capability appropriately
Manually grow and shrink data files
Manually grow and shrink transaction log files
Add data files and transaction log files

Estimated lesson time: 30 minutes

Using Automatic File Growth Appropriately

When you create a user database, by default, each data file and transaction log file is set to grow automatically when the particular file becomes full. Autogrowth is particularly useful for embedded applications and small installations where there is no database administrator to regularly monitor free space, such as in desktop installations. This includes desktop installations. However, in a dedicated SQL Server 2000 environment, autogrowth should only be used as a safety valve because of performance issues. Each time a file must grow, your system suffers a performance hit. In addition, disk fragmentation will occur if your disk grows frequently, particularly on a drive shared with other applications and data. Rather than relying on auto-growth, you should regularly monitor your data and transaction log files and manually increase (or decrease) their size as needed at a time when the database is not busy.

Note

There is also a constant small performance hit when autogrowth is enabled because your system must continually monitor the size of each file to determine if and when it needs to grow.

In general, you should plan the size of your data and transaction log files so that autogrowth is needed rarely, if at all. If you use autogrowth, set a growth increment large enough so that growth occurs infrequently. Furthermore, you should always set a maximum size to which a file can grow so the file does not fill up the entire disk (if the file is sharing the disk with other files). To monitor file growth and available free space, you should set an alert to notify you when a file automatically grows and when free space falls below a set value. Configuring alerts is covered in Chapter 13.

Use SQL Server Enterprise Manager or the ALTER DATABASE Transact-SQL statement to modify autogrowth settings. For example, to disable autogrowth for the primary data file in the TSQLDB database, use the following statement.

 ALTER DATABASE TSQLDB MODIFY FILE ( NAME = 'tsqldb_data' , FILEGROWTH = 0 )

Note

Setting FILEGROWTH equal to zero prevents the database files from expanding beyond their initial size. When the data files fill with data, no more data can be added until the existing data files are expanded or until more data files are added to the database. When the transaction log files fill with log records, no more transactions can complete until the existing transaction log files are expanded, more transaction log files are added, or the transaction log files are backed up (and truncated).

Practice: Configuring Automatic Filegrowth Settings Using SQL Server Enterprise Manager

In this practice you configure automatic filegrowth settings using SQL Server Enterprise Manager.

To configure automatic filegrowth settings using SQL Server Enterprise Manager

In the SQL Server Enterprise Manager console tree, expand the Microsoft SQL Servers container, expand the SQL Server Group container, expand the default instance, and then expand Databases.
In the console tree, right-click Northwind and then click Properties.
The Northwind Properties dialog box appears with the General tab selected, displaying various properties of the Northwind database.
Click the Data Files tab.
Notice that the primary data file for the Northwind database is set to grow automatically in 10 percent increments. Also notice that no maximum size is set.
In the Maximum File Size group box, click the Restrict File Growth (MB): option button and then type 25 in the Restrict File Growth (MB): spin box.
Click the Transaction Log tab.
In the File Growth group box, click the In Megabytes: option button and then type 5 in the In Megabytes: spin box.
Click OK to apply these changes to the Northwind database.

Using Automatic File Shrinkage Appropriately

In addition to autogrowth, you can configure a user database to shrink automatically whenever a data file or transaction log file has a large amount of free space. By default, this database option setting is set to false (disabled). Although this option is sometimes useful for desktop installations and embedded applications, you should never set this option to true in a regular production system, for the same performance reasons discussed for autogrowth. You should plan the size of your data files and transaction log files to use an appropriate amount of space. If you need to shrink a file, you should perform that task manually at a time when your system is not busy. Also, automatically shrinking a file that will have to autogrow later is inefficient. Use SQL Server Enterprise Manager or the ALTER DATABASE Transact-SQL statement to modify autoshrink settings.

Controlling Data File Size Manually

Monitoring the amount of free space in your data files allows you to anticipate the need to increase the size of your data files. You can then perform this task at a time when your system is not busy. Use SQL Server Enterprise Manager or the ALTER DATABASE Transact-SQL statement to increase the size of a data file. For example, to set the size of the primary data file for the TSQLDB database to 15 MB, use the following statement.

 ALTER DATABASE TSQLDB MODIFY FILE ( NAME = 'tsqldb_data' , SIZE = 15 )

If you need to shrink the size of a data file manually, use SQL Server Enterprise Manager or the DBCC SHRINKFILE statement. For example, to reduce the size of the primary data file in the TSQLDB database, use the following statement to specify a target size of 7 MB.

 USE TSQLDB DBCC SHRINKFILE ( 'tsqldb_data' , 7 )

DBCC SHRINKFILE applies to the current database only. When you shrink a data file, the data file is shrunk from the end of the file. By default, all used pages in the part of the data file being shrunk are relocated to available free space toward the beginning of the data file to shrink the data file to the desired size. You can also shrink a data file to the last allocated extent without relocating pages, or you can relocate pages without shrinking the data file. You cannot shrink a data file smaller than the amount of data it contains or the size of the model database. DBCC SHRINKFILE will shrink a data file smaller than the original size. In addition, you can shrink a data file while users are working in the database, but not while the database or transaction log is being backed up.

You can use the DBCC SHRINKDATABASE to shrink an entire database, including all data files and all transaction log files, to a specified percent of the current size. Using DBCC SHRINKDATABASE, you cannot shrink a database to a size smaller than its size at creation.

Note

ALTER DATABASE cannot be used to shrink the size of a file (only to increase the size of a file).

Practice: Modifying Data File Size Using SQL Server Enterprise Manager

In this practice you modify the size of a data file using SQL Server Enterprise Manager.

To modify data file size using SQL Server Enterprise Manager

In the SQL Server Enterprise Manager console tree, expand the Microsoft SQL Servers container, expand the SQL Server Group container, expand the default instance, and then expand Databases.
In the console tree, right-click Northwind and then click Properties.
The Northwind Properties dialog box appears with the General tab selected, displaying various properties of the Northwind database.
Click the Data Files tab.
In the Space Allocated (MB) column, notice that the primary data file for the Northwind database is set to 3 MB.
In the Space Allocated (MB) cell, type 7.
Click OK to apply this change to the Northwind database.

Controlling Transaction Log File Size Manually

In addition to monitoring the amount of free space in your data files regularly, you should monitor the free space available in your transaction log files. When using the Full and Bulk-Logged Recovery models, you must perform regular transaction log backups to truncate the transaction log files to free up space for additional records. When you are using the Full Recovery model, large-scale operations and bulk load operations can fill up the transaction log quickly. If you find your transaction log files filling up more rapidly than you want, you must either perform more frequent transaction log backups, allow SQL Server 2000 to automatically grow the transaction log files whenever they run out of space (when SQL Server 2000 is already busy), or manually increase the size of your transaction log files. Use SQL Server Enterprise Manager or the ALTER DATABASE Transact-SQL statement to increase the size of a transaction log file in the same manner you increase the size of a data file.

Caution

If SQL Server 2000 runs out of transaction log space, SQL Server 2000 will stop.

If you need to shrink the size of a transaction log file manually, use SQL Server Enterprise Manager or the DBCC SHRINKFILE statement. You can use SQL Server Enterprise Manager or the DBCC SHRINKDATABASE statement to shrink all data files and transaction log files at once. Like a data file, a transaction log file is shrunk from the end of the file. The unit of shrinkage is the VLF. If the transaction log file being shrunk contains inactive VLFs at the end of the file, the transaction log file will be reduced by the size of these inactive VLFs at the end of the file to reduce the file to as close as possible to the requested size. The requested size is rounded up to the next highest virtual log file boundary. For example, if you specify a target size of 150 MB and the transaction log file size is currently 300 MB with six VLFs, the new size for this transaction log file will be 180 MB (if the VLFs at the end of the transaction log file are empty).

If there are not sufficient empty VLFs at the end of the transaction log file to free up the desired space, SQL Server 2000 frees up as much space as possible. It fills the last active VLF at the end of the file with dummy records so that the MinLSN can move to the beginning of the file (after long-running transactions and unreplicated transactions replicate). See Figure 6.15.

figure 6.15 - shrinking the transaction log by adding dummy records.

Figure 6.15

Shrinking the transaction log by adding dummy records.

SQL Server 2000 then sends an information message telling you to perform a transaction log backup to truncate the VLFs at the end of the file. After you perform this action, you perform the DBCC command again to shrink the transaction log file to your desired size. See Figure 6.16.

figure 6.16 - transaction log after shrinking.

Figure 6.16

Transaction log after shrinking.

Creating Additional Data and Transaction Log Files

If you need to create additional data files or transaction log files (either because the additional space is on a separate drive or to enhance performance by spreading database files among multiple disks), you can use SQL Server Enterprise Manager or the ALTER DATABASE Transact-SQL statement. When creating additional database files using SQL Server Enterprise Manager, you simply add the desired file in the Properties dialog box for the database. By default, all additional data files are added to the primary filegroup. Lesson 4 of this chapter covers the use of user-defined filegroups. See Figure 6.17.

The following example adds a file to the TSQLDB database using the ALTER DATABASE Transact-SQL statement.

 ALTER DATABASE TSQLDB ADD FILE (NAME = 'TSQLDB2_DATA' ,     FILENAME =      'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TSQLDB2.ndf' ,     SIZE = 10 ,     MAXSIZE = 25 ,     FILEGROWTH = 5 )

figure 6.17 - adding new database files.

Figure 6.17

Adding new database files.

Lesson Summary

Over time, your SQL Server 2000 installation will need additional space for data files and transaction log files. You can allow SQL Server 2000 to add this additional space automatically, but in most production environments, you should only use this as a safety valve. For better performance, you should monitor your data files to anticipate when they are getting low on available space and increase their size manually. This allows you to grow your database files when your system is not busy. If your transaction log files are running low on space, you can either increase their size or perform the transaction log backups more frequently. In addition to increasing the size of existing database files, you can add additional data files and transaction log files. You can also shrink database files automatically or manually. For best performance, shrink database files manually, if at all.