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.
Recommended reading: FogBugz On-Premise Database Maintenance - Cheat Sheet to know more about Microsoft SQL Database maintenance, recovery models and their impact on logging and data restoration in time.
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.
- 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
- 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.
- TRUNCATE_ONLY option
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.
- TRUNCATE_ONLY option
- 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.
WARNING: While this recommendation is not wrong and results in a quick transaction log file size reduction, it does results in losing the transaction records since the last backup. When using this method, we recommend doing a full backup of the database as soon as possible, in case you need to restore data.