Nuts and bolts of Certify Database Maintenance

Introduction:

One of the key thing, which is often missed by the organizations, who have invested in using Worksoft Certify for automating their Business Process Validation initiatives, is implementing a Database Maintenance Plan. While the business and the test automation consultants get excited about the shiny new thing that they have got and start building the regression suite; planning and executing a database maintenance plan  for most of the customers gets pushed down the priority list.  However since all the test assets in Certify are stored in a Database, a robust database maintenance plan is very important to maintain smooth operation of Certify with acceptable performance criteria. The customers usually start facing issues once they have built significant number of Certify processes which they have started executing on regular basis. Such executions add a lot of data to the tables storing results data and increase the overall size of the Certify database.Find below the steps that will help you in implementing a Database Maintenance plan for your Worksoft Certify environment.

Worksoft Certify Database Architecture:

Initial Results Cleanup performed  against the Production Database:

Before you Begin

1. From the “…DB Maintenance” folder, read the included document “Tech Note – Worksoft Certify Database Management Kit.pdf”
2. Backup the Certify Production Database (full backup).

Note: Please do not skip the first 2 steps above. They will help you understand what you will be doing next.

Initial Setup

3. Execute dmp_LogTableCleanup_Date_Crt.sql from “…DB MaintenanceDelete ResultsFaster big transaction log” folder in the WorksoftCertify database to install the stored procedure.
4. Execute dmp_LogTableCleanup_Date_Crt_counts.sql from “…DB MaintenanceDelete counts” folder in the WorksoftCertify database to install the stored procedure.
5. Execute dmp_LogTableArchiveByDate_Archive_Crt.sql from “…DB MaintenanceArchive Results” folder in the WorksoftCertifyArchive database to install the stored procedure.

Setup the Archive/Delete Counts Query

6. Copy the contents of “Exec dmp_LogCleanup_Date_Crt.sql” into a new query in the WorksoftCertify database.
7. Change the line “SET @DaysBack = -365” to “SET @DaysBack = -730” to get the row counts for all Log table rows which were created within past 2 years.
8. Change the line “SET @DaysOld = -30” to “SET @DaysOld = -725” to get the row counts for all Log table rows which were created within past 5 days starting from @DaysBack.
9. Change the line “EXEC [dmp_LogCleanup_Date_Crt] @StartDate, @EndDate” to “EXEC [dmp_LogCleanup_Date_Crt_counts] @StartDate, @EndDate” to indicate that you will be using the Counts version of the script rather than the actual deletion version of the script.

Determine the Archive/Delete Counts per Execution

10. Execute the query and make note of the number of rows affected by the query
11. If the number of rows affected by query is less or greater than 20000, change the @DaysOld parameter in the Archive/Delete Counts query in the appropriate direction (either larger or smaller number of days) to modify the query to return approximately 20000 rows.
12. Repeat the previous 2 steps until you have found a date range which will archive/delete approximately 20000 rows and make note of the values. 
13. Take a screen capture of the resulting data (to record the total number of rows which will eventually be archived and deleted).

Perform the Initial Archive/Delete Execution to get timings

14. Created the Archive/Delete execution script (copied from examples in DB Maintenance folders) and modified to get counts first, Archive second, and then delete third.
15. Change the line “SET @DaysBack = -365” to “SET @DaysBack = -730” to start the archive / deletion from 2 years old.
16. Change the line “SET @DaysOld = -30” to the value determined in step 11 to 14.
17. Execute the query and make note of the amount of time taken to run the query for 20000 rows.
18. Given the time for 20000 rows, calculate the number of rows possible to Archive/Delete in 1 hour.

Repeat the Initial Archive/Delete Execution until @DaysOld = -14

19. Given calculated number of rows possible to archive/delete in one hour, repeat steps 11 to 13, substituting the calculated number of rows, to get the range for next 1-hour execution.
20. Adjust @DaysOld in the Archive/Deletion script to value determined in step 20.
21. Execute the Archive/Deletion script to archive and delete the Results.
22. Repeat the above 3 steps until @DaysOld is set to -14.

The next steps are:

• The Archive/Delete execution queries
• Test the Archive/Delete execution queries

The suggested next steps while you have temporary admin rights is to do the following:

1. Use the Counts script to determine a date range that will select approximately 45000 total rows. NOTE: To get next date range, start by setting @StartDate to previous value for @EndDate and adjust @EndDate to find the next 45000 rows. 

(NOTE: Given the timings, you should probably consider multiplying the total number of rows by 10 – i.e. 450,000 rows.)

2. Copy the SET @StartDate and SET @EndDate lines to all the other scripts from the Counts script.
3. Use the Compare script to get the number of Results records matching the data criteria in both the Production and Archive Certify databases (should be 0 in archive, 1 or more in production).
4. Run the archive script to archive the Results matching the date range.
5. Use the Compare script to confirm that both Prod and Archive now have the same number of Results for the date range.
6. Run the delete script to delete the Results which were just archived from the production database.
7. Use the Compare script to confirm that the results were deleted from the production database for the date range. (should be 0 in production, same number showing in archive as was previously displayed in step 3 and step 5 for production)
8. Verify the free space in the Production database has increased (using SQL Server Management Studio, select Properties for the WorksoftCertify production database)
9. Repeat the above steps until the production database free space has increased significantly. 

Nightly maintenance plan for archiving and deleting results:

1. Create a new folder as “1-Recycle Bin” in Results.
2. The nightly Results maintenance plan should delete Results from the “1-Recycle Bin” folder that were moved into this folder 4 days prior to the current date.
   Assumption: Results moved into this folder do not need to be archived, just deleted.   NOTE: Please confirm this assumption is correct.
   Recommendation: Delete results from 1-Recycle Bin folder with a Created Date older than 7 days or a Modified Date older than 4 days. This maintenance plan should be scheduled to run before the regular Archive/Deletion maintenance plan.
3. The nightly Results maintenance plan should archive and delete all Results older than 7 days based on the Created Date. 
   Assumption: Results table size should be maintained at a steady size to prevent “Query timeout” errors. 
   Recommendation: As our investigation found that the number of Results rows are doubling every 15 days, and the database errors begin appearing. It is recommended all results over 15 days old should be archived and deleted.

We at SOAIS have helped multiple customers plan and implement robust Database Maintenece Plan for their Worksoft Certify environment. Feel free to get in touch with us at info@soais.comContribution by Kumar Saurabh