Managing Databases

After the database is created, you are left with the ongoing task of managing it. At the database level, this generally involves manipulating the file structure and setting options appropriate to the usage of the database.

Managing File Growth

As we saw in the previous section on creating databases, SQL Server manages file growth by automatically growing files by preset intervals when a need for additional space arises. I find this, however, to be a very loose definition of the word manages . What actually happens is that when the database runs out of space, it suspends all update activity, checks if it is allowed additional space and if space is available, and then increases the file size by the value defined by FILEGROWTH . When it fills up again, the whole process starts over.

When all the files in a filegroup are full and they are configured to autogrow, SQL Server automatically expands one file at a time in a round- robin fashion to accommodate more data. For example, if a filegroup consists of multiple files and no free space is available in any file in the file group, the first file is expanded by the specified file growth setting. When the first file is full again, and there is no more free space elsewhere in the file group, the second file is expanded. When the second file is full, and there is no more free space elsewhere in the file group , the third file is expanded, and so on.

Because FILEGROWTH can be defined as small as 64KB, automatically increasing the file size can be detrimental to performance if it happens too frequently. When I think of managing file growth, I think of the database administrator proactively monitoring the size of files and increasing the size before SQL Server runs out of space to allocate new extents. That's not to say automatic file growth is a bad thing; it is, in fact, a great "safety valve" to accommodate unpredictable data growth or a lack of attention on the part of the administrator.

Expanding Databases

As previously discussed, databases can be expanded automatically, or you can intervene and expand them manually. There are two methods of increasing the size of a database: by increasing the size of existing files and by adding additional files. To increase the size of a file or add an additional file using T-SQL, use the ALTER DATABASE command. The SQL script shown in Listing 11.2 increases the size of an existing file to 20MB and then adds a new file.

