SQL CHECKDB Saved My EPDM Vault
/Since 2001 I have been using SolidWorks Enterprise PDM, previously known as Conisio, and I have had a vault that I have kept upgrading since version 5.3. Since the move to version 6, the upgrade process for EPDM has been quite simple. I would go as far as saying it might be the simplest product to keep up to date in the entire product line.
Typically the update involves running the installer which is not a "patch" but a complete standalone installation of the new version. The installer detects what is installed and allows you to hit "Upgrade", essentially seek and remove/install the new version. This is done on the clients as well as the server(s) and the last step is to run the Update tool (located in the Update folder of the install source) which updates the SQL database with the necessary changes for the new version.
In all the updates I had done, never have I ran into an issue performing an upgrade of the software or the database. This past week I was upgrading my vault, mentioned above, and the patch to EPDM 2011 SP2 went as expected but upon running the Update tool, I ran into the following error:
If you are ever faced with this issue, there are a number of solutions in the SolidWorks Knowlege Base to assist you in finding a place to start. If you check the logs in the Administration tool you might be able to find out what the issue is and correct it. Typically while the Upgrade tool is running, a progress bar displays at the bottom and when an error is discovered, a message will appear informing you an error has occurred and to look at the next screen for details. In my case, there was no error reported. The KB had solutions and SPR against similar issues, however none of them had exactly the issue I was experiencing.
The archive server log showed an issue accessing the item in the database, which did have some specific SPR and Solutions associated with it but the details were not the same. Looking in the SQL logs there was a corruption in the database itself and showed the last query ran along with the error it generated. Running the same query did indeed error out as indicated in the logs. Due to the age of this vault, it didn't surprise me if there was some debris in the tables that might be causing some issues with the automated script tools that are required to run.
Since I backed up, as you all SHOULD be doing prior to updates, I decided to try a few tricks to see if rebuilding the database would fix the update issues.
In order to perform a rebuild, the vault database must be placed in a "Single User Mode" and then told what level of rebuild you would like to perform. I ran the following:
/*Select database "Demodata"*/Use DemodataGO/*Switch to Single User Mode to perform Repair/Rebuild*/ALTER DATABASE Demodata SET SINGLE_USERGo/*Run Database Check in Repair/Rebuild mode*/DBCC CHECKDB('Demodata', REPAIR_REBUILD)GO/*Switch back to Multi-user Mode for use again*/ALTER DATABASE Demodata SET MULTI_USER
There are different levels of the CHECKDB command, some of which can be much more destructive and can corrupt or destroy the database so MAKE A BACKUP...consider yourself warned! For more information about the rebuild command you can check out MSDN for more information.
Once I performed the rebuild, I re-ran the Update.exe tool for Enterprise and the update took without errors. This is the first time I have had a database not update without error but if you upgrade enough vaults, you are bound to run into an error or two! ~Lou
