Expanding or Shrinking Databases

To expand a database is to allocate additional device space to the database. If a database grows so that it uses most or all of the space allocated for it, you can expand it.

You expand a database using SQL Enterprise Manager or the ALTER DATABASE statement. This chapter describes the use of SQL Enterprise Manager. For information about using ALTER DATABASE and other statements, see the Microsoft SQL Server Transact-SQL Reference.

You can expand a database by allocating more space on its existing device or by allocating space on another device. A database can occupy more than one database device with different amounts of space on each. When expanding a database, you must increase the size of the database by at least 1 MB. Permission for expanding a database defaults to the database owner and is automatically transferred with database ownership. For information on permissions and database ownership, see Chapter 8, Security Concepts.

You cannot expand the master database on any device other than MASTER. If the master database requires more space, you can use SQL Enterprise Manager or the DISK RESIZE statement to increase the size of the MASTER device, and then expand the master database on the MASTER device. For information on expanding a device, see Chapter 5, Managing Devices.

    To expand a database

1. From the Server Manager window, select a server, and then from the toolbar, choose the Manage Databases button.

   

   The Manage Databases window appears.

2. Select a database name, and then choose the Edit Database button.

   

   The Edit Database window appears.

   

   Under Information, the Size field shows the amount of space allocated to the database, and the Space Available field shows the amount of unused allocated space.

3. Choose the Expand button.

   The Expand Database dialog box appears.

   

4. From the list in the Data Device box, select a database device on which to expand the database. Or to create a new device for the database expansion, select <new> and complete the New Database Device dialog box that appears.

   Refer to the graph at the bottom of the dialog box for a display of the devices on the server, and the available space on each.

5. In the Data Device Size box, type a size, in megabytes, to allocate to the database.

   If this is a device the database already has space on, enter the amount of additional space to allocate (not the total space). The default is the maximum space available on the selected device.

6. Choose OK.

   The Edit Database window returns.

7. Choose OK.

Note While the database is expanded, the transaction log can also be expanded. For information about expanding transaction logs, see Expanding or Moving Transaction Logs, next in this chapter.

You can also expand the tempdb database, including tempdb in ram. However, when tempdb resides in RAM, it can only be altered 10 times without requiring the server to be shut down and restarted. Altering tempdb while it is in RAM causes each alteration of the database to allocate a new "chunk" of contiguous memory to tempdb. This memory, although it is contiguous, is not necessarily located next to the existing portion(s) of tempdb in RAM. To obtain maximum performance, the server should be stopped and restarted after tempdb is altered. For information about tempdb and tempdb in ram, see the Microsoft SQL Server Transact-SQL Reference.

If a database has been allocated unused space that you would like to recapture, you can shrink the database. In order to shrink a database, the database must be in single-user mode, but SQL Enterprise Manager sets this option automatically for you while it is shrinking the database.

    To shrink a database

1. From the Server Manager window, select a server, and then from the toolbar, choose the Manage Databases button.

   

   The Manage Databases window appears.

2. Select a database name, and then choose the Edit Database button.

   

   The Edit Database window appears.

   

   Under Information, the Size field shows the amount of space allocated to the database, and the Space Available field shows the amount of unused allocated space.

3. Choose the Shrink button.

   A message appears, informing you that the database will be set to single-user mode during the shrink operation, and asking whether or not to continue.

4. Choose Yes.

   The Shrink Database dialog box appears.

   

   There may be a delay before the minimum size is shown.

5. Enter a smaller size in the Database Size (MB) box, and then choose the OK button.

   The database is set to single-user mode, the database is shrunk, and then the single-user setting is cleared. The Edit Database window returns.

6. Choose OK.

You can also reduce the allocated space using DBCC SHRINKDB. To shrink the master or tempdb databases you must start the server in single-user mode (use the sqlservr command-line executable with the -m parameter) and use the DBCC SHRINKDB command. For information, see the Microsoft SQL Server Transact-SQL Reference.

Note After you expand or shrink a database, back up the master database.