Technical Architects Zone (TAZ)

SQL Server Administration

SQL Server Database Creation

From Basics to Production Best Practices

Introduction

Creating a database is one of the first tasks a SQL Server administrator learns. At its simplest, it requires only one command.

CREATE DATABASE TAZDemo;
GO

SQL Server creates the database, but from a DBA or database architect's point of view, database creation involves much more than simply choosing a database name.

Before creating a production database, we should think about:

Where the data and log files will be stored
How large the files should be initially
How the files should grow
How much free storage is available
Whether additional files or filegroups are required
Who should own the database
Which recovery model is appropriate
How backups and recovery will be implemented
How growth and capacity will be monitored

TAZ Principle: Creating the database is easy. Designing it so that it can grow predictably, remain manageable and support the required recovery objectives requires planning.

1. SQL Server Database Files 2. Creating a Database Using SSMS 3. Creating a Database Using T-SQL 4. CREATE DATABASE Parameters 5. Initial Size vs Autogrowth 6. Fixed vs Percentage Growth 7. Data and Log Storage 8. Verify the Database 9. Database Owner 10. Recovery Model 11. Common Mistakes 12. Production Checklist 13. Interview Questions

1. What Happens When We Create a Database?

A SQL Server database is not simply a logical name displayed inside SQL Server Management Studio. SQL Server uses physical files to persist database data and the transaction log.

MDF - Primary Data File

The MDF is the primary data file. Every SQL Server database has one primary data file.

TAZDemo.mdf

NDF - Secondary Data Files

Secondary data files are optional and are typically used when a database design requires additional data files or filegroups.

TAZDemo_Data02.ndf
TAZDemo_Data03.ndf

LDF - Transaction Log File

The transaction log contains the log records required by SQL Server's transaction-management and recovery mechanisms.

TAZDemo_log.ldf

TAZDemo Database
       |
       +-- Data
       |    |
       |    +-- TAZDemo.mdf
       |    +-- Optional .ndf files
       |
       +-- Transaction Log
            |
            +-- TAZDemo_log.ldf

Easy way to remember: MDF and NDF are data files. LDF is the transaction log file.

2. Creating a Database Using SSMS

Open SQL Server Management Studio and connect to the required Database Engine instance.

Object Explorer
|
+-- Databases
    |
    +-- Right-click
        |
        +-- New Database...

Enter:

TAZDemo

Before selecting OK, review:

Database owner
Logical file names
File type
Initial size
Autogrowth configuration
Maximum size where applicable
Physical file paths

For a lab, accepting suitable defaults may be convenient. For production, review the settings against the storage, capacity and recovery requirements.

3. Creating a Database Using T-SQL

The simplest form is:

CREATE DATABASE TAZDemo;
GO

For greater control over the database files, specify the data and log configuration:

CREATE DATABASE TAZDemo
ON PRIMARY
(
    NAME = N'TAZDemo_Data',
    FILENAME = N'D:\SQLData\TAZDemo.mdf',
    SIZE = 1024MB,
    FILEGROWTH = 256MB
)
LOG ON
(
    NAME = N'TAZDemo_Log',
    FILENAME = N'E:\SQLLogs\TAZDemo_log.ldf',
    SIZE = 512MB,
    FILEGROWTH = 128MB
);
GO

Important: The D: and E: paths are examples. The directories must exist, SQL Server must have access to them, and the storage design must be valid for your environment.

4. Understanding CREATE DATABASE Parameters

NAME

NAME = N'TAZDemo_Data'

This is SQL Server's logical file name.

FILENAME

FILENAME = N'D:\SQLData\TAZDemo.mdf'

This specifies the physical storage path.

SIZE

SIZE = 1024MB

This specifies the file's initial size. Appropriate sizing depends on the expected workload, growth profile, available storage and operational requirements.

FILEGROWTH

FILEGROWTH = 256MB

This controls how much additional space SQL Server allocates to that file during an automatic growth event.

Autogrowth is configured separately for each database file. Microsoft Learn notes that this means data and log files each have their own growth settings. 【1-3473fe】

5. Initial Size vs Autogrowth

A common mistake is to create very small files and expect autogrowth to handle normal capacity expansion.

Small File
   |
   v
Autogrow
   |
   v
Autogrow
   |
   v
Autogrow
   |
   v
Repeated Growth Events

A better approach is:

Estimate Capacity
      |
      v
Reasonable Initial Size
      |
      v
Monitor Usage
      |
      v
Planned Expansion
      |
      v
Autogrowth Available as Protection

Recommended mindset: Treat autogrowth as protection against unexpected space requirements, not as your primary capacity-management strategy.

This matches Microsoft Learn guidance that database files should be sized appropriately and disk space should still be monitored even when autogrowth is enabled. 【1-3473fe】

6. Percentage Growth vs Fixed Growth

SQL Server supports both percentage and fixed-size growth.

FILEGROWTH = 10%

or:

FILEGROWTH = 512MB

Percentage-based growth changes as the file becomes larger:

Current File Size	10% Growth Event
10 GB	1 GB
100 GB	10 GB
500 GB	50 GB

For many environments, fixed increments are easier to understand and predict. The correct growth increment depends on the workload and file size rather than a universal value.

7. Where Should Data and Log Files Be Stored?

A traditional Windows layout might resemble:

C:\  Operating System / SQL Server Binaries

D:\  SQL Server Data
     +-- .mdf
     +-- .ndf

E:\  SQL Server Transaction Logs
     +-- .ldf

F:\  SQL Server Backups
     +-- .bak
     +-- .trn

Important architectural point: Different drive letters do not automatically mean physically separate storage. Two Windows volumes may ultimately share the same SAN, RAID group, storage pool or cloud-storage subsystem.

