Post reply

infinite recompile: Remote Rowset Changed

  • April 8, 2016 at 8:45 am

    #309779

    Had an application start timing out yesterday, it's a simple report. while restarting SQL fixed the problem, I'm trying to figure out what exactly happened:

    The error shows:

    A possible infinite recompile was detected for SQLHANDLE 0x03000C000B44523E2E6999009BA500000100000000000000, PlanHandle 0x05000C000B44523E40C1EE94000000000000000000000000, starting offset 492, ending offset 2310. The last recompile reason was 6.

    The last recompile reason was 6: which is Remote Rowset Changed

    But can't seem to find what exactly 'Remote Rowset Changed' means in relation to this problem. The SP receive's two params retrieves a simple report:

    Thank you

    DECLARE @bdate VARCHAR(12)=null,

    @edate VARCHAR(12)=null

    DECLARE @StartDate DATETIME,@EndDate DATETIME

    SET @StartDate = @bdate + ' 12:00:00 AM'

    SET @EndDate = @edate + ' 11:59:59 PM'

    SELECT

    TE.Office,

    CASE WHEN BO.Office IS NULL THEN 'Admin' ELSE 'Support'

    END AS EmpType,

    su.Username AS NTAuth,

    dbo.FirstCaps(ISNULL(unv.FullName,su.Username)) AS

    UserName,

    MIN(Date_Time) as FirstIn,

    MAX(Date_Time) as LastOut,

    CONVERT(NUMERIC(18,2),DATEDIFF(mi,MIN(Date_Time),MAX(Date_Time)) / 60) AS ShiftDur

    FROM v_login_logout_all su

    LEFT JOIN Server.DB.dbo.unv_Employee unv ON unv.NTAuth COLLATE DATABASE_DEFAULT = su.Username

    LEFT JOIN Tracked_Emps TE on TE.Username=su.Username

    LEFT JOIN Admins_Office AD on AD.Username=su.Username

    LEFT JOIN Back_Office BO on BO.Username=su.Username

    WHERE su.Date_Time BETWEEN @StartDate AND @EndDate

    --AND ComputerName NOT LIKE 'JVM%'

    GROUP BY TE.OFFICE,unv.FullName,su.Username,dbo.DTO_V(Date_Time),BO.Office,AD.Office

    ORDER BY TE.Office, UserName,dbo.DTO_V(Date_Time)

  • April 8, 2016 at 8:59 am

    #1870835

    The LEFT JOIN Server.DB.dbo.unv_Employee very much looks like 4-part naming referencing a linked server. If so, that will likely be the remote rowset referred to.

    Check to see what changes to schema, permissions, etc. might have been changed for that remote table.

    Cheers!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply