Expanding the Database

Microsoft® SQL Server™ can expand a database automatically, according to growth parameters defined when the database was created. You can also manually expand a database by allocating additional file space on an existing database file or allocating space on another new file. You may need to expand the data or transaction log space if the existing files are becoming full. If a database has already exhausted the space allocated to it and it cannot grow automatically, error 1105 is raised.

When expanding a database, you must increase the size of the database by at least 1 megabyte (MB). Permission for expanding a database defaults to the database owner and is automatically transferred with database ownership. When a database is expanded, the new space is made immediately available to either the data or transaction log file, depending on which file was expanded.

If the transaction log is not set up to expand automatically, it can run out of space if certain types of activity occur in the database. The transaction log is purged only of inactive (committed) transactions when it is backed up, or at each checkpoint when the trunc. log on chkpt. database option is set to true. SQL Server can then reuse this truncated, unused portion of the transaction log. For more information about truncating the transaction log, see Truncating the Transaction Log in Creating and Applying Transaction Log Backups.

SQL Server does not truncate the transaction log when backing up the database.

When expanding a database, it is recommended that you specify a maximum size to which the file is permitted to grow. This prevents the file from growing until disk space is exhausted. To specify a maximum size for the file, use the MAXSIZE parameter of the ALTER DATABASE statement or the Restrict filegrowth (MB) option when using a property page within SQL Server Enterprise Manager to expand the database.

Expanding a database to increase space for data or the transaction log follows exactly the same process.

Expanding tempdb

By default, the tempdb database automatically grows as space is needed because the MAXSIZE of the files is set to UNLIMITED. Therefore, tempdb can continue growing until space on the disk that contains tempdb is exhausted. To prevent tempdb from growing without limits, set a MAXSIZE for tempdb by using the ALTER DATABASE statement or SQL Server Enterprise Manager.

Conversely, if tempdb has been set at a MAXSIZE, and you want to increase the size of tempdb, you must do one of the following:

• Increase the size of the files in the default filegroup currently used by tempdb.
• Add a new file to the default filegroup.
• Allow the files used by tempdb to grow automatically.

Important User-defined filegroups cannot be used with tempdb. They can be used only with the default filegroup.

Moving tempdb

To change the physical location of the tempdb database:

1. Alter the tempdb database, using the ALTER DATABASE statement and MODIFY FILE clause, to change the physical file names of each file in tempdb to reference the new physical location, such as the new disk.
2. Stop and restart SQL Server.
3. Delete the old tempdb database files from the original location.

To increase the size of a database

To change the physical location of a database

See Also