Solved

Why we need secondary data files in SQL server?

Posted on 2009-05-07
7
751 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

773 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