本文共 3303 字，大约阅读时间需要 11 分钟。
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:
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
转载地址：http://zlfmi.baihongyu.com/