When working with SQL Server, it is important to understand the concept of database files. In this article, we will explore the different types of database files and their purposes.

Primary Data File (.MDF)

When a new database is created in SQL Server, it creates two physical files in the operating system: one with a .MDF extension and another with a .LDF extension. The .MDF file is called the primary data file and contains the database catalogs, which store metadata or “data about data”. This includes information about system objects, indexes, columns, and users. The primary data file is treated as a special file because it contains important information about the database.

Secondary Data File (.NDF)

If additional data files are added to the database, they will have a .NDF extension and are called secondary data files. Unlike the primary data file, secondary data files do not contain metadata. They only store transactional data entered by users. The .NDF extension helps users easily identify whether a specific file is a primary or secondary data file.

Storing data in different files under different file groups offers several advantages. For example, read-only tables can be stored in one file group, while read-write tables can be stored in another. This allows for more efficient backups, as only the file group with read-write data needs to be backed up. Additionally, creating files on different physical hard disks can improve I/O performance.

Shrinking Database

Shrinking a database in SQL Server does not mean compressing the file, as it does in regular terms. Instead, it refers to removing empty space from the database files and releasing it either to the operating system or to SQL Server. When shrinking a database, it is important to note that it can lead to performance degradation and increased fragmentation.

It is also worth mentioning that shrinking a database is a logged operation, meaning it is recorded in the log file. If there is no empty space in the log file, SQL Server cannot write to it, and the shrink operation cannot be performed.

When shrinking a database, the size can only be reduced to the amount of data currently stored in the file. For example, if a database has 30GB of data and 20GB of free space, the database can only be shrunk to 30GB.

Conclusion

Understanding SQL Server database files is crucial for efficient database management. The primary data file (.MDF) contains important metadata, while secondary data files (.NDF) store transactional data. Shrinking a database should be done with caution, as it can impact performance and lead to fragmentation. By utilizing different file groups and physical hard disks, you can optimize your database’s performance and backup strategies.