DATA FILES IN SQL SERVER 2005

1) Primary File.

2) Secondary File.

3 ) Log File.

Data files are of two types Primary (.mdf extension ) and Secondary file ( .ndf  extension ). Each database can only have one primary data file. Ths primary data file is mandatory.Primary data file contains metadata i.e data of all the database objects in it.

Secondary files are not compulsory. We can create  many secondary data files but only one primary data file.If we do not create primary data file then the primary file is created by the SQL SERVER 2005.

If  the size of the primary file is full then we can also provide AutoGrow option for primary file.Secondary files are to be created by user.

The primary data file is the starting point of the database and points to the other files in the database. Some databases may not have any secondary data files, while others have several secondary data files .

Log files hold all the log information that is used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf.

SQL Server does not enforce the .mdf, .ndf, and .ldf file name extensions, but these extensions help you identify the different kinds of files and their use.

Pawan Kumar

Pawankkmr@hotmail.com