0

If I have a Windows disk with a capacity of 1T, for example, and the primary datafile (.mdf) for my database reaches the disk's maximum size, I want to add a second datafile to the primary group.

As this second datafile is isolated on another disk, can it be of type .ndf? If I create this second file of type .mdf, could there be any performance or operational problems other than just the fact that conventionally only one .mdf should exist?

(I'm not talking here about the logs (.ldf), which are separate on their own disk).

Louis
  • 1
  • 2

2 Answers2

1

Convention is the only thing dictating your... well... naming convention.

You're free to name the files anything. The on-disk extension doesn't matter.

Consider:

create database foo
    on (
        name = foo,
        filename = 'D:\MSSQL\Data\DBA-343369\foo.mdf'
    );
go
alter database foo
    add file (
        name = TheSecondMdf,
        filename = 'D:\MSSQL\Data\DBA-343369\TheSecondFile.mdf'
    );
go
alter database foo
    add file (
        name = RandomExtension,
        filename = 'D:\MSSQL\Data\DBA-343369\.'
    );
go

a demo database datafiles with some weird names

That said, I'd recommend you stick with convention and give the ".ndf" extension to data files after the first one. It might be hard-coded into some utility scripts or you may simply confuse the next person who touches the media.

Peter Vandivier
  • 5,485
  • 1
  • 25
  • 49
0

It is possible that your system admins created a rule on the virus scanner on your server to exclude database files (they normally should :-) ) . So it is important that you use in this case the standard files extensions for SQL server. This is for 1° file .MDF, other data files .NDF and log fil .LDF. But like already answered, you're free to use any extension you want.

Ludo
  • 66
  • 3