Niraj Bhatt – Architect's Blog

Ruminations on .NET, Architecture & Design

What is .LDF – SQL Server?

I was quite surprised to see search engines throwing very less information on this one, yet it’s a frequently asked question. LDF acronym stands for Log Data File (though could have been called – Transactional Log Data File). This is a must to have file, and is created at the same time the database file (.mdf) is created by SQL Server.

So why do you need it? For keeping .mdf consistent. To cut short I will copy these lines straight from wiki :
“SQL Server ensures that any change to the data is ACID-compliant, i.e., it uses transactions to ensure that any operation either totally completes or is undone if fails, but never leaves the database in an intermediate state. Using transactions, a sequence of actions can be grouped together, with the guarantee that either all actions will succeed or none will. SQL Server implements transactions using a write-ahead log. Any changes made to any page will update the in-memory cache of the page, simultaneously all the operations performed will be written to a log, along with the transaction ID which the operation was a part of. Each log entry is identified by an increasing Log Sequence Number (LSN) which ensure that no event overwrites another. SQL Server ensures that the log will be written onto the disc before the actual page is written back. This enables SQL Server to ensure integrity of the data, even if the system fails. If both the log and the page were written before the failure, the entire data is on persistent storage and integrity is ensured. If only the log was written (the page was either not written or not written completely), then the actions can be read from the log and repeated to restore integrity. If the log wasn’t written then integrity is also maintained although the database state remains unchanged as if the transaction never occurred. If it was only partially written, then the actions associated with the unfinished transaction are discarded. Since the log was only partially written, the page is guaranteed to have not been written, again ensuring data integrity. Removing the unfinished log entries effectively undoes the transaction. SQL Server ensures consistency between the log and the data every time an instance is restarted.”

Are there any other uses of .LDF? Yes they can be used for Log Shipping, a high availability solution.

What are the recommended optimizations for LDF file? LDF could be stored on a separate disk having a dedicated disk controller in high volume scenario. Growth of LDF should be managed as daily maintenance activity as it can easily eat up the available storage capacity. A 2 disk RAID 1 is also a good trade off for LDF is terms of performance vs. failure protection (if you are able to invest money on disks RAID 10 is good for MDF (faster read)).

Hope that helps you get started🙂 .

4 responses to “What is .LDF – SQL Server?

  1. Ken Hoopes September 1, 2009 at 6:07 am

    Great Article!!!

    I’m just starting to get my feet under me, in terms of SQL understanding (although they are still wobbely) and was surprised myself to find little information on this.

    My scenario, and the reason for my search was to identify if there were performance gains to having the .ldf files on a seperate volume. I believe that answer would be yes, so long as it is a seperate physical volume, containing its own HDD controller.

    Any advice?

  2. nirajrules September 1, 2009 at 8:55 am

    Yeah Ken, that should improve performance. I would suggest to measure the disk I/O load you are currently having, and then if required move LDF to its own volume with dedicated controller. Also if you are using a SAN box this can be easily done with a variety of RAID configurations available there.

  3. Kaushal September 21, 2010 at 1:18 pm

    Nice Article Niraj, Finally I unde.
    Hey, is there any way to restore data using LDF only. If database, and table structures are available ???

  4. Pingback: Accpac and SQL Server « Stephen Smith's Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: