SQL Transaction Log File Shrink Process

The Transaction Log file (LDF) is used in a recovery situation to update the database to the most recent state AFTER the full backup was run. Normally the log file would be a couple of hundred MBs (at the most) because it should be periodically truncating when scheduled transaction log backups are run. If this is not the case it usually means that the transaction log file is not being backed up properly; when a proper transaction log file backup takes place, the file is automatically truncated and starts recording from scratch again. The time and date of the most recent log backup can be easily verified by going into SQL Server Management Studio, right-click on the RFMS database, select "Properties," and look at the “Last Log Backup” field.

While an overgrown transaction log file is a fairly serious issue, it is relatively easy to fix. Please note, RFMS recommends this process be done by a qualified local technician as it makes changes to your database software and there is always a risk of data loss if not handled properly.

To shrink the Transaction Log file:

1. Get ALL users completely out of RFMS as you will be making changes to the database.
2. Make a manual backup of the SQL database before starting so that you can revert if anything goes wrong. Verify the backup has been created before proceeding.
3. Right-click on the RFMS database and select "Properties." Then go to “Options” and change the “Recovery Model” to “Simple.” Click OK and close out of the current properties window.
4. Right click on the RFMS database and go to Tasks > Shrink > Files. IMPORTANT: make sure to select "Files" not the database.
5. Make sure the database name is correct, then set the “File Type” drop-down to “Log.” 
6. Make sure that the bubble for “Release Unused Space” is checked, then click OK, and the shrink process will start.
7. After the shrink process is complete, right-click on the RFMS database and go to "Properties," select “Files” and verify that the transaction log file shows 1MB. 
8. Click on “Options” and change the “Recovery Model” back to “Full.”

While this process will shrink the LDF file, it will NOT remedy the issue - if it is ongoing. Over the coming days monitor the log file (LDF) size to see if it starts to grow again. If it continues to grow, to get to the root of the problem you’ll have to look at whether your SQL backups, for both the Full and Transaction Log, are set up and running correctly on a set schedule.