Shrink a transaction log file Maintenance Plan in SQL Server

Posted on May 2, 2016 by Mohamed El-Qassas

The Major factor that faces me when I was trying to create a Shrink a transaction log file Maintenance Plan is the shrink option in Maintenance Tasks that include shrink operation for both data file and transaction log file, and there is not an option for only log file!

In this article, I am trying to perform a shrink operation for only the transaction log file via Maintenance Plan to run on a regular basis or on demand based on your requirement.

Before you start, you should be aware of the following

• To create or manage maintenance plans, you must be a member
  of the sysadmin fixed server role.
• The SQL Server performance will be affected during executing The Shrink operation. Thence, I advise performing the shrink operation out working hours.
• The Shrink operation causes index fragmentation and can slow the performance of queries that search a range of the index. So you should consider rebuilding the indexes to eliminate the fragmentation before the shrink operation has been completed .

Therefore, It’s not recommended to perform the shrink operation periodically! Only in some circumstances that you need to reduce the physical size. (Check the possible responses to a full transaction log and suggested how to avoid it in the future).

Steps:

• Login to SQL Server.

• From Object Explorer > Management > Maintenance Plan Wizard.

• Click Next.

• Specify a meaning name > Click Change to Set Schedule (Because It’s not recommended to perform the shrink operation periodically. Thence I don’t need to build schedule.(only on demand). > Next.

• As I mentioned before, I should consider rebuilding the indexes to eliminate the fragmentation, so the Maintenance Tasks should be (Don’t select Shrink database)
  • Recognize or Rebuild.(Don’t select both)
  • Update Statistic.
  • Maintenance Cleanup Task.

• Leave tasks with the same order.

• Define Rebuild Index task for the database that you need to shrink it’s 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.

• Again, Define Update Statistic task for the database that you need to shrink its transaction log file.(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.)> OK.

• Next.

• On the Define Maintenance Cleanup Task page, set the values that meet your needs (The Maintenance Cleanup task removes files left over from executing a maintenance plan.) > click Next.

• 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 now created successfully > Close.

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

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

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

To add the Shrink log file step follow this instruction :

• Right click on Maintenance plan job > Properties.

• Select Steps Page > Insert.

• Specify step name, Paste the following code based on 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 > Ensure that it’s the first step before all other steps that have already created before in Maintenance plan.

• The following warning will be shown that mean the new shrink log file step will not be added to Maintenance plan diagram > Click Yes.

• Now try to run the maintenance plan on demand, It’s recommended to run it out off the working hours.

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

• Wait till the job has been completed successfully.

The physical log file size should be now reduced, and the index has been rebuilt.

Keep in mind: 

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

*To check the 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.

Enjoy