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?

asked Nov 16 '10 at 10:25 AM in Default

amourage
11 ● 1 ● 1 ● 1

(comments are locked)

2 answers:

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:

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.

Oleg

answered Nov 16 '10 at 10:38 AM

Oleg
15.4k ● 1 ● 4 ● 24

Spot on...

Nov 16 '10 at 11:17 AM Matt Whitfield ♦♦

here here, cant add to that. +1

Nov 16 '10 at 03:14 PM Fatherjack ♦♦

(comments are locked)

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

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

When you are running out of space and need to let the DB continue adding data onto a secondary file
When you want to leverage separate spindles for indexes
When you want to isolate stale data onto its own set of disks
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.