SQL Server Interview Questions & Answers

Topic 3: Database Files, Filegroups & Storage

Questions 21–30 | Beginner to Intermediate

This section covers how SQL Server organizes database files, filegroups and storage. These concepts are important for database design, capacity planning, performance, backup and recovery, and day-to-day production administration.

A DBA should understand not only how files are configured, but also how SQL Server allocates space, handles file growth and responds when storage becomes unavailable.

21. Why would you create multiple data files?

Answer:
Multiple data files may be used to improve storage management, distribute database allocations and support large or heavily used databases.

Common reasons include:

Managing very large databases more effectively
Distributing database files across appropriate storage volumes
Reducing allocation contention in specific workloads
Supporting multiple filegroups
Separating specific tables or indexes logically
Supporting filegroup-based backup and restore strategies

Within a filegroup containing multiple files, SQL Server uses proportional-fill behavior when allocating new extents. Files with more available free space can receive proportionally more allocations.

Interview Tip:
Multiple files should be created for a specific design or operational reason. Simply adding more files does not automatically make a database faster.

22. What is the PRIMARY filegroup?

Answer:
The PRIMARY filegroup is the default filegroup created with every SQL Server database.

The primary data file belongs to the PRIMARY filegroup, and system tables are stored there.

Unless another filegroup is configured as the default, newly created database objects are normally allocated to the PRIMARY filegroup.

You can review database files and their filegroups using:

SELECT
    name,
    type_desc,
    physical_name,
    size
FROM sys.database_files;

23. What are user-defined filegroups?

Answer:
User-defined filegroups are additional filegroups created by a DBA or database designer to logically organize one or more data files.

For example:

PRIMARY
FG_DATA
FG_INDEX
FG_ARCHIVE

User-defined filegroups can help with:

Large database administration
Partitioning strategies
Piecemeal backup and restore
Read-only or archival data management
Logical separation of database objects

DBA Tip:
Filegroups provide logical organization. Actual performance benefits depend on the underlying storage architecture and workload.

24. Can tables and indexes be placed on different filegroups?

Answer:
Yes. SQL Server allows certain table and index structures to be placed on different filegroups.

For example, a table's clustered index could be created on FG_DATA while a nonclustered index could be created on FG_INDEX.

CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
ON dbo.Orders(CustomerID)
ON FG_INDEX;

However, remember that the leaf level of a clustered index contains the table's data rows. Therefore, placing the clustered index on a filegroup effectively places that table's clustered data there.

Interview Tip:
Separating objects across filegroups is useful for administration, partitioning and recovery design. Performance improvements require the underlying storage architecture to support the intended benefit.

25. How does SQL Server allocate pages and extents?

Answer:
SQL Server stores data in 8 KB pages and groups eight contiguous pages into a 64 KB extent.

1 page = 8 KB
8 pages = 1 extent
1 extent = 64 KB

SQL Server uses allocation-map pages to track available space and allocation status.

PFS tracks page allocation and free-space information.
GAM tracks free and allocated extents.
SGAM tracks mixed extents with available pages.

As objects grow, SQL Server allocates additional pages and extents from the files belonging to the appropriate filegroup.

Interview Tip:
Remember the basic storage hierarchy: Row → Page → Extent → File → Filegroup → Database.

26. What happens when a database file becomes full?

Answer:
What happens depends on the file configuration and the amount of available storage.

SQL Server may:

Autogrow the file if autogrowth is enabled and disk space is available.
Allocate space from another eligible file in the same filegroup.
Fail allocations if no usable space is available.

If SQL Server cannot allocate additional space, operations requiring new pages can fail.

A DBA should check:

Available disk space
Database file size
Configured MAXSIZE
Autogrowth settings
Free space inside existing database files
Other files within the same filegroup

Production Tip:
Do not use autogrowth as your primary capacity-management strategy. Monitor database growth and increase file sizes proactively whenever possible.

27. What is file autogrowth?

Answer:
Autogrowth is a SQL Server file configuration that allows a database data or transaction log file to increase automatically when additional space is required.

Growth can be configured as:

A fixed amount, such as 512 MB or 1 GB
A percentage of the current file size

You can review file growth settings using:

SELECT
    name,
    type_desc,
    size * 8.0 / 1024 AS SizeMB,
    CASE
        WHEN is_percent_growth = 1
            THEN CAST(growth AS varchar(20)) + '%'
        ELSE
            CAST(growth * 8.0 / 1024 AS varchar(20)) + ' MB'
    END AS GrowthSetting,
    max_size
