Shrink a Transaction Log File Maintenance Plan in SQL Server 2016

Shrink Transaction Log File Maintenance Plan in SQL Server 2017

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!

Shrink a Transaction Log File Maintenance Plan in SQL Server

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.
Connect to SQL Server Management Studio
  • From Object Explorer > Management > Maintenance Plan Wizard.
Open maintenance plan wizard in SQL Server
  • Click “Next” to start “Maintenance Plan Wizard”.
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.
maintenance plan properties

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.
maintenance plan tasks

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.
maintenance plan task 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.
Define rebuild index task
select database to rebuild index

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.
Change free space per page

Update Statistics Task in Maintenance Plan

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

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.
Configure Update Statistics Task

Maintenance Cleanup Task in Maintenance Plan

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

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.
Report Options

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

  • Click Finish to complete the maintenance plan wizard.
maintenance plan wizard summary
  • The maintenance plan should be created successfully > Close.
maintenance plan wizard prgress

Check Maintenance Plan Status

  • Go back to Management section to ensure that the Maintenance plan and its job has been created successfully.
maintinance plan wizard 15
  • In Management Section, Double click to check the Maintenance Plan steps.
maintinance plan wizard 28

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.
Maintenance Plan job properties
  • From the left side, click on “Steps” > then click on Insert button.
add new step
  • Specify the step name as you prefer.
Shrink Transaction Log File script
  • 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:
job step list
  • 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

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.
maintinance plan wizard 22

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.
maintinance plan wizard 23
  • Wait a moment until the job is completed successfully.
maintinance plan wizard 24

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.
Open Index Physical Statstic Report
  • As the report name says, it show how the index is physically laid out on data files. Here is how the report looks like:
Index Physical Statstic Report

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
Have a Question?

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

1 thought on “Shrink a Transaction Log File Maintenance Plan in SQL Server 2016”

  1. Hello! Someone in my Facebook group shared this site with us so I came to give it a look. I’m definitely enjoying the information. I’m bookmarking and will be tweeting this to my followers! Wonderful blog and brilliant style and design.

Leave a Reply