With SQL Server 2005, you can manage database and log size either automatically or manually. You can use SQL Server Management Studio or Transact-SQL to configure database or log size. This section looks primarily at configuration through SQL Server Management Studio.
To configure automatic management of database and log size in SQL Server Management Studio, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server, and then work your way down to the Databases folder.
Right-click the database you want to configure, and then select Properties from the shortcut menu.
Select Files from the Select A Page list in the Database Properties dialog box. Each data and log file associated with the database is listed under Database Files. For each data and log file, do the following:
Click the button to the right of the file’s Autogrowth box to adjust the related settings. This will display the Change Autogrowth For… dialog box.
Set the file to grow using a percentage or an amount in megabytes, and then either restrict the maximum file growth to a specific size or allow unrestricted file growth.
Click OK.
Optionally, access the Options page and select the Auto Shrink check box. Auto Shrink compacts and shrinks the database periodically.
Click OK when you have finished. Your changes take effect immediately without restarting the server.
Note
See the section titled "Creating Databases in SQL Server Management Studio" earlier in this chapter for tips and advice on sizing databases and transaction logs.
Sometimes you may want to increase the size of a database or log file manually. You can do this by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server, and then work your way down to the Databases folder.
Right-click the database you want to configure, and then select Properties from the shortcut menu.
Select Files from the Select A Page list in the Database Properties dialog box. Each data and log file associated with the database is listed under Database Files.
To expand a data file, click in the related Initial Size box, and then enter a larger file size. (You can also create and size a new secondary file for the database. The advantage provided by using a new file rather than an existing file is that SQL Server does not need to lock what may be an active database file in order to expand the database.)
To expand a log file, click in the appropriate Initial Size box, and then enter a larger file size in the text box that becomes available. (You can also create and size a new transaction log file.)
Tip
With data and log files, the new file size must be larger than the current size. If it is not, you will get an error. The reason for this is that shrinking the database is handled in a different way. See the following subsection, "Compressing and Shrinking a Database Manually,’ for details.
Click OK to make the changes. SQL Server locks the database while expanding it, which blocks access.
Tip
You can add files using Transact-SQL as well. The command you use is ALTER DATABASE. For more information about using this command, see the section titled "Altering Databases and Their Options earlier in this chapter.
Compressing and shrinking a database is a bit different from expanding it, and in many cases, you will want finer control over the process than you get with the Auto Shrink option. Fortunately, you can manage this process manually, and you can also schedule this process on a recurring basis.
To compress or shrink all database files (both data and log files) manually in SQL Server Management Studio, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server, and then work your way down to the Databases folder.
Right-click the database you want to configure. Select Tasks from the shortcut menu, then choose Shrink, and then choose Database to display the Shrink Database dialog box shown in Figure 7-5.
The Database Size area in the dialog box shows the total amount of space allocated to all database files and the amount of free space. Use this information to decide if you really want to shrink the database.
To reorganize data pages and move them to the beginning of the data files, select Reorganize Files Before Releasing Unused Space. This compresses the data pages but does not remove empty data pages.
Note
Selecting the option Reorganize Files Before Releasing Unused Space performs the same task accomplished by using DBCC SHRINKDATABASE and specifying the amount of free space that you want left in the database after shrinking. If you clear the check box for this option, the database files are compressed in the same way as when you use DBCC SHRINKDATABASE with TRUNCATEONLY, which means that the file size is reduced without moving any data or reallocating rows to unallocated pages.
Log files are not reduced in size immediately. Instead, the size is reduced when the transaction log is backed up or the log is truncated, whichever occurs first. Also, you normally cannot shrink a database smaller than the model database (which is the database template).
Set the percentage of free space in the database in the Maximum Free Space In Files After Shrinking box. To squeeze all the extra space out of the database, use a value of 0 percent, but be aware that the next write operation may cause the database to grow automatically.
Click OK to begin or continue on to step 7 to schedule shrinking the database on a recurring basis. SQL Server locks the database while shrinking it, which blocks access.
The property settings you make in this dialog box are saved and are unique to the current database. If you want to use these properties to shrink the database on a recurring basis, click Schedule on the toolbar in the dialog box. You can now schedule this task as explained in Chapter 15.
To compress or shrink individual database files manually in SQL Server Management Studio, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server, and then work your way down to the Databases folder.
Right-click the database you want to configure. Select Tasks from the shortcut menu, then choose Shrink, and then choose Files to display the Shrink File dialog box shown in Figure 7-6.
Use the File Type, Filegroup, and File Name selection menus to choose the data or log file that you want to shrink. When you select a specific file, the total amount of space allocated and the amount of free space are shown. Use this information to decide if you really want to shrink the file.
Choose a shrink action:
Release Unused Space. Truncates free space from the end of the file. Unused space is released and the file is reduced in size to the last allocated extent. The file size is reduced without moving any data or reallocating rows to unallocated pages. You can accomplish the same task by using DBCC SHRINKDATABASE with TRUNCATEONLY and specifying the target file.
Reorganize Pages Before Releasing Unused Space. Reorganizes data pages and moves them to the beginning of the data files. This compresses the data pages but does not remove empty data pages. You can accomplish the same task by using DBCC SHRINKDATABASE and specifying the amount of free space that you want left in a target file after shrinking. After you select this option, set the file size by selecting a value in the Shrink To File box. The size cannot be less than the current allocated space or more than the total extents allocated.
Empty File by Migrating the Data... Migrates the data in this file to other files in the same filegroup. This option is equivalent to executing DBCC SHRINKFILE with the EMPTYFILE option, and it allows the file to be dropped later using the ALTER DATABASE command.
If you want to use these properties to shrink the data or log file later, select Shrink The File Later, and then select a date and time.
Click OK.
Another way to shrink a database is to use Transact-SQL. Two commands are provided, as shown in Example 7-3.
Example 7-3. DBCC SHRINKDATABASE and DBCC SHRINKFILE Syntax
DBCC SHRINKDATABASE Syntax
( "database_name" | database_id | 0
[ ,target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ] )
[ WITH NO_INFOMSGS ]DBCC SHRINKFILE Syntax
( { " file_name " | file_id }
{ [ , EMPTYFILE]
| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
} )
[ WITH NO_INFOMSGS ]You use DBCC SHRINKDATABASE to shrink all data files in the database and DBCC SHRINKFILE to shrink a specific data file. By default, these commands also compress the database. You can override this option with TRUNCATEONLY or specify that you only want to compress the database with NOTRUNCATE. To suppress informational messages, use WITH NO_INFOMSGS.
The following command compresses and then shrinks the Customer database to 30 percent free space:
DBCC SHRINKDATABASE ( Customer, 30 )
The following commands compress and then shrink an individual file in the Customer database to 5 MB free space:
USE Customer DBCC SHRINKFILE ( Customer_Data, 5 )
Note
The DBCC SHRINKFILE command is the only method you can use to shrink individual data and log files to make them smaller than their original size. With DBCC SHRINKFILE, you must shrink each file individually, rather than trying to shrink the entire database. Additionally, the truncation options for DBCC SHRINKDATABASE and DBCC SHRINKFILE only apply to data files; they are ignored for log files. You cannot truncate transaction logs with these commands.