Mdf ,Ldf

Question

Mdf ,Ldf

mssqlsvr

SSC Enthusiast

Points: 109
More actions
May 9, 2009 at 11:44 pm

#209377

I am new sql server Can any explain what kind of data in the mdf, ldf files in the sql server.
Thanks & Regards,
Sathish.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The mdf file contains the data, the metadata, any indexes. The ldf file is the transaction log, a list of changes to data pages.

Have a read through this: http://msdn.microsoft.com/en-us/library/ms179316.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

maechismo_8514 SSCoach Points: 17510 More actions · Answer 2

mssqlsvr (5/9/2009)
I am new sql server Can any explain what kind of data in the mdf, ldf files in the sql server.
Thanks & Regards,
Sathish.

These are created when you create the database. ".mdf" which is the primary file by default which contains all the metadata and also the user objects that you create and ".ldf" file is the transaction log file which records all the operations that are run against your database.

EdVassie SSC Guru Points: 60472 More actions · Answer 3

Start SQL Server Books Online (also called BOL). Use the Index to find: databases [SQL Server]. Then click on: about databases.

This should give you the information you need. If there is anything you do not understand, then looking elsewhere in BOL can often get the information you need. Otherwise Wikipedia is a good place to look for definition of terms.

If you have done all this but need more advice, then people on this forum are happy to help.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Andrew Hatfield SSCrazy Points: 2241 More actions · Answer 4

Reply from http://www.sqlservercentral.com/Forums/Topic717598-357-1.aspx

MDF are the primary data file in a File Group. These files contain "data" - so your tables, indexes, keys, constraints, UDF, UDT, stored procs, functions, etc...

LDF files are your transactions.

NDF files are the same as MDF except they are subsequent datafiles in a File Group

BAK files are your full backups

TRN files are your transaction backups

--
Andrew Hatfield

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 5

Hi Folks

appreciate all the info as I am just starting with SQL Server 2005

I have gotten everything installed and am in the process of moving the databases (system,samples,user)

I just moved TEMPDB

My question is what is best practice for movng a database mdf and ldf files

keep together or keep seperate ?

Thanks

jim

maechismo_8514 SSCoach Points: 17510 More actions · Answer 6

JC (7/1/2009)
Hi Folks
My question is what is best practice for movng a database mdf and ldf files
keep together or keep seperate ?
Thanks
jim

I really dint get this question or am i a dumbhoo.. 😛

But as what I understand, depends on whether your business allows downtime obviously the fastest way to do is to use sp_attach and sp_detach stored proc's.....

Could you reframe your question please, if you don't mind 🙂

Thanks,

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 7

Hi Krishna Potlakayala

no problem

have not learned how to speak "SQL Server" quite yet

when a database is created i see there are two files .mdf and .ldf

i want to move all existing databases off the c drive to some other drive

do i keep the .ldf file with the .mdf file for a particular database

or

do i also move the ldf file for that particular database to its own drive ?

Thanks

Jim

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Please in future start a new thread for a new question.

Placing the mdf file on one drive and the ldf on a separate drive will give you IO benefits as long as they are two separate physical drives.

If your DB is small or has very low usage it probably isn't necessary

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 9

JC-3113

SSCrazy Eights

Points: 8371

July 1, 2009 at 12:10 pm

#1018634

Thanks Gail

Jim

maechismo_8514 SSCoach Points: 17510 More actions · Answer 10

JC (7/1/2009)
when a database is created i see there are two files .mdf and .ldf

Yes, ".mdf" is the data file and ".ldf" is the log file. Both are as important as each other.

do i keep the .ldf file with the .mdf file for a particular database

It's not mandatory that the ".ldf" file should be placed on the same drive with its ".mdf" file but it is MUST that every ".mdf" file should exist with its corresponding ".ldf" file.

or
do i also move the ldf file for that particular database to its own drive ?
Thanks
Jim

Yes, as a best practice to boost your performance (I/O)

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 11

JC-3113

SSCrazy Eights

Points: 8371

July 1, 2009 at 12:21 pm

#1018642

Thanks Krishna

Jim

bmk086 SSC Enthusiast Points: 126 More actions · Answer 12

but is it ok if we keep both mdf/ldf files on one common storage (LUN partition) in SAN storage?

my db size:15 gb

mdf/ldf LUN Size : 200 GB

ldf LUN size: 100 gb

Jim McLeod SSCarpal Tunnel Points: 4057 More actions · Answer 13

Next time, please create a new thread for a new question.

There's nothing wrong with keeping your mdf and ldf on the same LUN IF there is little IO contention. If you have sufficient memory to keep most of the database in memory, and you make few changes to the data, this may be fine. If you do have an IO bottleneck, then this certainly won't be helping.

I am worried about a 15 GB database with a 100 GB log file! You may want to look into log maintenance and recovery models.

bmk086 SSC Enthusiast Points: 126 More actions · Answer 14

thank you jim! nxt time i will create new thread to discuss more..