[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 797
  • Last Modified:

Why we need secondary data files in SQL server?

Why we need secondary datafiles in the database? What are thier advantages? Also why we need multiple secondary files?
0
asqldba
Asked:
asqldba
  • 4
2 Solutions
 
Tapan PattanaikSenior EngineerCommented:

Secondary data files:
Secondary data files make up all the data files, other than the primary data file. Some databases may not have any secondary data files, while others have several secondary data files. The recommended file name extension for secondary data files is .ndf.

For more details check this below link:

http://msdn.microsoft.com/en-us/library/ms179316.aspx
0
 
Tapan PattanaikSenior EngineerCommented:

hi asqldba,
                 Check these links ,
             

Secondary data files:
Secondary data files make up all the data files, other than the primary data file. Some databases may not have any secondary data files, while others have several secondary data files. The recommended file name extension for secondary data files is .ndf.

For more details check this below link:

http://msdn.microsoft.com/en-us/library/ms179316.aspx

http://www.dotnetspider.com/resources/19525-Database-Files-SQL-Server.aspx
0
 
asqldbaAuthor Commented:
I do understand that there are 3 type of file: Primary, Secondary and Log. My question is why we need secondary data files? Why can't we just keep just two? MDF and LDFs are needed but why we need NDF?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Tapan PattanaikSenior EngineerCommented:
hi asqldba,

The big question now is why do we need secondary database files when I can store data in primary database files?

Well this has certain advantages and disadvantages. The main disadvantage of multiple database files is administration. You need to remember these different files, their locations, and their use. The main advantage is that you can place these files on separate physical hard disks, avoiding the creation of hot spots and thereby improving performance. When you use database files, you can back up individual database files rather than the whole database in one session.
0
 
Jerryuk007Commented:
The main reason I see as having multiple data files is so you can split a large database across multiple drives, or separate indexes, etc... for better performance. Or backup the database in "steps" (as mentioned above)

On small to average size database, I would generally use just one data file and one log file (there are of course a few exceptions...)

Jerry
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now