July 15, 2015 at 9:54 pm
Hi,
We are getting severe blocking in report server temp db deleting chunks and create stored procedure. I think that delete statements are cleanup some old subscriptions. I saw the code in a sp. Not sure how to disable that job. I didn't see any job under sql agent job
July 16, 2015 at 6:04 am
These are the blocking statements. How to disable that delete statements
delete top(@TemporaryMappingCount) CSM
output deleted.ChunkId, deleted.SegmentId into @cleanedSegments (ChunkId, SegmentId)
from [ReportServerTempDB].dbo.ChunkSegmentMapping CSM with (readpast)
join @cleanedChunks cc ON CSM.ChunkId = cc.ChunkId
where not exists (
select 1 from [ReportServerTempDB].dbo.SegmentedChunk SC
where SC.ChunkId = cc.ChunkId )
This is blocking by
(@SnapshotId uniqueidentifier,@IsPermanent bit,@ChunkName nvarchar(47),@ChunkType int,@ChunkId uniqueidentifier output,@ChunkFlags tinyint output,@MimeType nvarchar(260) output)
if (@IsPermanent = 1) begin
select@ChunkId = ChunkId,
@ChunkFlags = ChunkFlags,
@MimeType = MimeType
from dbo.SegmentedChunk chunk
where chunk.SnapshotDataId = @SnapshotId and chunk.ChunkName = @ChunkName and chunk.ChunkType = @ChunkType
selectcsm.SegmentId,
csm.LogicalByteCount as LogicalSegmentLength,
csm.ActualByteCount as ActualSegmentLength
from ChunkSegmentMapping csm
where csm.ChunkId = @ChunkId
order by csm.StartByte asc
end
else begin
select@ChunkId = ChunkId,
@ChunkFlags = ChunkFlags,
@MimeType = MimeType
from [ReportServerTempDB].dbo.SegmentedChunk chunk
where chunk.SnapshotDataId = @SnapshotId and chunk.ChunkName = @ChunkName and chunk.ChunkType = @ChunkType
if @ChunkFlags & 0x4 > 0 begin
-- Shallow copy: read chunk segments from catalog
selectcsm.SegmentId,
csm.LogicalByteCount as LogicalSegmentLength,
csm.ActualByteCount as ActualSegmentLength
from ChunkSegmentMapping csm
where csm.ChunkId = @ChunkId
order by csm.StartByte asc
end
else begin
-- Regular copy: read chunk segments from temp db
selectcsm.SegmentId,
csm.LogicalByteCount as LogicalSegmentLength,
csm.ActualByteCount as ActualSegmentLength
from [ReportServerTempDB].dbo.ChunkSegmentMapping csm
where csm.ChunkId = @ChunkId
order by csm.StartByte asc
end
end
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply