ReportServerTempDB at over 60 GB size - is it safe to truncate the Segment table?

• Karsten_EnH

  SSC Enthusiast

  Points: 151

  More actions

  July 25, 2014 at 2:23 am

  #1732279

  Hi all,

  our RS TempDB had grown up to 115 GB. As far as I got it meanwhile this was due to a change of the CleanupCycleMinute parameter in the Reportserver.config file from 10 minutes to 12 hours. I have no idea who did this but it has been all but a good idea because after this change no cleanup activities could be found in the report server log files any longer. Log files and the Reportserver.config are to be found in

  <Installation Drive>\<Program Files or Program Files(x86)>\Microsoft SQL Server\<SSRS Instance>\Reportserver\rsreportserver.config

  and

  <Installation Drive>\<Program Files or Program Files(x86)>\Microsoft SQL Server\<SSRS Instance>\Reporting Services\LogFiles.

  A funny thing to mention is that the Installationdrive of these paths can differ. So if you do not find the log files check drive D instead of drive C for example. After changing parameters in Reportserver.config the SSRS service has to be restartet.

  Additionally I decided to change another parameter in the report manager. The URL of this site can be found in the report server configuration program in the section report manager. On this site you have to click the site settings in the upper right. Here I limited the number of copies of report history to retain to ten. It has been set to unlimited by some one else before and I don't think this to be default.

  After these adjustments there was a change in the cleanup activities. When I checked it next morning the Segment tables have had been cleared, however there was still a huge amount of data in the sessiondata und sessionlock tables. The stored procedure which does the cleanup of this section of ReportServertempDB, well hidden in the ReportServer-DB 😉 is named "cleanexpiredsessions" with one output parameter to show the number of cleaned sessions. This sp works with chunks of 20 but will not do anything if you have more than 19 rows in the sessiondata table with no SessionId in the sessionlock table. These orphaned sessions have to be deleted manually. For example like this (if the number of orphaned sessions is not bigger than thousand which is typically the case)

  Delete top (1000) from sessiondata where SessionId not in (select SessionId from sessionlock)

  I setup a little script to run cleanexpiredsessions in a loop and now the used space of my ReportServerTempDB is melting like snow in the sun.

  use ReportServerTempDB

  go

  create table ##log (datim datetime, Anzahl int, return_value int)

  declare @cleaned_sessions int

  , @return_value int

  set @cleaned_sessions = 1

  set @return_value = 0

  while @cleaned_sessions > 0 and @return_value = 0 begin

  exec @return_value = CleanExpiredSessions @cleaned_sessions output

  insert into ##log select getdate(), @cleaned_sessions, @return_value

  end

  With select * from ##log you can track the progress of the work of CleanExpiredSessions in another Query Analyzer window. Of course you can omit the ##log thing completely as well.

• Valbuenito

  SSC-Addicted

  Points: 436

  More actions

  March 4, 2015 at 4:22 am

  #1781572

  Hi,

  I have the same problem and I use your solution.

  IT'S WORK !

  Thanks for all 🙂

  To resume the solution is :

  - Stop the Report Server Service

  - TRUNCATE TABLE dbo.segments

  - Restart the Report Server Service

  - SHRINFILE Log

  Log before : 72Go

  Log after : 0.4Go

  Thanks