Creating a SQL Server Job to Archive Measurement Data

Summary

This article explains how to create a scheduled SQL Server job that processes measurement archive data by executing a stored procedure.

It includes instructions to create measurement archive procedure dbTools_Archive_Meas_Data in @ptitude Analyst database. It will also provides instruction on how to create a scheduled job in SQL Server to execute procedure on desired schedule.

The primary purpose of this process is to reduce the manual effort required for cloud services to run individually for each database. You can also implement this process, provided they are using SQL Server Standard Edition or higher, which supports the creation of scheduled jobs.

Create dbTools_Archive_Meas_Data procedure

Note: This procedure is automatically created during the database update included in the 
Analyst 9 MR 4 release.

If you are using previous version of Analyst follow the steps below:

1. Locate the dbTools_Archive_Meas_Data.sql file in the database folder.
2. Connect to SQL Server Management Studio (SSMS) using Analyst local user credentials.
3. Create a New Query window and paste the contents of the DbTools_DataCleanup.sql file.
4. Click Execute to compile and save the procedure to the Analyst database.
    

How to execute stored procedure

This procedure is executed without a parameter value. However, parameters are available to enable certain features. The parameters below are used as a default for a scheduled job. 

The following list shows the order in which parameters can be used by the procedure:

1. @IsTest bit = 0,  This parameter controls whether the procedure performs the archive operation or simply displays what data would be deleted:

• Default value 0: Executes the archive process.
• Value 1: Displays the data that would be deleted without performing the archive. 
   

2. @iElementId numeric (38,0) = 0, This parameter allows the procedure to archive data for a specific point:

• Default value 0: Archives data for all Points.
• Value greater than 0: Archives data for the specified Point only, based on the provided element ID.
   

3. @iDisplay int = 0 This parameter controls whether the procedure displays the results of the archive operation:

• Default value (0): Results are not displayed.
• Value 1: Results are displayed.

How to execute procedure from SQL management Studio

1. Connect to SQL Server Management Studio (SSMS) using Analyst local user credentials.
2. Create a New Query window and paste the contents of the dbTools_Archive_Meas_Data.
3. Click Execute.

The statement in the example below, will execute archive in a test mode for PointId 885 with the display option on:

 

Create and schedule Measurement Archive Job   

1. Ensure SQL Server Agent service is running.

• Connect as Server Agent administrator.

• If the service is not running, click the Start button.
  If it cannot be started, check with the database administrator to ensure it is not disabled in Services.

  

2. Connect to database using (SA/Windows Authentication)
3. To create New Job, Right-click on Jobs and select New Job...
   
   
    
4. Under Job Properties, From the menu on the right select General and enter Name, Owner, and select Category.
   
   
    
5. From the menu on the right, select Steps and click New. Then under Command, enter the execute statement. The example below clears data older than 30 days. 
   
   
    
6. From the menu on the right, select Schedules, then click New to open the Job Schedule Properties window. Fill in the schedule details as desired.
   
   

7. Click OK to close the Job.

Contacting SKF Technical Support Group

For further assistance please open a support case using the Technical Support group's self-help portal at www.skf.com/cm/tsg. Once your support case is submitted, a technician will contact you to begin working on your issue. For urgent issues we are available at these times by phone:

• Monday through Friday, 5:00 a.m. to 4 p.m. Pacific Time -
  Phone: [phone] within the US or [phone] outside the US.
• Monday through Friday, 8:00 a.m. to 4:00 p.m. Central European Time -
  Phone: [phone].
• Monday through Friday, 7:30 a.m. to 4:30 p.m. India Standard Time -
  Phone: [phone].