An Introductory View of .ldf File and its location In SQL Server

The Microsoft SQL Server database is the relational database which is used to store, retrieve the database information using SQL statements (Insert, Update, Delete etc.).

In order to maintain the information in database SQL Server uses database objects like Table, View, Index etc. To carried the information stored in database objects, SQL Server uses two main different file formats i.e .mdf and .ldf.

The .mdf file which is the primary database file of SQL Server database, stores the database information in physical Order. In order to store the database entries of physical file (.mdf) in a logical way, SQL Server uses transaction log file (.ldf).

In addition, to store the information of a huge database, the SQL Server provides a secondary database file i.e. .ndf for the further storage. This is an optional file format used by SQL Server to spread data across primary and secondary database files. In this article, we will discuss the SQL transaction log file and learn the different methods for viewing the location of SQL .ldf file.

Important Note: Users can also try a third-party application as well i.e. SQL LDF Viewer to read LDF files in an efficient way.

What is .ldf File and What Information it Holds?

The .ldf file is a transaction log file used in SQL Server database to store the transactional information of each entry made on primary database file (.mdf). As the .ldf file holds very critical information of a database, it can also be used recover the database in case of any system failure or can be helpful in bringing the database in the healthy state. The directory structure of database files of each SQL Server version depends on its instance.

In order to view the default location of .ldf file in different SQL Server database, one can go through below mentioned path:

SQL Server 2005:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

SQL Server 2008:

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\

SQL Server 2008 R2:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\

SQL Server 2012:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\

SQL Server 2014:

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\

SQL Server 2016:

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\

So, going through the above-mentioned path one can find the location of data and log files on a particular SQL Server database.

Note: SQL Server uses the same drive for both .mdf and .ldf files just because of both files have to make changes at the same time, which helps in reducing the amount of time on writing both files.

How to View .ldf file in SQL Server ?

Except going through the root directory, to view the location of the .ldf file in SQL Server, there are some more helpful commands and Database settings page to view the default location of a database file. So, to know where are the database files located by SQL Server, we will follow the below methods:

• Command to View the SQL Server Log File Location:

To view the exact location of available databases on SQL Server one can try the below command:

SELECT name, physical_name AScurrent_file_locationFROM sys.master_files

After running the above command, the SQL user can find the current file location of the available data and log files as highlighted in above screenshot.

Finding the location of a particular database log file

Suppose we have a database with the name of My database and we need to find the location of its log file without going through the directory. So, one can go through the below script to view the exact path of a particular database log file.

SELECT filename

FROM sys.sysaltfiles

where name like ‘My Database_log%’

by using the Where clause with Like operator, one can easily find where the particular database log file located.

View the Location of .ldf file using SQL Server Management Studio

Another method that an SQL user can use to view SQL database files is by using SQL Server Management Studio. This method helps to view the default location of database files.

Please follow the below-mentioned steps using SQL Server Management Studio:

1. Connect to the SQL Server.
2. Right-click on the Instance name.
3. Click on Properties.
4. Under the Select a page click on Database Settings to see where the SQL database files are located.

The above-mentioned steps will help you in finding the default location of SQL .mdf and .ldf database files using SQL Server Management Studio.

Conclusion:

In this article, we took a small look over SQL Server database and understand what is it and why it is used for. Mainly we have tried to explain about the database files (.mdf and .ldf) of SQL Server database and explained what is .ldf file in SQL database and what information it holds, Also learned how to view .ldf file in SQL Server with different methods. Hope this article will helpful for beginners to know about SQL Server and its important database files.