- Install, Upgrade, and Maintenance
- FAQs about migration from IBM to AWS
- Troubleshooting Plugin Issues
- Maximum file size of attachments (uploads)
- Database timeout issue
- Targetprocess System Requirements
- How can I update to the latest version?
- How to switch from Targetprocess On-Site to Targetprocess On-Demand
If you host Targetprocess locally, you may notice that it becomes slower after some time, especially when you deal with a large amount of data and number of users, whereas upgrades prove to be infrequent. We would like to share some tips about database maintenance that need to be executed on a regular basis to promote a faster and more reliable performance of the application.
Put simply, we recommend scheduling and running a weekly maintenance plan in SQL Management Studio with 3 tasks: Rebuild Index, Reorganize Index, Update Statistics + another plan to run a specific sql query. Here is how this can be done:
- Open SQL Server Management Studio and start SQL Server Agent if it is stopped
- Expand Management and right-click Maintenance Plans, select New plan
- After naming the plan, drag “Rebuild Index Task” from Tools to anywhere at the blank space under maintenance plan
- Right-click “Rebuild Index Task”, select Edit. Pick the databases you want the task to be executed for:
- Drag “Reorganize Index Task” next to the the existing task, edit it and pick Targetprocess database the same way.
- Drag “Update Statistics Task” to the two tasks you already have, edit it and pick Targetprocess database the same way.
- Clicking a task will display a green arrow that can be used to link the tasks. Link them in the following order: rebuild index -> reorganize index -> update statistics
- Schedule the plan by clicking calendar icon next to sublan. By default it is a weekly run that is executed on Sunday, that should work for most and click Save Selected Items
- Add a new maintenance plan. This one is meant for optimizing the way we store Numeric Priority in the database
- Add just one item there - Execute TSQL Statement. Edit it and add the following query:
USE [TargetProcess] --please replace it with your DB name BEGIN TRANSACTION EXEC disableTriggers UPDATE g SET g.[NumericPriority] = updateRow.[NewNumericPriority] FROM [dbo].[General] g INNER JOIN ( SELECT ROW_NUMBER() OVER(ORDER BY [NumericPriority])[NewNumericPriority],[GeneralID] FROM [dbo].[General] WHERE [NumericPriority] IS NOT NULL ) updateRow ON g.[GeneralID] = updateRow.[GeneralID] UPDATE tctp SET tctp.[NumericPriority] = updateRow.[NewNumericPriority] FROM [dbo].[TestCaseTestPlan] tctp INNER JOIN ( SELECT ROW_NUMBER() OVER(ORDER BY [NumericPriority])[NewNumericPriority],[TestCaseTestPlanID] FROM [dbo].[TestCaseTestPlan] WHERE [NumericPriority] IS NOT NULL ) updateRow ON tctp.[TestCaseTestPlanID] = updateRow.[TestCaseTestPlanID] UPDATE tptp SET tptp.[NumericPriority] = updateRow.[NewNumericPriority] FROM [dbo].[TestPlanTestPlan] tptp INNER JOIN ( SELECT ROW_NUMBER() OVER(ORDER BY [NumericPriority])[NewNumericPriority],[TestPlanTestPlanID] FROM [dbo].[TestPlanTestPlan] WHERE [NumericPriority] IS NOT NULL ) updateRow ON tptp.[TestPlanTestPlanID] = updateRow.[TestPlanTestPlanID] EXEC enableTriggers COMMIT TRANSACTION
- Schedule the maintenance plan (we recommend weekly) and save it.
You could also consider adding a separate maintenance plan with History Cleanup Task and Shrink Database Task. This won’t enhance performance that much, though can help to reduce database size. However, such tasks may bring up some side effects, like making rollback more tricky. We recommend that you use these tasks, if you know what you are doing and have backups configured.
Still have a question?
We're here to help! Just contact our friendly support team