This article is the second of a two-part series. The first part outlines step-by-step direction on setting up a minimal maintenance plan.
By the way, this post contains a LOT of information so grab a cup of coffee, put your feet up and get to reading. Or, print it out for review at your leisure!
EDITING THE MAINTENANCE PLAN
As before, the first steps are to start SQL Server Management Studio, then log in to SQL Server as a database administrator. (This is what gives you access to the SQL Server databases).
As shown in the image below, the next step is to expand the Management Group and the Maintenance Plans subgroup. Right-click the existing maintenance plan and select Modify.
With the basic maintenance plan defined in part 1 , we can add the other tasks needed to keep your database healthy.
First on the list is to define an “Update Statistics Task”. Siemens does recommend that Teamcenter databases be configured with “Auto Update Statistics” enabled, and I agree with this method. However, I also like to set it so that the index statistics are updatedbefore doing maintenance on the indexes, which will come after this task.
Just like in Part 1, drag-and-drop the task onto the maintenance plan window and define its properties. First things first, select the database. In my example, I select “tctr”.
Next, add a “Rebuild Index Task”. This is not required, however it is an important task for the long-term health of the database. In my example, I again select the “tctr” database. The “Object” will be “Tables and Views”, and I use “Reorganize pages with the default amount of free space”.
First, add two “Maintenance Cleanup” tasks to clear out old backup files. The time frame chosen will depend on your company standards and what enterprise backup plan you have in place. For my example, I rely on the enterprise backup plan to copy my backup files or off-site storage. This means that I don’t need to retain those files for a long period of time.
In the example below, I have defined a Cleanup Task to delete my database backup files that are more than 3 weeks old. Having previously selected the option of placing the backups in a subfolder, I just select that subfolder in the Maintenance Cleanup Task form. Please notice that I have selected the folder specifically, I did not enable the option to “include first-level subfolders“. Doing it this way means that this cleanup task will not affect other backups or maintenance plans that may be defined on this server.
We’re finally at the last step. Save your changes and sit back and wait for the maintenance plan to run. Or, run it manually by right-clicking on your chosen maintenance plan and selecting “Execute”. I will typically run a new or modified maintenance plan once manually just to be sure all is working as intended.
And with that, you are done! With the above steps you can automate the maintenance of your Teamcenter database on SQL server to perform the database maintenance tasks recommended in the Best Practices guide. In my experience, this really does help to keep your installation healthy with very minimal effort.
Thanks for reading!
Oh, and one more thing: Make sure that you check your SQL Server and Maintenance Plan logs on a regular basis to make sure they have run successfully. To do this, right-click your Maintenance Plan and select “History”. There you will find logs of the maintenance execution tasks. You can expand the logs to see the actual results of each task.