SQL Recovery Model and Transaction Log Management

Article by GoEngineer on Feb 13, 2017

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)
sql server manangement studio
  • Right-click the name of your database > scroll to and select Properties
sql server properties
  • Select Options (located in the left pane of Properties window)
sql server properties window
  • Select Simple in the Recovery model dropdown list
simple recover model drop down
  • Click OK

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
Select files sql server
  • Select Log in the File Type dropdown list (Shrink file dialogue box)
select log sql server
  • Click OK

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.

About GoEngineer

GoEngineer delivers software, technology and expertise that enable companies to unlock design innovation and deliver better products faster. With more than 35 years' experience and tens of thousands of customers in high tech, medical, machine design, energy and other industries, GoEngineer provides best-in-class design solutions from SOLIDWORKS CAD, Stratasys 3D printing, Creaform & Artec 3D scanning, CAMWorks, PLM, Altair, and more

View all posts by GoEngineer

Subscribe

Get the latest articles delivered daily to your inbox, unsubscribe at any time.