Listing 11.2 Using T-SQL to Alter a Database to Increase the Size of a File and Add a New Data File

 ALTER DATABASE Big_DB MODIFY FILE    (NAME = Big_DB_Dat,    SIZE = 20MB) GO ALTER DATABASE Big_DB ADD FILE (  NAME = Big_DB_Data_Dat2,  FILENAME = 'e:\data\Big_DB_Data2.ndf',  SIZE = 50MB,  MAXSIZE = 100MB,  FILEGROWTH = 5MB GO 

Enterprise Manager can also be used to increase the size of a file or add a new one. Access the Properties page of the database you want to modify and select the Data Files tab. In Figure 11.5, the CreditTables file is increased to 100MB, and a new file CreditTables2 is being added.

Figure 11.5. Increasing the database size in Enterprise Manager.

Shrinking Databases

So far this chapter has looked at expanding the database, but what if you want to make it smaller? SQL Server does provide an AUTOSHRINK option, which detects free space in the data files and attempts to shrink them at 30-minute intervals, leaving 25 percent free space. The problem that occurs is that the files are shrunk working back from the end of the files, and data pages might be allocated in the "upper" end of the files even though excess free space exists elsewhere in the files. When this occurs, the file cannot be shrunk until the pages are reorganized to the "front" of the file. If you have ever done an operating system disk defrag, you can picture the kind of overhead this could involve. With the exception of single- user databases running on client machines where no one manages data files, I would suggest leaving this option off, as it has been known to negatively impact the performance of production databases.

However, if you want to shrink the database manually, you can do so using the DBCC SHRINKDATABASE and DBCC SHRINKDATAFILE statements, as well as by using Enterprise Manager.

NOTE

Unless there is a definite need for the space that would be made available by shrinking a database, it is recommended that you do not shrink the database files if the space will be used again in the future. For one thing, having free space available in the database files avoids the overhead of having to autogrow the database files when they become full. Also, if a database file is repeatedly shrunk and expanded, the database file itself can become fragmented within the file system, which can degrade IO performance for the file.

DBCC SHRINKDATABASE

The DBCC SHRINKDATABASE statement attempts to shrink the entire database (all files) and leave a specified target percentage of free space, as shown in this statement:

 DBCC SHRINKDATABASE (credit, 25) 

This would attempt to shrink the credit database, leaving 25 percent free space in each file. This operation is done on a file-by-file basis. If a particular file has less than the specified amount of free space available, it is left unchanged and not shrunk. The DBCC SHRINKDATABASE command cannot shrink a file below the original size specified when it was created. This limitation can be overcome using DBCC SHRINKFILE , which can set a new minimum size for the file. Following is the full syntax for DBCC SHRINKDATABASE :

 DBCC SHRINKDATABASE  (   database_name  [ ,  target_percent  ]          [ , { NOTRUNCATE  TRUNCATEONLY } ] ) 

If the NOTRUNCATE option is specified, SQL Server retains the freed space in the files. The data is compacted to the front of the file, but the file size remains unchanged. This could be useful if there have been too many deleted records, causing fragmentation; however, over time you expect the number of records to increase again to the previous size. A database where records are purged, perhaps on a yearly basis, might benefit from this. Keep in mind that this operation might have extremely high overhead in a large or extremely fragmented database.

The TRUNCATE ONLY option is quite the opposite . This specifies that the data should not be compacted, but that unused space above the last allocated extent (referred to as the "high water mark") be released. The target_percentage is ignored with this option. Use this option if your data growth has stabilized, but you have too much excess space allocated in the files.

DBCC SHRINKFILE

The DBCC SHRINKFILE statement affords more control over file size because it shrinks individual files. DBCC SHRINKFILE is also useful in that it can get around the SHRINKDATABASE option's inability to shrink a file below its minimum specified size. This is handy if you originally allocated too much space to a file. Following is the full syntax for DBCC SHRINKFILE :

 DBCC SHRINKFILE  ( { file_name  file_id } { [ , target_size ]               [ , { EMPTYFILE  NOTRUNCATE  TRUNCATEONLY } ] } 

Note that with this option, a filename or ID is supplied, rather than the database name. DBCC SHRINKFILE must be run in the database that the file belongs to. The TARGET_SIZE is specified in megabytes and is the desired size for the file; if not specified, it tries to shrink the file as much as possible. The EMPTYFILE option migrates all data in the file to other files in the same filegroup. No further data can be placed on the file. The file can subsequently be dropped from the database. This can be useful to migrate a data file to a new disk. The NOTRUNCATE and TRUNCATEONLY options are functionally the same as for DBCC SHRINKDATAFILE and are outlined in the previous section.

Shrinking the Log File

The data file that is most likely to grow beyond a normal size and require shrinking periodically is the transaction log. If a user process issues a large update transaction, the log file will grow to the size needed to hold the records generated by the transaction. This could be significantly larger than the normal growth of the transaction log.

As with data files, shrinking of the log file in SQL Server 2000 can only take place from the end of the log file. However, you must first back up or truncate the log to remove the inactive log records and reduce the size of the logical log. You can then run the DBCC SHRINKFILE or DBCC SHRINKDATABASE command to release the unused space in the log file.

Transaction log files are divided logically into smaller segments called virtual log files. Transaction log files can only be shrunk to a virtual log file boundary. Because of this, it is not possible to shrink a log file to a size smaller than the size of a virtual log file, even if the space is not being used. The size of the virtual log files in a transaction log increase as the size of the log file increases. For example, a database defined with a log file of 1GB will have virtual log files 128MB in size. Therefore, the log can only be shrunk to about 128MB.

Because of the overhead incurred when the autoshrink process attempts to shrink database files, it is not recommended that this option be enabled for the transaction log as it can be triggered numerous times during the course of a business day. It is better to schedule the shrinking of the log file to be performed during normal daily maintenance when production system activity is at a minimum.

Shrinking a Database with Enterprise Manager

New for SQL Server 2000 is the ability to shrink a database or file from Enterprise Manager. Right-clicking a database, selecting All Tasks, Shrink Database will bring up the Shrink Database dialog box. This dialog box, shown in Figure 11.6, gives you the options available with DBCC SHRINKDATABASE , as well as the ability to schedule the operation.

Figure 11.6. Shrinking the database size in Enterprise Manager.

If you want to shrink files ( DBCC SHRINKFILE ), selecting the Files button at the bottom of the page brings up the dialog box shown in Figure 11.7, allowing you to perform and schedule DBCC SHRINKFILE operations.

Figure 11.7. Shrinking file size in Enterprise Manager.