The TaskOutputPropertyValue table is growing very large

Products

• IT Management Suite

Issue/Introduction

In the SQL database, the TaskOutputValueProperty table is growing very large.

Environment

ITMS 8.x

Cause

When a Task is run, the outcome of the Task is stored in this table. Each task run creates at least 4 records to hold the outcome of the Task.

Examples of the contents of this table are included below.

Resolution

Run the following SQL query to get a general idea of the Tasks being run that are generating a majority of the output in the table.

select tpv.*, ti.taskname from taskoutputpropertyvalue tpv
join evt_task_instances ti on ti.taskinstanceguid = tpv.taskinstanceguid
order by taskname

Select one of the following options:

• Decrease the frequency of these Task's run schedules.
• Implement policies to accomplish the objective of repeated Tasks.
• Verify that the "Clean Up Task Data" task is running as required.  If it is holding too much data please lower the row count and the time Task output is kept, according to the needs of your environment.
  • Location: Settings > Notification Server > Task Settings > Clean Up Task Data

Additional Information

KB: The TaskOutputProperty table is getting very large, how can we clean it up? - 176114

The following are some suggested queries to identify possible areas that need some cleaning:

--Find the tasks that we want to delete data for:
Select 
  i.Guid
 ,i.Name
 ,SUM (DATALENGTH (topv.Value)) Size
 ,count (topv.TaskInstanceGuid) 'RowCount'
from TaskOutputProperty topv
left join TaskInstances ti on ti.TaskInstanceGuid = topv.TaskInstanceGuid
left join ItemVersions iv on iv.VersionGuid = ti.TaskVersionGuid
left join Item i on i.Guid = iv.ItemGuid
group by i.Name, i.Guid
order by 3 desc

--Put the data we want to keep into a table: 
select *
into TEMP_TaskOutputProperty
from TaskOutputProperty topv
where TaskInstanceGuid in
 (
  Select topv.TaskInstanceGuid
  from TaskOutputProperty topv
  left join TaskInstances ti on ti.TaskInstanceGuid = topv.TaskInstanceGuid
  left join ItemVersions iv on iv.VersionGuid = ti.TaskVersionGuid
  where iv.ItemGuid not in ('D05A9560-426D-4AB4-B9A1-CD8C9B7C0B8F') --add GUID to delete e.g. 386038C7-060D-420F-BC20-A16D8C43C91F
 )

--Validate the data is there before we truncate
select * from TEMP_TaskOutputProperty

--Truncate the table
Truncate table TaskOutputProperty

--Put the data we kept back into the original table
Insert into TaskOutputProperty
select * from TEMP_TaskOutputProperty

--Validate the data is back in the original table
select * from TaskOutputProperty

--Drop the table we created to hold the data
drop table TEMP_TaskOutputProperty

--truncate the TaskOutputProperty table:
Truncate table TaskOutputProperty