shrink log on SQL Server

Question

We have an Enterprise geodatabase in SQL Server. The DBAs have reported that the transaction log file is extremely large. The log file should normally shrink after the backup is completed, but since there is a low level of database activity (registered to several ArcGIS Servers, automated ETL processes), the file never shrinks. Has anyone seen this and have a suggestion besides continuing to add more disk space?

FentonCheng_-_DIT · Answer

We wound up temporarily blocking all connections to the database during the backup window and that seemed to work. Your mileage may vary though as it does depend on the use of the database and data size.

DavidEvans5 · Answer

Hey there, I know this post is a few years old, but I'm encountering the same issue and wanted to see if anyone had any recommendations for how to manage the Log file size in SQL. We ran the Shrink function on the Log file in SQL Management Studio and use the "Release unused space" option, but it had a negligible effect on the file size.

We are considering running the "Reorganize pages before releasing unused space" and setting the file size to 20-30% of the database data file size, as described in the SQL Server Transaction Log Administration Best Practices - https://www.sqlshack.com/sql-server-transaction-log-administration-best-practices/ but I wanted to see if anyone in this forum had any experience with this and/or could recommend alternative approaches to managing the Log file size.

Thanks in advance!

SimonAlford · Answer

How is SQL Server configured with regards to Recovery Model, and how are you performing backups?
Here is a good resource to begin your investigation.

SQL Server Transaction Log Backup, Truncate and Shrink Operations - https://www.sqlshack.com/sql-server-transaction-log-backup-truncate-and-shrink-operations/

Quoting from the above resource -  When the database is configured with Full recovery model, the SQL Server Transaction Log in the Transaction Log file will be marked as inactive after committing the transaction, without being truncated automatically, as it will be waiting for a Transaction Log backup to be performed. Recall that only the Transaction Log backup, but NOT the database Full backup, will truncate the Transaction Logs from the Transaction Log file and makes it available for reuse. If no Transaction Log backup is taken from the database, the Transaction Log file will grow continuously, without truncation, until it runs out of free space.