Does SQL Server 2000 Automatically Create a .NDF File?

Does SQL Server 2000 automatically create a .NDF file on another available drive or partition if the primary data file runs out of disk space? I can't find any official documentation that states this, but I did find the following quote on a not-so-reputable website:

"NDF files are secondary database files that are used by Microsoft SQL Server. The NDF file is created when the primary .MDF database becomes too big (such as when there is no disk space left). The NDF file may also be manually added to the SQL Server File Group to allow for distribution of data between the two files. NDF files are typically stored on a different hard disk or partition than the primary database file. The NDF file should use the same filename as the corresponding MDF file."

Can someone please clear this up for me?
more ▼

asked Nov 16, 2010 at 10:25 AM in Default

amourage gravatar image

11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort newest

To address you question. No. NDFs are not created automatically.

There are several reasons why you might want to create an NDF

  1. When you are running out of space and need to let the DB continue adding data onto a secondary file
  2. When you want to leverage separate spindles for indexes
  3. When you want to isolate stale data onto its own set of disks
  4. When your MDF is simply too large. I remember back in the day when an NDF in a 2000 instance grew over 140 GBs and the database was throwing an error while trying to extend it. Not sure if it was an OS or DB limitation, but I resolved by adding an additional NDF.
more ▼

answered Nov 16, 2010 at 11:57 AM

ozamora gravatar image

1.4k 2 3 5

(comments are locked)
10|1200 characters needed characters left

As far as I know, creation of .NDF files has nothing to do with running out of space. SQL Server will NOT create an NDF file if MDF becomes too big, rather the files get created when the databse is created (if create database statement mentions filegroups and secondary data files). As a matter of fact SQL Server does not even enforce .NDF file extensions. From [BOL][1]:

SQL Server does not enforce the .mdf, .ndf, and .ldf file name extensions, but these extensions help you identify the different kinds of files and their use.


[1]: http://msdn.microsoft.com/en-us/library/ms179316(v=SQL.90).aspx
more ▼

answered Nov 16, 2010 at 10:38 AM

Oleg gravatar image

15.9k 2 4 24

Spot on...
Nov 16, 2010 at 11:17 AM Matt Whitfield ♦♦
here here, cant add to that. +1
Nov 16, 2010 at 03:14 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 16, 2010 at 10:25 AM

Seen: 2839 times

Last Updated: Nov 16, 2010 at 10:25 AM