DataGinger.com

Does a database allows to create multiple mdf files? Let’s check here

Until recently I am on the assumption that a database can only have one data file with the *.mdf extension.

While checking the tempDB configuration on one of our production servers, I noticed tempDB having multiple data files with .mdf extension as shown in the screenshot below.

However, I expected the tempDB to be having only one .mdf file and multiple .ndf files, which is the way I have been seeing all these years.

To test it by myself (which I always prefer) I tried creating more than one data file with *.mdf extension on a database, and ofcourse YES, it accepted without any issues.

Reason: The primary data file shouldn’t necessarily have to be a .mdf extension

• A primary data file is the data file associated when first created the database, and YES this can only be one

Does the .mdf/.ndf/.ldf file extensions really matter?

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

Microsoft’s recommended file name extension for primary data files is .mdf. Similarly for secondary data files is .ndf and for transaction log file is .ldf. However, I can have any extension (something like .xyz) for any file (primary/secondary/log). Please refer to the screenshot below for an example of database created with different FileName extensions.

So, how does SQL Server know the file we are adding is a data file or log file ?

• If using GUI, in the File Type we mention either “Rows Data” or “Log”
• If using T-SQL, ALTER DATABASE DbName ADD FILE… or ALTER DATABASE DbName ADD LOG FILE…

In Conclusion: Yes, we can create multiple data files with .mdf extension for a database either on an already existing filegroup or by creating a new filegroup. Generally speaking secondary files have a .ndf extension (not necessarily though!), but you can give it whatever extension you want, and it will work correctly.

Read Full Post »