Solved

Why we need secondary data files in SQL server?

Posted on 2009-05-07
7
742 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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 …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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…

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now