Expanding a Database
By default, SQL Server 2005 automatically expands a database according to growth parameters defined when the database was created. You can also manually expand a database by allocating additional space to an existing database file or by creating a new file. You may have 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. This can happen if the database is not set to grow automatically or if there is not enough disk space on the hard drive.
When expanding a database, you must increase the size of the database by at least 1 MB. When a database is expanded, the new space is immediately made available to either the data or transaction log file, depending on which file was expanded. When you expand a database, you should specify the 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 use the Restrict filegrowth (MB) option when you use the Properties dialog box in SQL Server Management Studio to expand the database. Expanding a database to increase space for the transaction log follows the same process.
If the transaction log is not set up to expand automatically, the transaction log can run out of space if certain types of activity occur in the database. For example, if the database recovery model is set to FULL, performing large-scale bulk operations, such as bulk import or index creation, can cause the transaction log to fill rapidly. In addition to expanding the size of the transaction log, the log file can be truncated. Truncating the log purges the file of inactive, committed, transactions and allows the SQL Server Database Engine to reuse this unused part of the transaction log. How and when the log is truncated depends on the recovery model of the database. For more information about truncating the transaction log, see Transaction Log Truncation.
Expanding tempdb
By default, the tempdb database automatically grows as space is required, 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.
You can prevent tempdb from growing without limits by setting a MAXSIZE for tempdb, however this is not recommended. Limiting the size of tempdb may cause the database to run out of disk space. This can cause significant disruptions in your production environment and can prohibit applications that are running from completing operations. 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
tempdb cannot have user-defined filegroups.
For more information, see tempdb Database.
To increase the size of a database
- ALTER DATABASE (Transact-SQL)
- How to: Increase the Size of a Database (SQL Server Management Studio)
To change the physical location of a database
See Also
Tasks
Troubleshooting Insufficient Disk Space in tempdb
Concepts
Using Files and Filegroups to Manage Database Growth
Optimizing tempdb Performance
Capacity Planning for tempdb