SQL SIZE

What is the SQL SIZE option used for?

SIZE defines the initial amount of storage to allocate for a database or log file.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SIZE: SQL Server (full support); Azure SQL Database (full support); Oracle (similar SIZE clause in storage options); MySQL and PostgreSQL do not use SIZE in CREATE DATABASE but offer equivalent options in tablespace creation; SQLite not supported.

SQL SIZE Full Explanation

In Transact-SQL (T-SQL) the SIZE option appears in file specifications for CREATE DATABASE and ALTER DATABASE statements. It pre-allocates the physical space (in KB, MB, GB, or TB) that the data or log file will occupy on disk. By explicitly setting SIZE, administrators can avoid frequent autogrowth events, improve performance, and ensure enough space is reserved for expected data volume. If no unit is supplied, SQL Server interprets the number in 8-KB pages (effectively kilobytes). SIZE cannot be smaller than the minimum required for the file type, and it must not exceed the available disk capacity or the MAXSIZE value (if specified).

SQL SIZE Syntax

CREATE DATABASE database_name
ON PRIMARY
( NAME = logical_file_name,
  FILENAME = 'path_to_file.mdf',
  SIZE = <number>[KB|MB|GB|TB],
  MAXSIZE = <number>[KB|MB|GB|TB],
  FILEGROWTH = <number>[KB|MB|GB|TB]
);

-- Change an existing file
ALTER DATABASE database_name
MODIFY FILE (
  NAME = logical_file_name,
  SIZE = <number>[KB|MB|GB|TB]
);

SQL SIZE Parameters

SIZE (integer) - Numeric value followed optionally by KB, MB, GB, or TB that sets the initial file size.
Units - Optional suffix; if omitted the number is treated as kilobytes (8-KB pages).

Example Queries Using SQL SIZE

-- Create a 20-GB primary data file and 5-GB log file
CREATE DATABASE Sales
ON PRIMARY
( NAME = SalesData,
  FILENAME = 'D:\SQLData\SalesData.mdf',
  SIZE = 20GB,
  MAXSIZE = 100GB,
  FILEGROWTH = 2GB )
LOG ON
( NAME = SalesLog,
  FILENAME = 'D:\SQLLogs\SalesLog.ldf',
  SIZE = 5GB,
  MAXSIZE = 50GB,
  FILEGROWTH = 1GB );

-- Increase an existing file to 40 GB
ALTER DATABASE Sales
MODIFY FILE ( NAME = SalesData, SIZE = 40GB );

Expected Output Using SQL SIZE

The database is created (or file resized) with the specified initial size
Disk space equal to SIZE is immediately reserved
SQL Server returns "Command(s) completed successfully
"

Use Cases with SQL SIZE

Preallocate space for a new high-volume database
Prevent performance hits from frequent autogrowth
Satisfy storage team requirements for capacity planning
Ensure log files have sufficient space for long transactions

Common Mistakes with SQL SIZE

Forgetting the unit, leading to an unexpectedly small file
Setting SIZE larger than disk capacity, which returns an error
Using SIZE in statements that do not accept file options
Confusing SIZE with FILEGROWTH; the former is initial allocation, the latter is incremental growth

First Introduced In

SQL Server 7.0

Frequently Asked Questions

What happens if I omit SIZE?

SQL Server assigns default sizes (8 MB data, 1 MB log) and then relies on FILEGROWTH to expand the files.

Does SIZE reserve disk space immediately?

Yes. The operating system allocates the specified amount of space as soon as the command executes.

Can SIZE be larger than MAXSIZE?

No. SIZE must be less than or equal to MAXSIZE; otherwise SQL Server returns error 5134.

How do I change SIZE on an existing database?

Use ALTER DATABASE database_name MODIFY FILE (NAME = logical_name, SIZE = new_size );