Overview

Microsoft SQL Server Database Transaction Log Size can be reduced using different methods. It is important to understand the impact and consequences of using one or the other method.

The used database recovery model (simple, full, bulk-logged) also impacts the transaction log size and how transaction log information is discarded or kept.

Introduction

Explaining the difference between the different log shrinking methods is not FogBugz support team's scope. Though the support team still receives such questions, so we feel obliged to explain this topic to ensure our customers take the best decision when shrinking log files.

Description

Recovery Models

Recovery Models Overview - Microsoft Knowledge Base Article

• Simple - Minimal logging is done. Transaction Log size is minimal. Use this for development or test environments. NOT recommended for production environments.
• Full - Full logging is done. Recommended for production environments. Needed if point-in-time restoration is needed.
• Bulk-Logged - simply put: between Simple and Full. Don't use it if you don't understand its impacts.

Transaction Log Shrinking Methods

Managing the Size of the Transaction Log File - Microsoft Knowledge Base Article

• Shrinking
  • we are referring to the option available in SSMS by Right Click DB Name -> Tasks -> Shrink -> Files -> File type -> Log
  • does reduce the physical log file size by freeing up internal free space of the transaction log
  • not really useful if the freed-up space will be reclaimed pretty quickly in case the Recovery Model is set to Full or Bulk-Logged.
• Truncating
  • TRUNCATE_ONLY option
    • Famous BACKUP LOG <database> WITH TRUNCATE_ONLY
    • Was available only Prior to SQL Server 2008
    • On SQL Server 2008 or later you will get an error: 'TRUNCATE_ONLY' is not a recognized BACKUP option.
  • On SQL Server 2008 and later this can be done by changing the database recovery model to Simple and doing a Shrinking operation as explained above.
• Backing up - Recommended Solution
  • Executing a transaction log backup frees up space within the transaction log file by saving its content to a different storage. To reduce physical log file size, execute a file shrink operation after the log backup.

Often heard recommendation

The default recommendation that often is given on the internet and in many forums is actually: change to the simple recovery model, (truncate your log file - available only on previous SQL versions) and shrink the log file to reclaim the physical space. And, if done in a production environment, as the last step: revert to the full recovery model.

Back to the top

Related Articles

• FogBugz On-Premise Database Maintenance - Cheat Sheet