Reduce the size of a large database log file in SQL Server

Introduction

We recently came across a situation where one of the drives in the production server is almost full. There are two large databases and they were not backed up. No backup job or no maintenance plans were in place. That drive is filled by the log files of these two databases (~200GB each). This has happened because the databases were in full recovery mode but transaction logs are not backed up. As a result, log file grew and grew till it occupied maximum space available in the drive.

When we spotted this situation, we changed the recovery mode over to Simple hoping that would reduce the log file size.  However, the log file was still present, was still growing. Even though we are in Simple Recovery mode.

Steps to solve this issue

Here are the steps to solve this issue:

  1. Make a full backup of the databases (Not required but as a precaution)
  2. Set the recovery model to simple
  3. Check any open transaction is going ON or NOT
  4. Check log backup is required or not before log file shrink
  5. Shrink the log file
  6. Check log file space
  7. Set the recovery model back to full
  8. Take a full backup
  9. Setup a job to backup transaction log frequently

Note: Shrinking the log file is not a good option to do it regularly. I had to use the shrinking option because this is one-off and the log file has grown that big is due to lack of log backups.

Full backup

We can take full backup either by using TSQL script or by SSMS.

By TSQL

Result

By SSMS

Right-click on the database > Task > Back Up…

General > Destination > Back up to: > Disk > (Remove the path if any) > Add

Destination on disk > File name > Click on 3 dots (…) then select the destination folder & provide a name with ‘.bak’ extention. [Example: F:\SQL DATABASE\Gama\Our_Tech_Ideas_FullBackup.bak]. Then click ‘OK’

Media Option > As per requirement choose ‘Oerwrite media’ & Reliabiliy then Click ‘Backup Option’

Backup Option > Compression > Set backup compression as ‘Compress backup’ then click ‘OK’

Backup started, check the status on bottom left corner.

Database backup is successfully done. Click ‘OK’

Set the recovery model to simple

By TSQL

Result

By SSMS

Right click on database > Properties

Option > Recovery model

Recovery model > Full to Simple

Click ‘OK’

Check any open transaction is going ON or NOT

By TSQL script

Result

Or to check open transaction we may use below script also

The result will be shown in the below format. As we don’t have any open transaction so its showing blank.

Check log backup is required or not before log file shrink

By TSQL script

Result

If ‘NOTHING’ then shrink activity can be done
If ‘LOG_BACKUP‘ then log backup must be taken before shrink activity
If ‘OLDEST_LOG‘ then full backup must be performed, then log back can be take before shrink activity

Shrink the log file

By TSQL

First, check the name of the log file

Result

In our case ‘name’ is AdventureWork2016_Log

TSQL script to Shrink the log file

By SSMS

Right-click on database need to shrink > Task > Shrink > Files

General > File type

File type > Log

Shrink action > Select 2nd option & provide the value as per requirement. Click ‘OK’

Check log file space

By TSQL

Result

Set the recovery model back to full

By TSQL

Setup a job to backup transaction log frequently

The final step to create a job or maintenance plan to take transaction log backup. We will show step by step process in our next post. Click Here

 


Comments

Leave a Reply

Your email address will not be published. Required fields are marked *