Servertaskinstancerequests table has invalid data

Products

• Task Server

Issue/Introduction

The server taskinstancerequests table has invalid data. This data could be a large number of rows, old tasks or a combination of both.

No specific error is returned from this. Usually you will see server tasks not executing. To verify that there is a problem with the Servertaskinstancerequests table do the following:

1. Create a Run script on server task
2. Add the syntax "dir"
3. Schedule the task

If the task does not run within 5 minutes then there is a problem.

Environment

ITMS 8.x

Cause

There are many things that can cause problems with this table. The primary cause is from solutions adding tasks that never finish or overlap each other and cause the tasks to lock up.

This can manifest in one of two ways:

1. Too many server task instances in the servertaskinstancerequests table
2. Expired task instances in the servertaskinstancerequests table

Resolution

This table holds temporary data. The only time data should be in this table is when there are active (currently running) server tasks.

The quick fix is the following: (If you want to diagnose the issue then do this last)

1. Stop the atrshost and dataloader services
2. Run the following SQL statement :
   truncate table servertaskinstancerequests
   exec tmCleanupSummaryOrphans
   exec tmCleanupTaskOrphans
   
3. Start the atrshost and dataloader services

This will flush the server task instances and usually get things working again.

To diagnose the issue you will need to look at what is executing and if it has a valid task instance. Run the following SQL statement:

Select count(*) from servertaskinstancerequests

This will give you the number of rows in the table. Take note of this number. It is normal to only have one to five rows in this table. Next run:

select * from servertaskinstancerequests stir
join taskinstances ti
on stir.taskinstanceguid = ti.taskinstanceguid

Again. Take note of the number of rows. If there was any decrease in the number of rows then there are invalid tasks in the servertaskinstancerequests table. At this point you will not be able to tell what kind of tasks they are. You can continue to troubleshoot and see what tasks are in the servertaskinstancerequests table by running the following:

select i.name, count (*) as count from servertaskinstancerequests stir
join taskinstances ti
on stir.taskinstanceguid = ti.taskinstanceguid
join itemversions iv
on ti.taskversionguid = iv.versionguid
join item i
on iv.itemguid = i.guid
group by i.name
order by count DESC

This query will show you what server tasks are running that have a valid task instance. If you have too many of one kind of instance there may be a problem with that particular solution. Look at any related tasks or policies and see if you can adjust them as needed. Once you have found what kind of tasks are in there it is safe to truncate the table and restart the services.

Advisory: Please view the listed tables that can also be truncated outlined on KM: KB 152933 if further bloated tables exist in the database.