Shrink a Transaction Log File Maintenance Plan in SQL Server 2016

When I tried to create a “Shrink a transaction log file Maintenance Plan in SQL Server”, I have noted that the shrink option in Maintenance Tasks includes a shrinking operation for both data file and transaction log file, and there is not an option for only log file!

Therefore, In this article, we’ll concentrate to perform a shrink operation for only the transaction log file as a Maintenance Plan to run on a regular basis or on demand based on your requirement.

---

Shrink a Transaction Log File Maintenance Plan in SQL Server

Before we getting started, you should first be aware of the following:

• To create or manage Maintenance Plans in SQL Server, you must be a member of the sysadmin fixed server role.
• The SQL Server performance will be affected during executing the Shrink operation. Thence, we advise you to schedule the Shrink a Transaction Log File Maintenance Plan in SQL Server to be running out working hours.
• The Shrink operation causes index fragmentation and can slow the performance of queries that search a range of the index. therefore, you should consider rebuilding the indexes to eliminate the fragmentation before the shrink operation has been completed.

As per the above notes and recommendations, It’s not recommended to perform the shrink operation periodically. and you should perform the shrink operation only in specific circumstances. in case, you need to reduce the physical size and you can’t perform one of the possible responses to a full transaction log.

You might also like to read The transaction log for database SharePoint_Config is full due to LOG_BACKUP

---

SQL Shrink Transaction Log Maintenance Plan Steps

In this section, we’ll list detail steps to Shrink a Transaction Log File Maintenance Plan in SQL Server.

Steps

• Login to SQL Server.

• From Object Explorer > Management > Maintenance Plan Wizard.

• Click “Next” to start “Maintenance Plan Wizard”.

Set Maintenance Plan Properties

• Specify a meaning name for your Maintenance Plan as you prefer.
• Check “Single schedule for the entire plan or no schedule”.
• Click on the “Change” button to set the task schedule > Next.

As we earlier mentioned, It’s not recommended to perform the shrink operation periodically. so you don’t need to build a schedule and run it “On Demand”

Specify Tasks in Maintenance Plan

• In “Maintenance Tasks, you should consider rebuilding the indexes to eliminate the fragmentation, so the Maintenance Tasks has the following tasks: (Don’t select Shrink database)
  • Recognize or Rebuild. (Don’t select both)
  • Update Statistic.
  • Maintenance Cleanup Task.

Don’t select Shrink database Maintenance task because this option will shrink the data and log file.

• Leave the Maintenance Plan tasks with the same order.

Rebuild Index Task in Maintenance Plan

• Define Rebuild Index task for the database that you would like to shrink its transaction log file.> OK.

This process drops the existing Index and Recreates the index.

• Below Free space options > Check Change free space per page with 80 %.
• Below Advanced options > Check Sort results in tempdb >Next.

Update Statistics Task in Maintenance Plan

• Again, Define Update Statistic task for the database that you need to shrink its transaction log file > OK.

The Update Statistics task ensures the query optimizer has up-to-date information about the distribution of data values in the tables. This allows the optimizer to make better judgments about data access strategies.

• Click “Next” to continue.

Maintenance Cleanup Task in Maintenance Plan

• On the Define Maintenance Cleanup Task page, set the values that meet your need> click Next.

The Maintenance Cleanup task removes files left over from executing a maintenance plan

Report Options in Maintenance Plan

• Specify the report path > Check email report if you need to receive a report.

Note: you should configure email setting in SQL to can send report by email

• Click Finish to complete the maintenance plan wizard.

• The maintenance plan should be created successfully > Close.

Check Maintenance Plan Status

• Go back to Management section to ensure that the Maintenance plan and its job has been created successfully.

• In Management Section, Double click to check the Maintenance Plan steps.

Shrink Transaction Log File in Maintenance Plan

Now, the Maintenance Plan has been created with only Rebuild ,Update Statistic, and Maintenance Cleanup Task so What about the Shrink log file step?!

Actually, the Shrink log file task will be added manually to avoid shrinking for data file.

Steps

• Right click on Maintenance Plan job > Properties.

• From the left side, click on “Steps” > then click on Insert button.

• Specify the step name as you prefer.

• Paste the below SQL query with your database info.

use sharepoint_config
go
alter database sharepoint_config set recovery simple
go
dbcc shrinkfile('SharePoint_Config_log',100)
go
alter database sharepoint_config set recovery FUll
go

Note: 100 is called the target_size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created.

• Click OK to close the step dialog.
• Make sure that the Shrink step is listed as the first step in the job step list as shown below:

• The following warning will be shown which means the new shrink log file step will not be added to Maintenance plan diagram.

The following job steps cannot be reached with the current job step flow logic

• Click Yes to close the warning dialog.
• Now try to run the maintenance plan on demand.

As we earlier mentioned, It’s recommended to run this Shrink Maintenance Plane out off the working hours.

• Again, make sure that the Shrink step before other steps > Click Start.

• Wait a moment until the job is completed successfully.

Shrink a Transaction Log File Maintenance Plan Considerations

You should be aware of the following:

• The maintenance plan should include either index reorganization or index rebuilding; not both.
• The maintenance plan should never include shrinking a database.
• The Maintenance Cleanup task removes files leftover from executing a maintenance plan.
• The index rebuilding process uses more CPU and it locks the database resources. So I advise running the maintenance plan out of the working hours.

Check Index Fragmentation

• Right Click on Database > Reports > Standard Reports> Index Physical Statistic to check index fragmentation.

• As the report name says, it show how the index is physically laid out on data files. Here is how the report looks like:

Note : In Some cases, Even if you rebuild the index, it would still recommend Rebuild in operation to recommend column,So Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40% based on Index Physical Report.

---

Conclusion

In conclusion, we have learned how to create a “Shrink a Transaction Log File Maintenance Plan in SQL Server“.

Applies To

• SQL Server 2012.
• SQL Server 2014.
• SQL Server 2016.
• SQL Server 2017.

You might also like to read

• The transaction log for database SharePoint_Config is full due to LOG_BACKUP
• Create a LOG_BACKUP Maintenance Plan in SQL Server.
• BACKUP LOG cannot be performed because there is no current database backup in SQL Server.
• Extend SQL Server Evaluation Period.
• SQL Server Best Practices for SharePoint 2019.
• SQL Server: Get the Detailed Information Via SERVERPROPERTY.
• SQL Server: How to get the current installed update level.

Have a Question?

If you have any related questions, please don’t hesitate to ask it at deBUG.to Community.