How to maintain the DISTRIBUTIONTASKLOG Table

Version 3

    Question

     

    I noticed that the DISTRIBUTIONTASKLOG table is growing bigger and bigger and it's taking a lot of space of my DB. How can I maintain it?

     

    What is the DISTRIBUTIONTASKLOG Table?

     

    You can follow the instructions below to create a maintenance plan to delete all scheduled tasks that have not run in 45 days.

     

    The table DistributionTaskLog reports log information past tasks    Older entries may not still contain worthwhile information.

     

    How to maintain the size of it by implementing a new SQL Maintenance Plan?

     

    1. Open SQL Server Management Studio and connect to the SQL instance where the LDMS database resides

     

    IMPORTANT!: You need to login with an account having a Sysadmin right in order to access to the Maintenance Plan tool

     

    2. Follow the instructions given in this link Create a Maintenance Plan

    3. Add a new T-SQL task to the plan which will delete, in this example, all logs older than 45 days;

     

    DECLARE @deleteOlderThan Int 
    --DECLARE @taskSQL nvarchar(MAX) 
    Set @deleteOlderThan = 45 
    --Set @taskSQL = 'Select LD_TASK_IDN from LD_TASK where LAST_START < GETDATE() - @deleteOlderThan and CONSOLEUSER_IDN != 1' 
    
    delete from TaskBnfMacros where LD_TASK_IDN in (Select LD_TASK_IDN from LD_TASK where LAST_START < GETDATE() - @deleteOlderThan and CONSOLEUSER_IDN != 1) 
    delete from taskfiles where LD_TASK_IDN in (Select LD_TASK_IDN from LD_TASK where LAST_START < GETDATE() - @deleteOlderThan and CONSOLEUSER_IDN != 1) 
    delete from DistributionTaskLog where LD_TASK_IDN in (Select LD_TASK_IDN from LD_TASK where LAST_START < GETDATE() - @deleteOlderThan and CONSOLEUSER_IDN != 1) 
    delete from ScheduledQueries where LD_TASK_IDN in (Select LD_TASK_IDN from LD_TASK where LAST_START < GETDATE() - @deleteOlderThan and CONSOLEUSER_IDN != 1) 
    delete from ApmCommandLines where LD_TASK_IDN in (Select LD_TASK_IDN from LD_TASK where LAST_START < GETDATE() - @deleteOlderThan and CONSOLEUSER_IDN != 1) 
    delete from TaskPolicy where LD_TASK_IDN in (Select LD_TASK_IDN from LD_TASK where LAST_START < GETDATE() - @deleteOlderThan and CONSOLEUSER_IDN != 1) 
    delete from LD_TASK_MACHINE where LD_TASK_IDN in (Select LD_TASK_IDN from LD_TASK where LAST_START < GETDATE() - @deleteOlderThan and CONSOLEUSER_IDN != 1) 
    delete from LD_TASK_MACHINE where LD_TASK_IDN in (Select LD_TASK_IDN from LD_TASK where LAST_START < GETDATE() - @deleteOlderThan and CONSOLEUSER_IDN != 1) 
    delete from LD_TASK_MACHINE where LD_TASK_IDN in (Select LD_TASK_IDN from LD_TASK where LAST_START < GETDATE() - @deleteOlderThan and CONSOLEUSER_IDN != 1) 
    delete from TaskAllowedMachines where LD_TASK_IDN in (Select LD_TASK_IDN from LD_TASK where LAST_START < GETDATE() - @deleteOlderThan and CONSOLEUSER_IDN != 1) 
    delete from TaskContent where LD_TASK_IDN in (Select LD_TASK_IDN from LD_TASK where LAST_START < GETDATE() - @deleteOlderThan and CONSOLEUSER_IDN != 1) 
    delete from LD_Task where LD_TASK_IDN in (Select LD_TASK_IDN from LD_TASK where LAST_START < GETDATE() - @deleteOlderThan and CONSOLEUSER_IDN != 1)