Home > How-to Guides > How to Create Maintenance Tasks for EPG in SQL Express Installations

How to Create Maintenance Tasks for EPG in SQL Express Installations

This article applies to the following products:

  • Messageware EPG 2019
  • Messageware EPG 2016
  • Messageware EPG 2013

 

SUMMARY

 

This guide will cover how to manually assign batch files in Task Scheduler to execute SQL based scripts to perform daily health and maintenance tasks in order to maintain good performance on the EPG database on regular basis when the SQL Server Agent is not available in SQL Express versions.

 

Breakdown of the key items:

  • SQL scripts will target the current EPG database to perform maintenance tasks
  • Batch files will contain a command line syntax to execute the SQL scripts
  • Tasks will launch the batch files (at scheduled times) through Task Scheduler in Windows

 

SYMPTOM

 

All versions of SQL Express will not be able to utilize the SQL Server Agent to schedule a job, task, operation, alert as the component is not available for installation.  This is an expected limitation (among others) of the free SQL Express software.

 

SOLUTION

 

Due to the SQL Express limitation of not having the SQL Server Agent available to perform scheduled jobs/task within SQL, the alternative solution is to use the Task Scheduler in Windows to schedule and execute the required EPG maintenance jobs.

 

Perform the following steps within the SQL server where the EPG database is installed.

 

Update the SQL Scripts

  1. Create or assign a folder location where the scripts will be saved on the SQL server.  This is to keep the files in a known location for safe keeping.
    • Example: \Program Files\Messageware\EPGMaintenance\

 

  1. Each script will need to be saved to a single file.  This is so the two jobs don't run simultaneously and allow time for each task to complete.
    • Open Notepad in Windows and paste the contents of each script
    • Update each script (highlighted within brackets) with the name of the EPG database
    • When finished, save the file with the assigned filename

 

Use[Enter EPG Database name here]

SET DEADLOCK_PRIORITY 10

Truncate table tblUserHistory

GO

Filename: Cleanup.sql

Use[Enter EPG Database name here]

SET DEADLOCK_PRIORITY 10

exec sp_Enfoce_LifeSpanFor_SuccessIP

GO

Filename: EPGMaintenanceJob.sql

 

Create Batch Files

  1. Open Notepad and copy the following command

sqlcmd -S servername -i folder\filename.sql

    • Command options are case sensitive
    • Adjust the servername with the name of the SQL server (and instance if applicable)
    • Adjust the folder\filename.sql with the full folder and file name path
  2. Save the file as BAT at your designated location
  3. Repeat this step until both batch files have been created

Examples of command lines:

sqlcmd -Ssqlserver -i C:\ Program Files\Messageware\EPGMaintenance\EPGCleanupjob.sql

sqlcmd -Ssqlserver\sqlexpress -i \\EPGMaintenance\EPGMaintenancejob.sql

 

Note: sqlcmd should be bundled with every installation of SQL Server.  The utility can also be obtained from the Microsoft links below where you can find additional information.

 

sqlcmd Utility (2016 and higher)

 

sqlcmd Utility (2014 and older)

 

Create task in Task Scheduler

 

  1. Launch the Task Scheduler in Windows
  2. In the menu, click Action
    • Select Create Task…
  3. In the General tab
    • Name - Assign a meaningful name to the task. (Keep in mind two jobs will need to be created for EPG) Example. EPG Cleanup Job or EPG Maintenance Job.
    • Description – (optional) Write a description of the job
    • Assign a local or domain admin account that Task Schedular will execute the task on behalf of the account
    • Select radio button "Run whether user is logged on or not"
    • Check the box "Run with highest privileges"
    • Configure for – Select your matching Windows server level
  4. In the Triggers tab
    • Click New…
    • Begin the task – Select "On a schedule"
    • Select Daily with an overnight start time of 03:00 AM to recur every "1" days
    • In Advanced settings, verify "Enabled" is checked. Other options are not necessary at this time.
    • Click Ok when done
  5. In the Actions tab
    • Click New…
    • Actions – Select "Start a program"
    • Programs/script – Click Browse and navigate to the file location of a BAT file for execution
    • Click Ok when done
  6. In the Conditions tab
    • No modifications are necessary and defaults will be used
    • "Start the task only if the computer is on AC Power"
    • "Stop if the computer switches to battery power"
  7. In the Settings tab
    • No modifications are necessary and defaults will be used
    • "Allow task to be run on demand"
    • "Stop the task if runs longer than 3 days"
    • "If the running task does not end when requested, force it to stop"
  8. Clicking Ok will finalize the creation of the task and a basic authentication window will pop-up
    • Enter the password of the assigned account in step 3

 

Note: If the basic authentication window prompt doesn't show, then the task will fail due to invalid credentials.

Additional note: If modifying a task, some parameters may not take affect and the task will need to be re-created.

 

  1. Return to step 2 and create the second scheduled task to include the other BAT file
  2.  Verify the tasks executes as expected
    • Navigate to Task Scheduler Library
    • Right-click on one of the newly created tasks
    • Select Run, this will immediately execute the task
    • Double-click on task and navigate to History tab
    • This will retrieve the current logged data of the task. To refresh the log, close and re-open the task.
    • Verify "Event 201 Action completed " is present. This means the task was successfully executed with the provided credentials.
    • The results can also be verified in the EPG database as each script will clear the contents of the following location:
      1. Cleanup.sql - EPGdatabase/Tables/dbo.tblUserHistory
      2. EPGMaintenanceJob.sql -EPGdatabase/Tables/dbo.tblSuccessfulIP

 

Updated: