Creating a SQL Server Job to Archive EventLog Data

Summary

This article explains how to create a scheduled SQL Server job that processes EventLog Archive Data data by executing a stored procedure.

It includes instructions for creating the EventLog archive procedure DbTools_Archive_EventLog_Data in the @ptitude Analyst database. It also provides guidance on how to set up a scheduled SQL Server job to execute the 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.

Important: Before performing this procedure, it is strongly recommended to perform a full backup of the database This ensures data can be recovered if the archiving process encounters issues.

Create DbTools_Archive_EventLog_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. 

The purpose of the parameter is to delete data from the EventLog table that is older than the specified number of days.

The procedure has an internal default value of 45 days, represented as an integer.

How to execute procedure EventLog archive 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_EventLog_Data.
3. Click Execute.

The following statement uses a parameter value of 100 days. When executed in Management Studio, the procedure removes records older than 100 days from the EventLog table. In this example, 6,535 records were deleted, each having a date older than 100 days.

Create and schedule EventLog 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].