none
Purge ReportServerTempDB RRS feed

  • Question

  • We have a SQL2008 Report Server connected to a SQL2005 Report Server Database.

    The ReportServerTempDB is several GB in size.

    Two of its tables are huge. (SessionData and PerssistedStream)

    Is there a proper way to purge these tables?

    Also, our CleanupCycleMinutes is set to 10 minutes but it is not purging the TempDB file.

    Thanks in advance,

    Michael

    Monday, June 28, 2010 11:07 PM

All replies

  • Hi Michael,

    Try the following troubleshooting steps to dig this issue out:

    1. Monitor SQL Server or sp_lock command to see if there is dead lock on the database.

    2. Look into the error log of reporting services to get the related error message for further research.

    3. Ensure the SQL Server 2005 is installed with latest service patch level, you can download its latest patch service (sp3 + cu8) from http://support.microsoft.com/?Kbid=978915 .

    Hope this helpfully,

    thanks,
    Jerry

    • Marked as answer by Jerry NeeModerator Thursday, July 8, 2010 10:28 AM
    • Unmarked as answer by miguelh Thursday, July 8, 2010 11:44 AM
    Wednesday, June 30, 2010 8:37 AM
    Moderator
  • Hi,

    I am sorry that my previous post did not help you out of this problem. However, with my further research on this issue, since the reportserver tempdb is used to store tempory data, session information and cached reports, you can truncate/delete the data of the SessionData and PerssitedStream if you found they hold huge data, for example, the following T-SQL command to delete the more than 3 days session data and truncate the persistedStream data.  

    DELETE FROM [ReportServerTempDB].[dbo].[SessionData]
    WHERE datediff(day, CreationTime, getdate() ) > 3
    Go
    truncate table [ReportServerTempDB].dbo.PersistedStream

    Besides using cleanupcycleminutes to control clean up process, you also can add the DailyCleanupMinuteofDay configuration setting in the RSReportServer.config file to modify the frequency of the clean up process:

    <Add Key=" DailyCleanupMinuteOfDay " Value="120" />

    see http://msdn.microsoft.com/en-us/library/bb283153.aspx for more details.

    Please try and let me know the result.

    thanks,
    Jerry

    Tuesday, July 13, 2010 6:16 AM
    Moderator