Shrink a transaction log file Maintenance Plan in SQL Server

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!

maintinance plan wizard 29

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.

Connect to SSMS

  • From Object Explorer > Management > Maintenance Plan Wizard.

maintinance plan wizard

  • Click Next.

maintinance plan wizard 1

  • 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.

maintinance plan wizard 2

  • 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.

maintinance plan wizard 3

  • Leave tasks with the same order.

maintinance plan wizard 25

  • 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.)

maintinance plan wizard 6

maintinance plan wizard 7

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

maintinance plan wizard 26

  • 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.

maintinance plan wizard 9

maintinance plan wizard 10

  • Next.

maintinance plan wizard 11

  • 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.

maintinance plan wizard 27

  • 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)

maintinance plan wizard 12

  • Click Finish to complete the maintenance plan wizard.

maintinance plan wizard 30

  • The maintenance plan should be now created successfully > Close.

maintinance plan wizard 31

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

maintinance plan wizard 15

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

maintinance plan wizard 28

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.

maintinance plan wizard 17

  • Select Steps Page > Insert.

maintinance plan wizard 18

  • Specify step name, Paste the following code based on your database info.

maintinance plan wizard 19

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.

maintinance plan wizard 20

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

maintinance plan wizard 21

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

maintinance plan wizard 22

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

maintinance plan wizard 23

  • Wait till the job has been completed successfully.

maintinance plan wizard 24

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.

Open Index Physical Statstic Report.png

  • 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.png

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 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s