How to Identify the Database Files in SQL Server

February 24, 2014 , , , 0 Comments

In SQL Server programming context, each database is stored as a set of files on the hard disk of the computer. These files may be of following types including

Primary data file

The primary data file contains the database objects. The primary file can be used for the system tables and objects, and the secondary file can be used to store user data and objects. The primary data file has (.mdf) extension.

Secondary data file

The secondary data file also stores the database objects. Very large databases may need multiple secondary data files spread across multiple disks. Databases need not have secondary data files, if the primary data file is large enough to hold all the data in the database. The secondary data file has (.ndf) extension.

Transaction log file

The transaction log file records all modifications that have occurred in the database and the transactions that caused those modifications. At least one transaction log file holds all the transaction’s information and can be used to recover a database. At least one transaction log file must exist for a database. There can be more than one transaction log file. The minimum size of a transaction log file is 512K. The size of the transaction log file should be 25 – 40 percent of the size of the database. The log file have an (.Idf) extension.
A database must consist of a primary data file and one transaction log file.
The database files are stored in filegroups. A filegroup is a collection of files. A database comprises a primary filegroup and any user-defined filegroup. A primary filegroup contains the primary data file and any other files that are not put into any other filegroup. The primary filegroup also contains the system database. When objects are created in the database without specifying the filegroup, they are assigned to the default filegroup. Only one filegroup in a database can be the default filegroup.

A user-defined filegroup is a filegroup that is created by users. You can create filegroups to distribute the data amongst more than one filegroups to improve the performance of database.

Rhett Butler

Some say he’s half man half fish, others say he’s more of a seventy/thirty split. Either way he’s a fishy bastard. Google