When performance isolation is the objective, verify the entire I/O path rather than assuming that D: and E: automatically provide independent storage.

8. Verify the Newly Created Database

Verify the database status and recovery model:

SELECT
    name,
    database_id,
    state_desc,
    recovery_model_desc
FROM sys.databases
WHERE name = N'TAZDemo';
GO

Check Database Files

USE TAZDemo;
GO

SELECT
    name AS LogicalFileName,
    type_desc AS FileType,
    physical_name AS PhysicalFileName,
    size * 8.0 / 1024 AS FileSizeMB,
    max_size,
    growth,
    is_percent_growth
FROM sys.database_files;
GO

Microsoft specifically identifies sys.database_files for examining current-database file configuration and sys.master_files for instance-wide file information. Microsoft Learn 【1-3473fe】

Readable File Size and Growth Configuration

SELECT
    name AS LogicalFileName,
    type_desc AS FileType,
    physical_name AS PhysicalFileName,
    CAST(size * 8.0 / 1024 AS decimal(18,2)) AS FileSizeMB,
    CASE
        WHEN is_percent_growth = 1
            THEN CAST(growth AS varchar(20)) + '%'
        ELSE
            CAST(
                CAST(growth * 8.0 / 1024 AS decimal(18,2))
                AS varchar(30)
            ) + ' MB'
    END AS FileGrowth
FROM sys.database_files;
GO

9. Database Owner

The database owner should also be reviewed after database creation.

SELECT
    name AS DatabaseName,
    SUSER_SNAME(owner_sid) AS DatabaseOwner
FROM sys.databases
WHERE name = N'TAZDemo';
GO

Database ownership should follow your organization's SQL Server security and operational standards rather than being left unreviewed.

10. Recovery Model

SQL Server supports three recovery models:

SIMPLE
FULL
BULK_LOGGED

Check the current configuration:

SELECT
    name,
    recovery_model_desc
FROM sys.databases
WHERE name = N'TAZDemo';
GO

Architectural decision: Do not choose the recovery model only because one option sounds safer. The selection must align with backup frequency, restore strategy, point-in-time recovery requirements, acceptable data loss and transaction-log management.

11. Common Database Creation Mistakes

1. Accepting every default without review
Defaults may be fine for many environments, but production settings should be reviewed against real requirements.

2. Creating files too small
Tiny files can cause unnecessary autogrowth during expected database expansion.

3. Treating autogrowth as capacity planning
Autogrowth does not replace storage monitoring and planned capacity expansion.

4. Ignoring the transaction log
The transaction log needs its own sizing, growth, backup and monitoring strategy.

5. Assuming different drive letters mean separate physical storage
Always understand the underlying storage architecture.

6. Routine shrinking
Shrinking files only for normal growth to expand them again usually creates unnecessary work. Autoshrink and repetitive shrink/grow cycles should not be treated as routine capacity management. Microsoft Learn 【1-3473fe】

12. Production Database Creation Checklist

☐ Database naming convention reviewed

☐ Database owner defined

☐ Data-file location confirmed

☐ Transaction-log location confirmed

☐ Underlying storage architecture understood

☐ Initial data-file size estimated

☐ Initial log-file size estimated

☐ Data FILEGROWTH reviewed

☐ Log FILEGROWTH reviewed

☐ Percentage vs fixed growth reviewed

☐ Maximum-size requirements considered

☐ Expected database growth estimated

☐ Free storage verified

☐ Recovery model selected

☐ Backup and restore strategy defined

☐ RPO and RTO requirements understood

☐ Additional files/filegroups evaluated

☐ File and disk-space monitoring configured

☐ Database configuration verified after creation

13. Quick Interview Questions

What is an MDF file?

The MDF is the primary data file of a SQL Server database.

What is an NDF file?

An NDF is a secondary data file. Additional data files can be used according to the database's storage and filegroup design.

What is an LDF file?

The LDF is a SQL Server transaction log file.

What is database autogrowth?

Autogrowth allows SQL Server to expand an individual database file when additional file space is needed, according to that file's configured growth setting.

Should we depend entirely on autogrowth?

No. Capacity should be monitored and planned. Autogrowth provides additional protection when a file unexpectedly requires more space.

Is percentage FILEGROWTH always wrong?

No. However, the actual growth allocation increases as the underlying file becomes larger, making fixed-size increments easier to predict in many environments.

Does placing MDF and LDF files on D: and E: guarantee separate storage?

No. The underlying SAN, RAID, cloud-storage or virtualization architecture determines whether the I/O paths are actually isolated.

The DBA Mindset

Database
|
+-- Owner
|
+-- Recovery Model
|
+-- Data Files
|   |
|   +-- Initial Size
|   +-- Growth
|   +-- Physical Storage
|
+-- Transaction Log
|   |
|   +-- Initial Size
|   +-- Growth
|   +-- Physical Storage
|
+-- Backup Strategy
|
+-- Recovery Objectives
|
+-- Capacity Planning
|
+-- Monitoring

That is the difference between simply knowing the CREATE DATABASE command and understanding SQL Server administration.

Final Thoughts

Creating a SQL Server database is easy. Designing a database that remains manageable as it grows requires considerably more thought.

Whenever you execute:

CREATE DATABASE MyDatabase;

think beyond the command itself:

Where will the files live?
How much space does the workload really need?
How should growth be handled?
What happens if the volume becomes full?
How will the transaction log be maintained?
What recovery model supports the backup strategy?
How will growth and free disk space be monitored?

Developing that habit is what turns a database-creation task into proper SQL Server administration.

Author: Ajit Kumar Patnaik