Change SQL from Full Recover to Simple Recovery Model

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 article'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.

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’

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.

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

Editor's Note: This article was originally published in August 2017 and has been updated for accuracy and comprehensiveness.