Solved

Why we need secondary data files in SQL server?

Posted on 2009-05-07
7
775 Views
Last Modified: 2012-08-13
Why we need secondary datafiles in the database? What are thier advantages? Also why we need multiple secondary files?
0
Comment
Question by:asqldba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
7 Comments
 
LVL 21

Accepted Solution

by:
Tapan Pattanaik earned 250 total points
ID: 24332029

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
 
LVL 21

Expert Comment

by:Tapan Pattanaik
ID: 24332040

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
 

Author Comment

by:asqldba
ID: 24332243
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
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!

 
LVL 21

Expert Comment

by:Tapan Pattanaik
ID: 24332345
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
 
LVL 21

Expert Comment

by:Tapan Pattanaik
ID: 24332398
0
 
LVL 6

Assisted Solution

by:Jerryuk007
Jerryuk007 earned 250 total points
ID: 24333679
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question