I have searched online but not found what I am looking for.
The problem/query is to my knowledge a database in SQL Server should have One .mdf file and maybe some .ndf files and One .log file.
I have seen many database with one mdf and multiple ndf files. But recently I came across some database on a server where every database had multiple mdf files.
I took a backup of some database moved them to another server and restored the Primary file as .mdf and all the other files as .ndf obviously except the log file it was restored as .log.
Now my questions are:
- Is there any wisdom in having multiple .mdf files for a database?
- Is there any drawbacks of having multiple .mdf files?
It was SQL Server 2008 R2 64bit Enterprise Edition
Any advice or pointer in the right direction is much appreciated. Thank you.
Best Answer
That is because of incorrect naming convention. Microsoft says that every database has one primary data file but it does not means it can have only one "mdf data file", database can have many data file with
.mdfextension but only one will be primary data file. It is better to givemdfextension to primary data file andndfto secondary data file to have proper demarcation but this is no hard and fast rule you can also give .abc extension to primary data file so what you witnessed is normal. As a fact you can give any extension you like.If you mean:
The answer is no, a database can have only one primary data file.
But if you mean:
It depends, you can and cannot have advantage with multiple data files. If they are spread across different physical drives (I am talking about spindles) you would see some advantage with write intensive application. If they are all on same logical partitions it won't have any advantage because underlying they would be using common resource. Using files and filegroups improves database performance, because it lets a database be created across multiple disks, multiple disk controllers, or RAID (redundant array of independent disks) systems. For example, if your computer has four disks, you can create a database that is made up of three data files and one log file, with one file on each disk. As data is accessed, four read/write heads can access the data in parallel at the same time.
As per this MSDN BOL article
Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.
Other advantage which I know is consider a 1 TB database if you would have single data file for it and you want to restore this database on some other server its highly unlikely you would have 1 TB of free space. Now if the same database is spread on different files each having size 250 G it becomes easier in restoration. This might not be actually your scenario but it does helps a lot its little easy to find a server with four 250 G drives than one 1 TB drive
I would say instead of many data files its better to have different file groups but again there are not many environments. Databases made up of multiple filegroups can be restored in stages by a process known as piecemeal restore. Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple mode. Files or filegroups in a database can be backed up and restored individually. This allows you to restore only the damaged files without having to restore the rest of the database. Files in a filegroup backup can be restored individually or as a group