Select to view content in your preferred language

shrink log on SQL Server

1360
1
02-27-2023 01:57 PM
FentonCheng_-_DIT
Emerging Contributor

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?

0 Kudos
1 Reply
SimonAlfordEsriAU
Esri Contributor

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 Operationshttps://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.