SQL Maintenance for SOLIDWORKS PDM Standard

The SQL backbone is a highly organized structure that allows SOLIDWORKS PDM to operate quickly and efficiently. Over time, this indexed structure can get itself out of order and reduce its efficiency. SQL Standard provides maintenance plans to help combat this. However, SQL Express does not have these automation or maintenance options. To perform this maintenance the rebuild and reorganizations must be done manually per table. With a little creative scripting, we can still automate this as well. 

Rebuilding and Reorganizing T-SQL 

For us to automate this we first need to generate some T-SQL. SOLIDWORKS gives us a leg up on this part by providing a procedure to check what the current fragmentation of all tables are and by design rebuilds or reorganizes them based on this fragmentation level. This isn’t necessarily ideal in terms of what we would need to do regularly. With some modifications we can split this script into two; one for rebuilds and another for reorganization.  

The first part of the script is to build a temporary table and we’ll borrow this from SOLIDWORKS Knowledgebase article S-074949. This bit of code will go in both our rebuild and reorganization scripts. Create a new query in SQL Management Studio and paste this code in. This code will build a temporary table that identifies the index fragmentation of the PDM tables. This will be used to determine which tables need which form of maintenance.   

To create our reorganizing script we’ll make an addition after the temporary table. It uses the table to reorganize any table with a fragmentation greater than 5% but less than 30%. Less than 5% is not necessary and greater than 30% requires a rebuild which the other script will cover. 

In a second script for rebuilding, we’ll put the temporary table script and follow it up with a second edit of the script provided by SOLIDWORKS. This will take care of any table with fragmentation above 30%. 

Save and close these when finished. We’ll rely on Task Scheduler and a batch script to execute them. 

Automating with Task Scheduler and a Batch Script 

The first step to automating is creating batch scripts to run the T-SQL through a command prompt. This is done with a simple command that we’ll use in the two batch scripts. Start by creating a text file and saving it as “Reorganize.bat” and then another as “Rebuild.bat”. The code will only vary between the two for which SQL script file we are pointing to. 

We then need to open the Windows Task Scheduler. We’re going to create two basic tasks to run on schedules. The schedule will be to run the Reorganize.bat weekly and the Rebuild.bat monthly. It is best to pick a day and time where there won’t be any activity in the vault. In the Task Scheduler go to Action > Create Basic Task. 

On the first page, we will give the task a name and hit next. The trigger for this task will be either weekly (for the reorganize) or monthly (for the rebuild). We’ll also need to specify which day of the week or month and a time. Pick a day and time where there should be no activity in the vault to avoid any issues. 

The action for the task will be to start a program. Click browse and select the appropriate batch script. After this, we can hit finish and repeat the process for the other script. The health of your PDM Standard database will be maintained automatically now. 

Learn more about SOLIDWORKS PDM 

SOLIDWORKS PDM Standard Automated SQL Backup

Moving the SOLIDWORKS PDM Vault Database Yourself? Start Here

SOLIDWORKS PDM Upgrading SQL 2014 to Service Pack 3

SOLIDWORKS PDM - How to Reset the SQL 'SA' Password

SOLIDWORKS PDM - Backup SQL