PDM Vault Maintenance Documentation from SOLIDWORKS

The following article includes several SOLIDWORKS knowledgebase solutions concerning setting up maintenance plans for your PDM Server.

• S-022452 - What is the recommended procedure to make a backup of an existing SOLIDWORKS® PDM vault?
• S-077744 / QA00000123611 Is there a SolidPractice available on the topic of SOLIDWORKS® PDM "Backup and Restore Procedures"?
• S-07794 / QA00000123483 - How can I prevent the Microsoft® SQL database transaction log file ('.LDF') for a SOLIDWORKS® PDM database from growing unexpectedly in size and how do I shrink a large log file size?
• S-060559 / QA00000114864 - What are the steps to manually change the size of the data files that the Microsoft® SQL Server uses for tempdb?
• S-069601 - In the SOLIDWORKS® PDM environment, how do I maintain or reduce the size of the ‘tempdb’ database?
• S-042552 / QA00000108683 Using SOLIDWORKS® PDM Professional, what is the recommended procedure to rebuild the table indexes in a file vault database using a maintenance plan in Microsoft® SQL server?
• S-069363 / QA00000120028 - How do I schedule backups of the SOLIDWORKS® PDM Standard file vault databases that are hosted on Microsoft® SQL Server Express Edition?
• S-072048 / QA00000121565 - How do I schedule maintenance tasks to rebuild table indexes for SOLIDWORKS® PDM Standard file vault databases that are hosted on the Microsoft® SQL Server® Express edition?

S-022452 - What is the recommended procedure to make a backup of an existing SOLIDWORKS® PDM vault?

The best practice for making a complete backup of an existing SOLIDWORKS PDM vault is to backup the three major components of the vault:

1. The SQL Database.
2. The Archive Vault files.
3. The registry settings for the vault.

All three should be made at the same time to keep the vault synchronized in the event they are needed to restore the vault from a backup.

The SQL Database is backed up through the SQL Server Management Studio tool. For completeness, backup both the vault database and the Conisio master database.

The vault archive files are located at C:\Program Files\PDMWorks Enterprise\Data by default. Each vault can be backed up individually using any ZIP program (WinZip, WinRAR, 7-Zip, etc.). Note that the location of the archives may vary depending on install procedure.

The registry settings can be backed up by using the SOLIDWORKS PDM Archive Server configuration tool.

See more details in the SOLIDWORKS PDM installation guide, chapter "Backing Up and Restoring File Vaults".
PDM installation guide

S-077744 / QA00000123611 - Is there a SolidPractice available on the topic of SOLIDWORKS® PDM "Backup and Restore Procedures"?

The attached SolidPractice document describes factors that influence the backup and restore processes for the SOLIDWORKS PDM Professional product set. The document discusses possible error scenarios that can occur when restoring not-synchronous server components, and how to fix a few of these errors. The document first describes details about the SOLIDWORKS® PDM Professional architecture including:

• Backup strategies for the database, archives, and other parts of the system.
• How to set up a test environment for practicing restore scenarios.
• Information about how to address some inconsistencies that can arise while restoring not-synchronous server components.

There is no single strategy that applies in each setup. However, an understanding of all of the options available will help you to define the best strategy for your specific requirements.

The intention of this document is to expand and enhance this information over a period of years and versions to incorporate feedback from the field experience about the most useful areas.

Source: https://support.3ds.com/knowledge-base/?q=docid:QA00000123611

S-07794 / QA00000123483 - How can I prevent the Microsoft® SQL database transaction log file ('.LDF') for a SOLIDWORKS® PDM database from growing unexpectedly in size and how do I shrink a large log file size?

This solution is only valid for non-replicated databases. Replicated databases require full recovery mode.

In Microsoft® SQL Server, all newly created databases (e.g. a SOLIDWORKS® PDM database) will have the recovery model option set to 'Full'.

With the full recovery model, regular backups of the individual transaction log must be performed to prevent the transaction log file size from growing out of proportion to the database size. If the transaction log file grows to fill the disk and you run out of disk space the SQL server will not be able to perform any data modification operations to the vault database.

The transaction log file is usually located under 'C:\Program Files\Microsoft SQL Server\MSSQL.MSSQLSERVER\MSSQL\DATA' next to the database file and has a '.LDF' file extension For example 'Engineering_log.LDF'.

If you rely on daily full database backups, we recommend that that the recovery is set to 'Simple' instead to limit the file size growth of the database transaction log (.LDF).

To check the recovery model of a database, do as follows:

1. Start Microsoft SQL Server Management Studio,
2. Log in to the SQL server as a system administrator.
3. Expand the 'Databases' folder.
4. Right-click the database to check and select 'Properties'.
5. Under 'Options', view the 'Recovery Model' setting.

After you set the recovery model to simple for a database, you can shrink the current transaction log file to regain space:

1. Start Microsoft SQL Server Management Studio,
2. Log in to the SQL server as a system administrator.
3. Expand 'Databases',
4. Right-click the database that has the transaction log you wish to shrink and select 'Tasks' > 'Shrink' > 'Files'.
5. 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.
6. Click 'OK' to shrink the log file.
   - If the recovery model is simple the free space should now be regained.
   - If the recovery model is full you likely must first back up the transaction log file before you can shrink the file.

Please refer to the SQL Server documentation for more information about the transaction log and recovery options.

Source: https://support.3ds.com/knowledge-base/?q=docid:QA00000123483

S-060559 / QA00000114864 - What are the steps to manually change the size of the data files that the Microsoft® SQL Server uses for tempdb?

As with other physical storage used by the SQL Server®, the files used by tempdb can automatically grow to meet demand, however there are performance implications if this process is left unmanaged.

The attached PDF document explains how tempdb files behave, how to measure them and how to establish a suitable size for them so that performance can be optimized.

