Change SQL from Full Recover to Simple Recovery Model

Article by GoEngineer on Aug 10, 2017

SOLIDWORKS PDM utilizes SQL as its database engine where one database file (.mdf) and at least one transaction log file (.ldf) is created.  The database file stores the physical data added to the database, and the transaction log keeps records of database modifications. By default, the recovery method of a SQL database is set to ‘Full’.  With Full recovery model set, a transaction log can grow until it is out of disk space which can make your Microsoft SQL Server database unusable.  The following document will walk you through step by step on how to change your database recovery mode from ‘Full’ to ‘Simple’.

Considerations

  • This document's sole purpose is to supplement the SOLIDWORKS backup and restore procedures outlined in the SOLIDWORKS PDM Installation guide not replace.
  • Using Simple Recovery, the database can be recovered to the point of the last full or differential backup. The database cannot be restored to the point of failure or to a specific point in time.
  • With Full Recovery, the database can be recovered to the point of failure or to a specific point in time. If Full Recovery is used, then make sure the database files (MDF and LDF) are backed up on a regular schedule.
  • If relying on daily full database backups, we recommend the recovery model is set to ‘Simple’ to limit the file size growth of the database transaction log (.LDF).
  • The transaction logs are very important for maintaining the transactional integrity of the database. Do not delete the transaction log files, even after you make a backup of your database and the transaction logs.

Process

1. Check the current Recover Model settings of your database:

  • Log in to the SQL server as a system administrator.
  • Start Microsoft SQL Server Management Studio.
  • Login using a Local system administrator account or the ‘sa’ user account.
  • Expand the ‘Databases’ folder.
  • Right-click the database to check and select ‘Properties’.
  • Under ‘Options’, view the ‘Recovery Model’ setting.
SQL Full Recover to Simple

2. Take a Database backup of your PDM Vault before making any changes. Backup steps can be found in the SOLIDWORKS PDM Installation guide under the ‘Backing Up and Restoring File Vaults’ section.

3. If the Recovery model is set to ‘Full’ you can select the dropdown arrow and change to ‘Simple’

Recovery Mode to set Full

4. Select ‘OK’ to accept the change.

5. Restart the SQL server PDM Instance.

6. After you set the recovery model to ‘Simple’ for a database, you can shrink the current transaction log file to regain space:

  • Start Microsoft SQL Server Management Studio.
  • Expand ‘Databases’.
  • Right-click the database that has the transaction log you wish to shrink and select ‘Tasks’ > ‘Shrink’ > ‘Files’.
  • In the ‘File Type’ droplist, select ‘Log’. The dialog will show how much free space is available.

    -Note that it is not recommended to shrink the actual database file.

  • Click ‘OK’ to shrink the log file.

    – If the recovery model is simple the free space should now be regained.

Recovery Model Simple

Note: If you have any questions concerning this document please contact our GoEngineer support.

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.