Introduction to Database Files in SQL Server 2008

Today, I’m going to talk about the files associated with a SQL Server 2008 database. When we create a new database in SQL Server, there are a few files that are created by default. What are those? What is the use of those files? These are the questions I always come across, especially from beginners.

The database files are physical files on the underlying file system. They are having specific file extensions, which I’m going to talk about below.

There are two types of files that are created by default:

1. Data File:This file contains the actual data saved into the table in a database. However, we can sub-divide this category into two:
   • Primary Data File: Default data file for the database, also known as Primary Data File. It is having (.mdf) extension and default size of 2 MB. Primary data file cannot have size less than 2 MB. This is created by the SQL Server be default even if we don’t mention the file specification in our database creation script. It contains the database data as well as pointers to other data file i.e. secondary data files, if any. Every database can have one and only one primary data file (.mdf). It is created on the primary file group by default.
   • Secondary Data File: Contains data but is optional to have this file. It is having (.ndf) extension. We can have multiple secondary data file in a database. By default, it is created on primary file group. However, we’ve liberty to create it on secondary file group as well, which is the normal convention followed by DBAs.
2. Transaction Log: Contains most of the T-SQL statements fired against the database. It is very useful to recover a database up to certain point in time. However, don’t misinterpret that it contains all the T-SQL statements fired against a database. There are certain T-SQL statements, which are not logged into this file. However, I would like not to get into details of that over here, may be into another blog. Also, we don’t use it as a backup option. We have separate backup mechanism in SQL Server. We can create this file either on primary file group or secondary file group. By default, this is created on primary file group. We can omit specification for transaction log file during database creation. However, SQL Server will create a transaction log file  for you in that case, giving it size of 1 MB by default.