FROM sys.database_files;

Autogrowth should be considered a safety mechanism rather than a replacement for proper capacity planning.

28. Percentage growth vs fixed-MB growth: which is preferable?

Answer:
In most production environments, a carefully selected fixed-size growth increment is preferable to percentage-based autogrowth.

The problem with percentage growth is that the actual growth amount changes as the database becomes larger.

For example, with 10% growth:

A 10 GB file grows by approximately 1 GB.
A 100 GB file grows by approximately 10 GB.
A 1 TB file grows by approximately 100 GB.

This can make growth events increasingly large and unpredictable.

Fixed growth provides more predictable capacity behavior.

DBA Tip:
There is no universal fixed growth value for every database. Choose the increment based on normal database growth, file size, available storage, workload and how quickly the storage subsystem can satisfy a growth event.

29. Can a database have multiple transaction log files?

Answer:
Yes. SQL Server technically allows a database to have multiple transaction log files.

However, SQL Server does not use multiple log files in the same way that it can allocate data across multiple data files.

The transaction log operates logically as a sequential stream of log records. Adding additional log files does not provide normal load balancing or striping of transaction log activity.

Therefore, creating multiple log files should not normally be used as a transaction log performance-tuning technique.

Common Interview Trap:
Multiple data files and multiple log files behave differently. Multiple log files do not automatically increase transaction log throughput.

30. When are multiple log files actually useful?

Answer:
Multiple transaction log files are generally useful only in specific operational or emergency situations rather than as a standard performance configuration.

A common example is when the existing transaction log volume becomes full and cannot be expanded immediately.

If another storage volume is available, a DBA may temporarily add another transaction log file on that volume to provide additional log space and keep the database operational.

Example:

ALTER DATABASE SalesDB
ADD LOG FILE
(
    NAME = SalesDB_Log2,
    FILENAME = 'E:\SQLLogs\SalesDB_Log2.ldf',
    SIZE = 4096MB,
    FILEGROWTH = 1024MB
);

This should normally be treated as an operational workaround while the underlying storage or capacity problem is addressed.

Interview Tip:
If asked whether you would create multiple log files for performance, the normal answer is no. First investigate log sizing, storage latency, VLF configuration, autogrowth and transaction-log throughput.

Useful DBA Script: Check Database File Configuration

The following query provides a quick overview of the files in the current database:

SELECT
    DB_NAME() AS DatabaseName,
    name AS LogicalFileName,
    type_desc AS FileType,
    physical_name AS PhysicalFileName,
    size * 8.0 / 1024 AS SizeMB,
    CASE
        WHEN max_size = -1
            THEN 'Unlimited'
        ELSE
            CAST(max_size * 8.0 / 1024 AS varchar(30)) + ' MB'
    END AS MaxSize,
    CASE
        WHEN is_percent_growth = 1
            THEN CAST(growth AS varchar(20)) + '%'
        ELSE
            CAST(growth * 8.0 / 1024 AS varchar(20)) + ' MB'
    END AS AutoGrowth
FROM sys.database_files
ORDER BY type, file_id;

Interview Quick Recap

Multiple data files can assist with storage management, filegroups and some allocation-heavy workloads.
Every SQL Server database contains a PRIMARY filegroup.
User-defined filegroups provide additional logical organization for data files and database objects.
Tables and indexes can be strategically placed on filegroups.
SQL Server stores data in 8 KB pages and 64 KB extents.
When a file is full, SQL Server may use autogrowth or another eligible file in the same filegroup.
Autogrowth is a safety mechanism, not a replacement for capacity planning.
Fixed-size autogrowth generally provides more predictable growth than percentage-based growth.
SQL Server supports multiple transaction log files, but they do not provide normal log-performance striping.
Additional log files are primarily useful for specific operational situations, such as temporary storage-capacity workarounds.

← Previous Topic
# Topic 2: SQL Server Architecture & Internals

Next Topic →

# Topic 4: Recovery Models & Transaction Log

Author: Ajit Kumar Patnaik
Technical Architects Zone (TAZ)
Practical Solutions | Real-World Experience | Continuous Learning

Search This Blog

Technical Architects Zone

SQL Server Q&A Topic 3: Database Files, Filegroups & Storage