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