.ldf, .mdf and .ndf file extensions

Hi,
Is the following interpretation correct for MS-SQL file extensions:
.ldf -> transaction log file
.mdf -> "master" data file
.ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
----
---- --
----
----
----
--
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)Found this in the Books Online. This answers my query.
File type File name extension
Primary data file .mdf
Secondary data file .ndf
Transaction log file .ldf
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
Hi,
Is the following interpretation correct for MS-SQL file extensions:
.ldf -> transaction log file
.mdf -> "master" data file
.ndf -> "normal" data file
Also, why is that the first data file that gets created in a database has
the extension .mdf? Is it in some way related to the "master" database?
Thanks,
- V
Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
osql for an MS-SQL 2005 instance.
fileid groupid size maxsize growth status perf
name
filename
-- -- -- -- -- -- --
----
---- --
----
----
----
--
1 1 38400 51200 10 1048578 0
dataFile1
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
2 0 12800 268435456 0 66 0
marathon_log1
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
3 1 4096 -1 128 2 0
marathon_data4_444444
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
4 2 3968 -1 128 2 0
marathon_data2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
5 2 8576 -1 128 2 0
marathon_data3
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
6 2 3840 -1 128 2 0
marathon_data5_55555555
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
7 2 14208 -1 0 2 0
marathon_data6_66666666
M:\Program Files\Microsoft SQL
Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
8 0 384 268435456 128 66 0
marathon_log2
M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
(8 rows affected)|||Be aware that these are just conventions. SQL Server does not enforce or
require any particular file extensions for any data or log files. Still,
it is a good idea to stick to the conventions if you want to go on vacation
sometime.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Volcano" <volcano@.volcano.com> wrote in message
news:OjHsAb0pFHA.2504@.tk2msftngp13.phx.gbl...
> Hi,
> Is the following interpretation correct for MS-SQL file extensions:
> .ldf -> transaction log file
> .mdf -> "master" data file
> .ndf -> "normal" data file
> Also, why is that the first data file that gets created in a database has
> the extension .mdf? Is it in some way related to the "master" database?
> Thanks,
> - V
> Here's the output of "use marathon; select * from dbo.sysfiles;" passed to
> osql for an MS-SQL 2005 instance.
>
> fileid groupid size maxsize growth status perf
> name
> filename
> -- -- -- -- -- -- --
> ----
> ---- --
> ----
> ----
> ----
> --
> 1 1 38400 51200 10 1048578 0
> dataFile1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data1.mdf
> 2 0 12800 268435456 0 66 0
> marathon_log1
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_log1.ldf
> 3 1 4096 -1 128 2 0
> marathon_data4_444444
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data4_444444.ndf
> 4 2 3968 -1 128 2 0
> marathon_data2
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data2.ndf
> 5 2 8576 -1 128 2 0
> marathon_data3
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data3.ndf
> 6 2 3840 -1 128 2 0
> marathon_data5_55555555
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data5_55555555.ndf
> 7 2 14208 -1 0 2 0
> marathon_data6_66666666
> M:\Program Files\Microsoft SQL
> Server\MSSQL.4\MSSQL\DATA\marathon_data6_66666666.ndf
> 8 0 384 268435456 128 66 0
> marathon_log2
> M:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\DATA\marathon_log2.ldf
> (8 rows affected)
>