SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DB File Extension


DB File Extension

Author
Message
Steve Eckhart
Steve Eckhart
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1224 Visits: 8664
I didn't select .ndf because it is a suggested name, not a default. A default implies that if the user makes no other selection, the system will automatically set a value. So, if I add a new data file to a database and don't specify an extension, SQL would automatically append .ndf to the file name. This is not the case. If you add a file (in SQL Server 2000) to a database and do not include an extension, SQL Server doesn't append any extension.



Steve Eckhart
sqlizer
sqlizer
SSC Eights!
SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)

Group: General Forum Members
Points: 804 Visits: 628
I disagree with the answers, .ldf is for logfile not for datafile. the question specifically asks for the database files.
SQL Server 2005 does not enforce database filename extensions, but which of the following are the default file extensions for database files? (choose all that apply)w00t
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6622 Visits: 2398
This QOD had trick question written all over it, or so it seemed to me. I almost chose .mdf and .ndf based on the word "database" in the question but then checked BOL, saw all 3 grouped together and decided to include .ldf anyway. I got it right but certainly did not like the way it was worded.
jim.powers
jim.powers
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2838 Visits: 871
I suppose that technically .ldf should be included but I'm inclined to agree with most here who got stuck on wording.
Alvin Ramard
Alvin Ramard
SSCrazy Eights
SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)SSCrazy Eights (9.3K reputation)

Group: General Forum Members
Points: 9308 Visits: 11687
Like someone already said, do not confuse database files with data files.

If you do not agree that the log file is a database file then go ahead and remove the log file from your databases. We can revisit the issue after that.



Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help
mtassin
mtassin
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7332 Visits: 72521
Come on if you follow the link to MS's site you find this glaring at you


SQL Server databases have three types of files:

* Primary data files
The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .mdf.
* 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.
* Log files
Log files hold all the log information that is used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf.


Thus log files are database files. If you actually create a database and specify a second data file and don't enter a filename in SQL 2005 it adds ndf to it. Hence mdf, ldf, and ndf are all database file extensions.

A Database to SQL server is what all of these files when combined provide. Concurrency control is a necessity in an RDBMS so having a LOG file so you can use Transactions is a must. This was an easy one.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
webrunner
webrunner
SSCrazy Eights
SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)SSCrazy Eights (8K reputation)

Group: General Forum Members
Points: 8021 Visits: 4001
I knew I should have checked *.ldf - even though it is a log file, so I thought it was not a database file.

I guess both are considered "database files" because they are needed for the database. Sounds picky, but the URL below makes sense - mdf/ndf files are "data files" and ldf files are "log files". Together they make up "database files."

A primer on SQL Server database files
http://articles.techrepublic.com.com/5100-10878_11-6096988.html

I should go with my gut more often. Smile

webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
timothyawiseman
timothyawiseman
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2404 Visits: 920
Michael Kipp (8/28/2008)
Please don't confuse "database files" with "data files".
A database is more than just a data file.


A database is more than just a data file, but it is not inherently dependent on a log. Many databases that are designed for single-user use do not maintain any form of log file, yet remain true databases.

Even in MS SQL Server specifically, while the log file is of great importance for many things, it at least arguably does not hold the database entities. It can be discarded and rebuilt with no challenge if you have the actual data files (.mdf and .ndf by default.)

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Iggy-SQL
Iggy-SQL
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3251 Visits: 440
for those of you who says .ldf is log file not data file, please read the question carefully, it asks for database file, not data file. .ldf forms a part of database file, because database file consists of (on full and bulk-logged recovery mode) data file and log file.

I personally think it's a good question, as it tests your knowledge of differentiating data file and database file.


Urbis, an urban transformation company
SimonLiew
SimonLiew
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4281 Visits: 1839
yep, i was confused by the question too. I thought it was just asking for database file extension, not include database log extension, meaning only mdf and ndf by default.

What a confusing question indeed.
- Simon

Simon Liew
Microsoft Certified Master: SQL Server 2008
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search