DBAtlas · Database operations atlas

Change file size / autogrowth

SQL ServerStorage

Command

-- Grow (SIZE only grows; shrinking requires shrink, not a resize):
ALTER DATABASE [Customer]
MODIFY FILE (NAME = N'Customer_data', SIZE = 20GB);

-- Set autogrowth (fixed MB recommended):
ALTER DATABASE [Customer]
MODIFY FILE (NAME = N'Customer_data', FILEGROWTH = 1GB, MAXSIZE = 200GB);

-- Disable autogrowth:
ALTER DATABASE [Customer]
MODIFY FILE (NAME = N'Customer_log', FILEGROWTH = 0);

What it does

Pre-grows a data/log file or sets its autogrowth increment/upper limit.

When to use it

To fix uncontrolled small growth increments, pre-allocate space, or set a MAXSIZE.

Caution

Percentage-based autogrowth produces very large increments on big files; use fixed MB. Without Instant File Initialization, data file growth blocks. SIZE cannot shrink a file (shrink is required).

Open in DBAtlas →

Related