SQL Server Database Files
SQL Server database is made up of data files and log files. While the data files store data and objects like views, tables, rules, views etc; the log files stores transactions with every detail of operation performed on database (also helpful in various recovery scenarios). Meanwhile, the data files are categorized as Primary Database File and Secondary Database File. Let us have an introduction to them.
Primary Database (MDF)
Primary Data file (with suffix ‘.mdf’) of SQL Server provides reference to start point of database and gives pointers to files that are connected with database as well as tables, columns, index etc. For every database, a primary database (.mdf) file gets created.
Some Facts about MDF File:
- Location of a Primary Data File is stored in Master Database (filename coloumn>> sysdatabases table)
- An MDF file is the part of default database File Group. Only one DB file is enough for storing tables and indexes.
- Header details of primary data file is stored in sysfiles table. It also stores logical and physical filename of database.
Secondary Database (NDF)
The *.ndf files are used for secondary data files that make up all the data files, other than the primary data file. Secondary data files are used with extra large databases and often are stored on other hard drives, than primary database files (*.mdf files). Some databases might not have any secondary data files, while other might have several secondary data files.
Secondary data files are optional, are user-defined, and store user data. Secondary files can be used to spread data across multiple disks by putting each file on a different disk drive. Additionally, if a database exceeds the maximum size for a single Windows file, you can use secondary data files so the database can continue to grow.
Transaction Logs (LDF)
The LDF stand for ‘Log database file’ and it is the transaction log. Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction. LDF files contain logging information for all transactions completed by the server. LDF files are used to time stamp any transactions to the SQL Server database, allowing the SQL database to be easily recoverable in the case of data loss.
The transaction log supports the following operations:
- Recovery of individual transactions.
- Recovery of all incomplete transactions when SQL Server is started.
- Rolling a restored database, file, filegroup, or page forward to the point of failure.
- Supporting transactional replication.
- Supporting high availability and disaster recovery solutions: AlwaysOn Availability Groups, database mirroring, and log shipping.