How to increase the disk space of database in SQL 2005 Server?

How can I incre开发者_JAVA百科ase my disk space of database in SQL Server 2005?

---

To increase database file size: ALTER DATABASE database MODIFY FILE (NAME = logical_file_name, SIZE = size_in_MB, <other options>). For example:

ALTER DATABASE mydb MODIFY FILE (NAME = mydb_data, SIZE = 5000)

<other options> you may want to consider include MAXSIZE (to limit the top-end of the filesize) and FILEGROWTH (how much to grow the file when more disk space is needed for the database, up to a maximum of MAXSIZE).

Conversely, to decrease database file size: DBCC SHRINKFILE(logical_file_name, size_in_mb). For example:

DBCC SHRINKFILE(data_live, 50)
DBCC SHRINKFILE(data_live_log, 10)

Note that it's not possible to SHRINKFILE to less than the database actually needs to be (e.g., if you've got 500MB of data, you can't SHRINKFILE to 400 MB).

In both cases, if you do not know the logical file name of the database file, then you can query sys.sysfiles for the database of interest, and extract the 'name' column:

SELECT * FROM <database>.sys.sysfiles

Hope that helps.

---

Alternatively you can enable AUTOGROWTH

USE master
GO
ALTER DATABASE db_name
MODIFY FILE
(NAME = db_name,
FILEGROWTH = 10MB)