April 8, 2016 at 8:45 am
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
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