The most important consideration is to pick the right database recovery model that meets the business needs. If you're using a recovery model of full then you also need to backup the log on an appropriate frequency. There's also a question of scale here. If your log is 32 GB in size for a 10 GB database then I understand why you're considered. However, if that log file is for a 10 TB database then the right thing to do might be to allocate more space for the transaction log files. Be careful in how you think about the log files. It's a good thing that protects against data loss. It shouldn't be viewed as your enemy that should shrunk as much as possible.
With all of that said, there may be different ways of writing code which lead to the same results but reduce total writes on the transaction log file. Taking advantage of possible optimizations in this space can improve performance. Some high level tips:
- If your business needs allow you to change the recovery model to simple then some code may qualify for minimal logging.
- There may be an opportunity to change code to use temp tables instead of user tables. Tempdb always has a recovery model of simple so you can get minimal logging for those operations.
- Your application design may allow for the use of a scratch database to hold temporary data, such as a staging database. Your business requirements may allow the staging database to have a recovery model of simple while keeping the recovery model of the real database as full.
- Getting data Right The First Time can reduce log writes. For example, a single INSERT instead of an INSERT + UPDATE will often write less to the log. Same concept applies to avoiding operations which delete large numbers of rows, if possible.
- SQL Server acquired locks need to be written to the transaction log as well. In high scale scenarios you may see an improvement by adding
TABLOCK hints to reduce transaction log activity.
