report server tempdb 过大问题

Hi abeljda,

In Reporting Services, the ReportServerTempdb is created with the primary report server database and used to store temporary data, session information, and cached reports. For the Segment table in the ReportServerTempdb, it stores temporary large objects such as cached reports.

In this issue, the Segment table grows significantly may occur due to the following reasons:

• The report execution takes longer than 10 minutes. (The CleanupCycleMinutes is configured for 10 minutes.)
• The report snapshots are too large to process for reporting services.

Because of the above issue, as the time progresses it left lots of orphaned snapshots which in turn resulted in growth of the database. Additionally, if a snapshot is deleted from the corresponding report, the snapshotdata and segment table gets cleaned up by reporting services every 12 hours automatically. That's why the "dbcleanup!windowsservice" runs. 
To work around the issue, we can use the following steps:
1. On the report manager, click "Site Settings", and switch the default settings for report history to "Limit the copies of report history: 10" instead. 
2. Run the following script to remove orphans manually based on your requirement:
begin transaction
       declare @cleanedSnapshots table (SnapshotDataId uniqueidentifier) ;        
       declare @cleanedChunks table (ChunkId uniqueidentifier) ; 
       declare @cleanedSegments table (ChunkId uniqueidentifier, SegmentId uniqueidentifier) ;         
       declare @deleteCount int ; 
       insert into @cleanedSnapshots
       select  distinct SnapshotDataId
       from SegmentedChunk 
       where SnapshotDataId not in (select SnapshotDataID from SnapshotData)
       -- clean up chunks
       set @deleteCount = 1 ; 
       while (@deleteCount > 0)
       begin         
              delete top(20)  SC 
              output deleted.ChunkId into @cleanedChunks(ChunkId)
              from SegmentedChunk SC with (readpast)   
              join @cleanedSnapshots cs on SC.SnapshotDataId = cs.SnapshotDataId ; 
              set @deleteCount = @@ROWCOUNT ; 
       end ;
       
       -- clean up unused mappings
       set @deleteCount = 1 ;     
       while (@deleteCount > 0)
       begin         
              delete top(20)  CSM
              output deleted.ChunkId, deleted.SegmentId into @cleanedSegments (ChunkId, SegmentId)
              from ChunkSegmentMapping CSM with (readpast)
              join @cleanedChunks cc ON CSM.ChunkId = cc.ChunkId
              where not exists (
                     select 1 from SegmentedChunk SC
                     where SC.ChunkId = cc.ChunkId ) 
              and not exists (
                     select 1 from [ReportServerTempDB].dbo.SegmentedChunk TSC
                     where TSC.ChunkId = cc.ChunkId ) ;
              set @deleteCount = @@ROWCOUNT ;
       end ;
       
       -- clean up segments
       set @deleteCount = 1
       while (@deleteCount > 0)
       begin
              delete top(20) S
              from Segment S with (readpast)
              join @cleanedSegments cs on S.SegmentId = cs.SegmentId
              where not exists (
                     select 1 from ChunkSegmentMapping csm
                     where csm.SegmentId = cs.SegmentId ) ;
              set @deleteCount = @@ROWCOUNT ;
       end
commit 
Regards,
Mike Yin