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

Sanjay Kumawat 20 Reputation points
2023-06-16T14:45:08.2433333+00:00

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

SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. Anonymous
    2023-06-19T05:35:10.65+00:00

    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

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Ronen Ariely 15,206 Reputation points
    2023-06-16T21:42:03.7066667+00:00

    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
    
    0 comments No comments

  2. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2023-06-16T21:45:31.4866667+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.