CREATE PROCEDURE [dbo].[CleanExpiredSessions]
@SessionsCleaned int OUTPUT
AS
SET DEADLOCK_PRIORITY LOW
DECLARE @now as datetime
SET @now = GETDATE()
CREATE TABLE #tempSession
   (SessionID varchar(32) COLLATE Latin1_General_CI_AS_KS_WS,
    SnapshotDataID uniqueidentifier,
    CompiledDefinition uniqueidentifier)
INSERT INTO #tempSession
SELECT TOP 20 SessionID, SnapshotDataID, CompiledDefinition
FROM [ReportServerTempDB].dbo.SessionData WITH (XLOCK)
WHERE Expiration < @now
SET @SessionsCleaned = @@ROWCOUNT
IF @SessionsCleaned = 0 RETURN
-- Mark persisted streams for this session to be deleted
UPDATE PS
SET
    RefCount = 0,
    ExpirationDate = GETDATE()
FROM
    [ReportServerTempDB].dbo.PersistedStream AS PS
    INNER JOIN #tempSession on PS.SessionID = #tempsession.SessionID
DELETE SE
FROM
   [ReportServerTempDB].dbo.SessionData AS SE
   INNER JOIN #tempSession on SE.SessionID = #tempsession.SessionID
UPDATE SN
SET
   TransientRefcount = TransientRefcount-1
FROM
   [ReportServerTempDB].dbo.SnapshotData AS SN
   INNER JOIN #tempSession AS SE ON SN.SnapshotDataID = SE.CompiledDefinition
UPDATE SN
SET
   TransientRefcount = TransientRefcount-
      (SELECT COUNT(*)
       FROM #tempSession AS SE1
       WHERE SE1.SnapshotDataID = SN.SnapshotDataID)
FROM
   SnapshotData AS SN
   INNER JOIN #tempSession AS SE ON SN.SnapshotDataID = SE.SnapshotDataID
UPDATE SN
SET
   TransientRefcount = TransientRefcount-
      (SELECT COUNT(*)
       FROM #tempSession AS SE1
       WHERE SE1.SnapshotDataID = SN.SnapshotDataID)
FROM
   [ReportServerTempDB].dbo.SnapshotData AS SN
   INNER JOIN #tempSession AS SE ON SN.SnapshotDataID = SE.SnapshotDataID

GO
GRANT EXECUTE ON  [dbo].[CleanExpiredSessions] TO [RSExecRole]
GO