There are several ways to improve SQL server performance if it’s approaching low disk space or even entirely consumed its disk space. There could potentially be several culprits consuming more space than necessary. This document focuses on Recovery Models and the SQL Transaction Log outlining what you can do to mitigate its disk space consumption.
Please Note: this should only be performed by someone comfortable working with SQL databases.
Full/Simple Recovery Model
If you’re doing frequent backups of your database which is recommended anyway, you can change to Simple Recovery Model to avert unnecessary disk consumption and maintain SQL server performance. Full Recovery Model is used to restore the database to a point-in-time. Since you’ve access to recent backups, you can switch to Simple as a restore point can be obtained from the backup. To reduce the size of a large Transaction Log after you change to Simple Recover Model, you’ll need to shrink the Transaction Log (next section). Here’s how to change to Simple Recovery Model:
- Launch Microsoft SQL Server Management Studio > click Connect
- Expand Databases folder (located in the left pane)
Shrinking Transaction Log
Often, the reason behind this is the Transaction Log Files (.ldf) which keep records of database modifications, are taking up space each time a change is made to the database. It will continue consuming space until there’s no more and causes SQL server performance to suffer. Continue reading to learn how to resolve this. It’s recommended to run this process when users won’t be needed SQL Server functionality.
If Transaction Logs are full, backup the Transaction Log File. During this backup, the SQL server automatically truncates the inactive portion of the Transaction Log File. The inactive portion contains completed transactions, so the transaction log file is no longer used by SQL Server during the recovery process. By default, SQL Server reuses this truncated, inactive space instead of letting the log continue to grow and eat more space. Now that the inactive portion of the log is truncated, here’s how to shrink the Transaction Log:
- Right-click the database name (in SQL Server Management Studio)
- Select Tasks
- Select Shrink
- Select Files
Conclusion
This process may be repeated whenever you notice disk space is being compromised. Please allow time for the log to grow, otherwise, you’ll have nothing to shrink. If you have multiple vaults, this process will need to be repeated on each one by right-clicking on the vault name and repeating this procedure. You may find performing this task has improved SQL performance. If it’s to a level you’re happy with, great! If not, there are additional measures that can be taken. You can clean up/remove old files, distribute the Archive across multiple drives, vault compression, and Cold Storage. I hope this has been helpful. Please contact support if you have any questions.
