Updated 'CtsGetClientTaskInstanceRequests' stored procedure, introduced in TASK_7.6_POST_HF7_v1, may cause frequent SQL locks & waits, tasks will start failing.

Products

• IT Management Suite

Issue/Introduction

The Symantec Management Platform can host several types of middleware components, such as package servers, task servers, and boot servers. Middleware components can be installed on computers other than the Notification Server computer. A computer with installed Task Service is generally called a Task Server.  Task Servers extend the architecture, improve distribution efficiency, and reduce network bandwidth requirements. 

'CtsGetClientTaskInstanceRequests' is one of the stored procedures that is used to deliver jobs and tasks to managed computer. Some changes were introduced into the stored procedure starting from TASK_7.6_POST_HF7_v1.zip (http://www.symantec.com/docs/INFO3459). These changes may trigger frequent SQL locks and waits, tasks will start failing.
 

No error messages. Tasks or jobs will get queued and then fail, after a pre-configured timeout value has passed. 

Environment

ITMS 7.6 HF7 with TASK_7.6_POST_HF7_v1 ( - v5).zip installed. 
ITMS 8.0.x.

Cause

SQL stored procedure logic deficiency.

Resolution

ITMS 7.6 HF7 with TASK_7.6_POST_HF7_v1 ( - v5) users do the following steps:
1. Stop all(!) Altiris Services on NS staring from "AltirisSupportService";
2. Stop "World Wide Web Publishing Service";
3. Backup the existing stored procedure via EXEC sp_rename 'CtsGetClientTaskInstanceRequests', 'CtsGetClientTaskInstanceRequests_Backup';
4. Execute 
=====

CREATE PROCEDURE [dbo].[CtsGetClientTaskInstanceRequests] @ClientTaskServerGuid UNIQUEIDENTIFIER
    ,@maxRequests INT = 5000
    ,@allowedEndpointList NVARCHAR(max) = NULL
AS
BEGIN
    IF @allowedEndpointList IS NULL
    BEGIN
        UPDATE TOP (@maxRequests) [dbo].[ClientTaskInstanceRequests]
        SET [ClientTaskServerGuid] = @ClientTaskServerGuid
        OUTPUT INSERTED.[TaskInstanceGuid]
            ,INSERTED.[ResourceGuid]
            ,INSERTED.[Operation]
            ,INSERTED.[TaskVersionGuid]
            ,'Client' AS [InstanceType]
            ,INSERTED.[ParentTaskInstanceGuid]
            ,'00000000-0000-0000-0000-000000000000' AS [JobNodeGuid]
            ,0 AS [InstanceStatus]
            ,- 1 AS [Result]
            ,- 1 AS [Success]
            ,'Server' AS [ParentInstanceType]
        FROM [dbo].[ClientTaskInstanceRequests] ctir WITH (
                UPDLOCK
                ,ROWLOCK
                )
        JOIN [dbo].[Inv_Client_Task_Resources] ctr ON ctr.[_ResourceGuid] = ctir.[ResourceGuid]
            AND ctr.[ClientTaskServerGuid] = @ClientTaskServerGuid
        WHERE ctir.[ClientTaskServerGuid] IS NULL
    END
    ELSE
    BEGIN
        CREATE TABLE #tmpResources (ui UNIQUEIDENTIFIER PRIMARY KEY)

        INSERT INTO #tmpResources
        SELECT DISTINCT ui
        FROM [dbo].fnListToGuidTableDal2InLine(@allowedEndpointList)

        UPDATE TOP (@maxRequests) ctir
        SET [ClientTaskServerGuid] = @ClientTaskServerGuid
        OUTPUT INSERTED.[TaskInstanceGuid]
            ,INSERTED.[ResourceGuid]
            ,INSERTED.[Operation]
            ,INSERTED.[TaskVersionGuid]
            ,'Client' AS [InstanceType]
            ,INSERTED.[ParentTaskInstanceGuid]
            ,'00000000-0000-0000-0000-000000000000' AS [JobNodeGuid]
            ,0 AS [InstanceStatus]
            ,- 1 AS [Result]
            ,- 1 AS [Success]
            ,'Server' AS [ParentInstanceType]
        FROM [dbo].[Inv_Client_Task_Resources] ctr
        JOIN [dbo].[ClientTaskInstanceRequests] ctir WITH (
                UPDLOCK
                ,ROWLOCK
                ) ON ctir.[ResourceGuid] = ctr.[_ResourceGuid]
        WHERE ctr.[ClientTaskServerGuid] = @ClientTaskServerGuid
            AND ctir.[ClientTaskServerGuid] IS NULL
            AND (
                ctir.IsServerTypeTask = 1
                OR ctir.[ResourceGuid] IN (
                    SELECT ui
                    FROM #tmpResources
                    )
                )

        DROP TABLE #tmpResources
    END
END

=====
5. Restart the stopped services and let it run for 5 -10 minutes;
6. Test it with a simple taks, 'run script' for example -> it should complete within minutes on the targeted machines. 

ITMS 8.0.x (up to HF6) users should install a cumulative point-fix for 8.0 HF6 (the cumulative point-fix is not public yet) or you may try the aforementioned steps for ITMS 7.6 HF7.