There are situations when the size of the SQL Server Database Transaction Log (.ldf) file becomes very large, occupying a lot of space on the disk. One way is to shrink database files. But when this method fails and there is hardly any change in the size of the .ldf file, then you go with another method. In this method, we delete the transaction log file and create a new file for the database with minimum size.
In this method, first, take a backup of the database and detach the database. Then, we delete the transaction log file and attach the database again.
Given below are the steps to delete SQL Server Database Transaction Logfile: –
Step 1: Open Microsoft SQL Server Database using Windows or SQL Server authentication.
Step 2: Backup the database.
Right-click on a database. Click on Tasks- BackUp.
Step 3: Detach the database.
Right-click on the database and click detach.
Step 4: Delete the log file.
- to the path where a log file is present.
- C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA
Step 5: Attach the database again.
Step 6: Click the Add button in the pop window.
In the Attach box, click ADD.
Step 7: In the pop-up box, choose the database (.mdf) file and click OK. Select (.ldf) file of that database and click REMOVE.
This method is helpful to reduce database size effectively by deleting the old large transactional log file and creating a new .ldf file with minimum errors.