Shrinking and Managing the SQL Transaction Log File used by RightFax

Follow

Prerequisites:

  • There are SQL db_owner permissions for the RightFax database.
  • RightFax services are all stopped.
  • Sufficient free space is available to perform this task. If the transaction log file (.LDF) is 20 GB, for example, verify that you have at least 20 GB free.
  • There is sufficient time to perform this task. The size of the log file determines how long the process takes. If the RightFax transaction log file (.LDF) is larger than 10 GB, shrinking it may take 30 minutes or more.

Steps:

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.

Step 1: Back Up the SQL Server Transaction Log (.LDF) File:

  1. Open Microsoft SQL Server Management Studio.
  2. Expand the Databases node.
  3. Right-click the RightFax database, and then click Tasks > Backup.
  4. For Backup Type, select Transaction Log, as shown below:
  5. Click OK to start the backup operation.

 

Step 2: Shrink the RightFax Transaction Log File (.LDF): Now that the log file is backed up, you can shrink it. Shrinking the log file removes white space and committed transactions. You can shrink your SQL Server transaction log file using SQL Server Management Studio or at the OSQL command prompt. Instructions for both methods are provided below.

Using Microsoft SQL Server Management Studio (Option 1):

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

  1. Open SQL Server Management Studio, and log on.
  2. Expand Databases.
  3. Right-click your RightFax database, and then click Tasks > Shrink > Files.
  4. Ensure that the file type is Log and the file name is correct.
  5. Click OK when you are ready to make the changes.

 

From an OSQL Command Prompt (Option 2):

At a command prompt, type:

osql -S -U -P 

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

osql -SSQLServer\RightFax -Usa -Ppassword

At 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

 

Preventing the Transaction Log File (.LDF) from Growing Too Large:

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, you can set the Autogrowth/Maxsize of the transaction log file through the Database properties:

  1. Right-click the RightFax database, and select Properties
  2. Select the Files page from the left side, and modify the RightFax_log Autogrowth/Maxsize to preferred settings by clicking the ellipsis:
Have more questions? Submit a request

Comments

Powered by Zendesk