The changes made will become active when SQL Server is restarted.

Source: https://support.3ds.com/knowledge-base/?q=docid:QA00000123483

S-069601 - In the SOLIDWORKS® PDM environment, how do I maintain or reduce the size of the ‘tempdb’ database?

The Microsoft® SQL Server® setup process creates the ‘tempdb’ system database automatically. The ‘tempdb’ database file is temporary and works as a kind of page file for SQL Server. SQL Server clears and recreates the file each time the SQL Server service starts.

The’ tempdb’ database is essential to normal SQL Server operation. Internal system-level SQL Server processes and user-generated requests use the database extensively. The ’tempdb’ database grows over time and in some cases becomes very large.

For information about how to reduce the size of the ‘tempdb’ database, read the attached PDF file.

S-042552 / QA00000108683 - Using SOLIDWORKS® PDM Professional, what is the recommended procedure to rebuild the table indexes in a file vault database using a maintenance plan in Microsoft® SQL server?

Many tables in the SOLIDWORKS® PDM file vault database contain indexes – to ensure data is found efficiently when lookups are done by various database operations. Microsoft® SQL server automatically maintains these indexes whenever insert, update, or delete operations are made to the underlying data. Over time as you perform a lot of file operations in the vault - for example after an upgrade where a lot of records may have been modified, or performed a migration where many new records were added - the various database table indexes becomes scattered. This is similar to how a hard drive can become fragmented. You can update and refresh the indexes by running a maintenance plan to rebuild or reorganize the indexes. If the indexes are very fragmented, this operation may improve general performance with searching, browsing etc. in the file vault.

SOLIDWORKS recommends rebuilding the file vault database indexes at least once a month to improve overall performance. In cases of larger databases and more activity - more frequent rebuilds such as once per week may prove beneficial. The attached PDF document explains how to use Microsoft SQL Management Studio to create a maintenance plan to schedule rebuilding of indexes.

Please note that Microsoft SQL Express that is used for SOLIDWORKS PDM Standard databases does not have maintenance plan support. Please refer to KB solution S-074245 for details how to rebuild indexes in SQL Express.

NOTE: At no time should new custom indexes be manually created in a SOLIDWORKS PDM database without consent from SOLIDWORKS technical support.

Source: https://support.3ds.com/knowledge-base/?q=docid:QA00000108683

S-069363 / QA00000120028 - How do I schedule backups of the SOLIDWORKS® PDM Standard file vault databases that are hosted on Microsoft® SQL Server Express Edition?

You must host the SOLIDWORKS® PDM Standard software vault databases on an instance of the Microsoft® SQL Server Express database software. It is important that you make daily backups of the file vault databases and their archives.

The SQL Server Express edition is essentially a "free" version of the SQL Server database software. The SQL Server Express database software does not include the SQL management tools or the SQL agent that comes with the full version of SQL Server. Therefore, there are no pre-existing maintenance plans or automated backup options. However, there are many solutions available to handle database backups. Here are a few:

• If you already have software running on the server that performs recurring file backups, investigate if that software also supports backing up SQL databases. Most professional backup solutions have add-ons that support backing up live SQL databases. The SOLIDWORKS Technical Support team does not recommend backing up the .MDF and .LDF database files directly because the SQL Server Service keeps the files in an open state.
• An easy solution is to use a batch script to launch the backup and then use Windows Task Scheduler to run the backup every day. For detailed instructions about how to set this up, See the Microsoft Knowledge Base article KB2019698 “How to schedule and automate backups of SQL Server databases in SQL Server Express” at https://support.microsoft.com/en-us/kb/2019698 
• Search the Internet for backup solutions that support the SQL Server Express database software. There are several free alternatives.

Additionally here is our Webinar recording of the process.

This video can help with understanding the Microsoft Article on creating a task to backup SQL. 

Source: https://support.3ds.com/knowledge-base/?q=docid:QA00000120028

S-072048 / QA00000121565 - How do I schedule maintenance tasks to rebuild table indexes for SOLIDWORKS® PDM Standard file vault databases that are hosted on the Microsoft® SQL Server® Express edition?

The Microsoft® SQL Server® Express edition is essentially a free lightweight version of the SQL Server database software. Like the full version of SQL Server, it is essential that you perform routine maintenance tasks on databases in the SQL Server Express instance. This is necessary because over time, fragmentation occurs in the indexes for the SOLIDWORKS® PDM vault database. Fragmentation can negatively affect performance. As a best practice, you should periodically rebuild or reorganize of the indexes.

Unlike the full version of SQL Server, the SQL Server Express database software does not include the SQL management tools or the SQL agent. Therefore, there are no pre-existing maintenance plans available (see QA00000108683 for details about maintenance plans.) However, you can use a SQL script to perform maintenance tasks manually as needed.

To rebuild all table indexes in a database, load the attached SQL script QA00000121565.sql into SQL Server Management Studio and run the script on the file vault database.

For more information about reorganizing and rebuilding database indexes, read this Microsoft TechNet article: https://technet.microsoft.com/en-us/library/ms189858.aspx 

To run the script as a scheduled task, you can run the script from the Windows Task Scheduler app as suggested in these articles:

https://www.haurumit.dk/how-to-automate-sql-server-express-database-maintenance-using-windows-task-scheduler-and-ola-hallengrens-maintenance-solution/ 
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html 
Always back up a database before performing maintenance or making changes.

For information about how to schedule a backup of a SQL Server Express database, see QA00000120028.

Source: https://support.3ds.com/knowledge-base/?q=docid:QA00000121565

For further technical support, please contact Hawk Ridge Systems at 877.266.4469 for the U.S. or 866.587.6803 for Canada and support@hawkridgesys.com.