Back to series

This video discusses Shrinking the SQL Server Transaction Log (.LDF) File to Prevent Excessive Growth on the RightFax database.
This video was recorded at 1280 by 720 resolution for High Definition and is best viewed in full screen mode.

In RightFax 9.0 and later, the RightFax database is located in Microsoft SQL Server. If the RightFax database log (.LDF) file becomes too large, SQL Server performance may deteriorate. In general, you should keep your transaction log file to less than 4 GB to avoid performance issues.

Before starting, there are a few required prerequisites:

Microsoft SQL Server uses the transaction log file (.LDF) for each transaction. The log file is useful for data recovery. To recover data, you can restore a known good backup of the database, and then apply any transactions that took place since your last backup was taken by applying the log file.

Before shrinking the SQL Server database, back up the log file to mark all transactions as committed so they are freed up during the shrink operation.

First, let's back Up the SQL Server Transaction Log (.LDF) File:

Open Microsoft SQL Server Management Studio.

Expand the Databases node.

Right-click the RightFax database, and then click Tasks > Backup.

For Backup Type, select Transaction Log.

Click OK to start the backup operation.

Once the database is backed up we can start the process to shrink the RightFax transaction log file. Shrinking the log file removes white space and committed transactions.

Using Microsoft SQL Server Management Studio (Express):

If you do not have SQL Server Management Studio installed, you can download it from Microsoft.

Open SQL Server Management Studio, and log on.

Expand Databases.

Right-click your RightFax database, and then click Tasks > Shrink > Files.

Ensure that the file type is Log and the file name is correct.

Click OK when you are ready to make the changes.

You can also shrink the log file from an OSQL command prompt by connecting to the SQL server with your username and password using the following pattern:

osql -S -U<username> -P<password>

For example, to connect to SQL Server as the "sa" user with a password of "password," enter the following:

osql -SSQLServerRightFax -Usa -Ppassword

Once connected, from the >1 prompt, type the following, and press ENTER after each line:

USE rightfax
DBCC SHRINKFILE("RightFax_Log", 1)
BACKUP LOG RightFax WITH TRUNCATE_ONLY
DBCC SHRINKFILE("RightFax_Log", 1)
GO

NOTE: In SQL Server 2008 and 2012 the BACKUP LOG as TRUNCATE_ONLY feature was deprecated and is not recognized as a backup option. The simple recovery model should be used to automatically truncate the transaction log.

In conclusion as previously mentioned, for best performance you should ensure that the RightFax transaction (.LDF) log file remains smaller than 4 GB. The larger it grows over 4 GB, the more likely you are to experience performance issues.

To keep the log file in check, perform regular backups of both the RightFax database (.MDF) file and transaction log (.LDF) file, and then shrink the transaction log file.

You can use the SQL Server Agent to schedule backups. However, the free version of SQL Server Management Studio (the Express version) does not include SQL Server Agent. You require a paid version of SQL Server to schedule backups.

As always, if you have any questions about this topic, or any topic in the world of faxing, let us know at The Fax Guys, we are always happy to help.

This is Jay Campbell with The Fax Guys for RightFax University saying thank you for watching and Please visit us again. The RightFax university is a work under continual progress and we update often.