Shrink Log File in SQL Server Using SQL Query – Quick Tutorial

What is a Log File in SQL Server?

Every SQL Server database has two main files:

• .mdf (Primary Data File): This is used to store your actual data (tables, indexes, etc)
• .ldf (Log File):  Records all changes made to the database. This helps SQL Server recover data in case of a crash.

The log file is very important, but sometimes it can grow too large. When backups aren’t being done regularly, or a large transaction was recently run.

Consider Before You Reduce Transaction Log File Size

Shrinking the log file isn’t something you should do as part of routine maintenance. It’s something you should do only in specific cases, such as:

• After a huge one-time operation (like a data import)
• When the log file has grown unusually large and won’t shrink back on its own.

How to Shrink a Log File in SQL Server Using a Query?

In this part, we’ll go over various ways to shrink the size of the log file. 

Method 01: Use T-SQL Command to Shrink Transaction Log in SQL Server

1. Run this given query to reduce the size of the log file. 

USE YourDatabaseName;GODBCC SHRINKFILE (YourLogFileName_Log, 100);  — Shrinks to 100MB

2. Replace YourDatabaseName and YourLogFileName_Log with your actual database and log file names.

Method 02: Use SSMS to Reduce Transaction Log File Size

1. Start by opening SQL Server Management Studio (SSMS) on your computer.
2. Connect to the Server and expand the Management. Right-click on Maintenance Plans.
3. After that, choose New Maintenance Plan from the list and enter its name. Click OK.
4. Then, drag Execute T-SQL Statement Task from the object explorer to the design window.
5. Now, double-click on Execute T-SQL Statement Task to edit the design window.
6. Enter this command:

USE [EPDMdatabase];GOALTER DATABASE [EPDMdatabase]SET RECOVERY SIMPLE;GODeclare @LogFileLogicalName sysnameselect @LogFileLogicalName=Name from sys.database_files where Type=1print @LogFileLogicalNameDBCC Shrinkfile(@LogFileLogicalName,1);GO

7. Further, press OK and go to the calendar icon, define the frequency.
8. Afterwards, click Save and refresh the SQL Server Management Studio.
9. Lastly, again expand the Maintenance plan and execute the Shrink Log file.

Limitations of the Manual Method

The manual method is useful to shrink your Log file. But it contains numerous limitations, such as:

• It might not provide a 100% accurate result.
• Increase the chance of losing your data.
• In some cases, the log file may become corrupted.
• It may also negatively impact SQL Server performance

Method 03: Professional Way to Recover Shrink Log File in SQL Server

If your SQL Database file is corrupted or damaged, you can use the SQL Recovery Tool. This tool is built to restore damaged or corrupted SQL database files. It provides two recovery modes to ensure fast and reliable results. Furthermore, it has a simple and easy-to-use interface, which makes the process more effective even for non-tech users. Also, it is fully compatible with all SQL and Windows versions.

Steps to Recover a Corrupt SQL Database File:

1. Firstly, download the SQL Recovery Tool on your system.
2. Now, click on Browse and add the corrupted database file.
3. Then, choose the Recovery Mode and Scanning Mode as per your needs.
4. After that, preview and select the folder that you want to save.
5. Lastly, click Save to complete the recovery process.

Conclusion

To sum up, a large log file can cause a big and large problem. So, it’s essential to shrink them. Earlier, we looked at two manual methods to shrink log file in SQL Server. If your database file gets corrupted, you can use the professional tool. It can recover your corrupted or damaged database file without any disruption. This tool offers a preview feature to view the recovered data before saving it. Moreover, you can try the free version of this tool to understand its functionality and performance.

Related Post