What are resons for ldf file full? what we need to do if ldf file is full?

Question

Why ldf file full how we can trouble shoot if it is full

Answer 1

Hi @Sanjay Kumawat

There are many reasons why log files fill to extreme sizes. By far the most common situation is that the database is in full recovery mode and the transaction log backups are not coming fast enough, or not happening at all. Next, it may be that you have a large transaction, such as a large data import, rebuilding all indexes, etc. These will be recorded and remain there until the .ldf file is backed up.

You can refer to the following links to manage transaction log file size.

https://learn.microsoft.com/en-us/sql/relational-databases/logs/manage-the-size-of-the-transaction-log-file?view=sql-server-ver15

https://dallasdbas.com/why-is-my-sql-log-file-huge/

Best regards,

Aniya

Answer 2

Hello @Sanjay Kumawat and welcome to the QnA forum

What are resons for ldf file full?

I am not sure what you mean by "full" but I can explain to you what is the the .ldf files and what it is used for.

Before we start, it is highly important to understand the the file extension name has no meaning.

You can use any file name that you want for each type of file the database uses, but there are some names which are default or most used.

Each database must have at least one data file and one log file. This first data file is considered as the primary data file and any addition data file is considered as secondary data file.

• By default and most common extension which used for the primary data file is .mdf
• By default and most common extension which used for the secondary data file is .ndf
• By default and most common extension which used for the transaction log file is what you asked about .ldf

So, the short answer to your question is that an .ldf is probably the transaction log file for the database.

For more information about the names of the files related to your database, you can read this doc:

https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

Notice that I am saying "probably" since as mentioned above, It's not a hard law, just a convention if you do not explicitly set a different name.

In the following doc you can learn how to create a new database and if you want than you can set the name of the files. If you do not set the name of the files then you get a default name.

https://learn.microsoft.com/sql/t-sql/statements/create-database-transact-sql?view=sql-server-ver16&tabs=sqlpool&WT.mc_id=DP-MVP-5001699

So what is the transaction log file and why we need it?

Let's bring the official doc explanation: Every SQL Server database has a transaction log that records all transactions and the database modifications made by each transaction. The transaction log is a critical component of the database. If there is a system failure, you will need that log to bring your database back to a consistent state.

And to understand more, you can read the full document here:

https://learn.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver16&WT.mc_id=DP-MVP-5001699

Update: adding example

use master
GO

-- convention names
CREATE DATABASE [Funny_Extension_Names]
 ON  PRIMARY ( NAME = N'Funny_Extension_Names', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\DATA\Funny_Extension_Names.mdf')
 LOG ON 
( NAME = N'DB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\DATA\Funny_Extension_Names.ldf')
GO

-- Using anny name that you want for the file extension
CREATE DATABASE [Funny_Extension_Names]
 ON  PRIMARY ( NAME = N'Funny_Extension_Names', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\DATA\Funny_Extension_Names.RonenPrimaryDatafile')
 LOG ON 
( NAME = N'DB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\DATA\Funny_Extension_Names.RonenLogfile')
GO

Answer 3

Short answer to short question: You look at the column log_reuse_wait_desc in sys.databases for the database in question and then act accordingly.