ReportServerTempDB seems excessively large

Epicor ERP 10
1

Our ReportServerTempDB is 29 GB, while the LIVE db is only 17 GB.

Is there some maintenance (in either E10, in SQL Server Mngmnt) we should be doing?

We’re on version 10.1.400.23

2

You should check a few things.

Is the database in Simple Mode? It should be SIMPLE unless there is a good reason for otherwise.
The log file is probably what is taking the majority of the space (confirm that)
If the log file is the culprit and in Simple mode then run a backup to clear the log and then shrink the log back to a manageable size.
Confirm a backup or truncation occurs for the log on a periodic basis.
If the log file is not the culprit there are ways to track down the reports causing this growth.

3

Here is a query to check the contributors of this table -

–Reports that are largest contributors to ReportServerTempDB size through tables Segment, ChunkSegmentMapping, SegmentedChunk and SessionData:

SELECT
sd.ReportPath,
COUNT(s.SegmentId) as CountOfSegmentId
FROM
[ReportServerTempDB].dbo.Segment s
INNER JOIN
[ReportServerTempDB].dbo.ChunkSegmentMapping m
ON
m.SegmentId = s.SegmentId
INNER JOIN
[ReportServerTempDB].dbo.SegmentedChunk c
ON
c.ChunkId = m.ChunkId
INNER JOIN
[ReportServerTempDB].dbo.SessionData sd
ON
sd.SnapshotDataID = c.SnapshotDataId
GROUP BY
sd.ReportPath
ORDER BY
CountOfSegmentId DESC;

4
  1. Recovery model is SIMPLE
  2. ROWS are 16 GB, LOG is 13GB (So LOG is big, but not a majority)
  3. No change to db size after a full backup.
  4. “and then shrink the log back to normal size” Is this something I initiate, or is it something that happens during the backup?
5

I think I see the culprit

image

Now what do I do about it?

6

You have to initiate the shrink of the log file after the backup. You can also safely truncate the SEGMENT table but take SSRS offline before doing this.

7

I would read the following link and it will help you clean up the data. Once that is cleaned up I would just monitor to see if something current is causing the size or perhaps it was during testing, etc.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/333cff93-46b3-4e94-93aa-164963cafd18/cleaning-up-reportservertempdb-segment-and-related-tables?forum=sqlreportingservices

8

Thanks. I’ll pass that on to the SQL guy.

And if it wasn’t obvious, I’m not the SQL guy. I only know just enough to cause really, really big problems.