Skip to content

Database Filegroups and Data Files#

Every SQL Server database contains a data file (.MDF) and a transaction log file (.LDF); however, you can add additional files to the database. These files are called secondary files (.NDF) and will also be used to store data rows or indexes. The files are assigned to filegroups in the same way that files are assigned to folders in a file system. When assigning several files to the same filegroup, data is distributed evenly between all of them, in a round-robin way.

When Critical Manufacturing is installed, it creates six data files:

  1. Primary
  2. MainTableDat_1
  3. MainTableIdx_1
  4. HstTableDat_1
  5. HstTableIdx_1
  6. The transaction log file

Additionally, it creates the necessary TempDB data and log files.

For Critical Manufacturing database, we strongly recommend that you isolate the transaction log file(s) into a separate LUN. Ideally, this LUN will be completely separated from the data files - even at disk level (most SAN vendors provide a way to set aside a few disks and isolate them to provide a write-optimized, mirrored disk resource like a RAID 10 LUN). With SQL Server's synchronous, write-log first transaction log behavior, the transaction log write latency is a critical potential bottleneck. You can avoid this by using proper SAN and database data file architecture.

SQL Server writes each transaction to the log before writing it to the data file. If both data and log are on the same disk, this means a lot of jumping back and forth between the two files and extra time taken up. Nonetheless, if the log is on its own disk, it has a dedicated read/write head to write the transactions, uninterrupted by data file writes.

It is also important to keep your log files separated, from a disaster recovery perspective. In case the disk that houses your data files fails, you will be glad to have the transaction log accessible in another disk. As a result, you are able to back up the tail of the log and be able to recover to the point of failure.

By definition, Critical Manufacturing uses one data file per filegroup.