September 30, 2011 at 1:51 pm
Our ReportServerTempdb is currently at 67 GB size, with the Segment table accounting for virtually all the space.
Does anyone know if it is safe to truncate/prune it?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 5, 2011 at 2:04 pm
I am also having the same issue. My ReportServerTempDb is now 82G with the Segment table being 78G. Just 2 weeks ago the Segment table was 74G. It just keeps getting bigger. I asked Microsoft but they seemed to think this was normal. I don't think it is normal. It is getting very big very fast. In July this was 17G. I'd love to know what is going on.
Francis
October 5, 2011 at 2:08 pm
Thanks for responding.
I am also thinking of opening a case with Microsoft on this because it doesn't seem right.
There seems to be no pruning process on this table, shipping with the product.
There is also hardly no info on this on the web, which is frustrating...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 5, 2011 at 2:24 pm
According to SQL Server 2008 R2 Books Online topic "Report Server Database":
http://technet.microsoft.com/en-us/library/ms156016.aspx
"...
Report Server Temporary Database
Each report server database uses a related temporary database to store session and execution data, cached reports, and work tables that are generated by the report server. Background server processes will periodically remove older and unused items from the tables in the temporary database.
...
Generally, it is safe to delete the contents of the temporary database at any time. However, you must restart the Report Server Windows service after you delete the contents."
October 5, 2011 at 2:29 pm
Michael Valentine Jones (10/5/2011)
According to SQL Server 2008 R2 Books Online topic "Report Server Database":http://technet.microsoft.com/en-us/library/ms156016.aspx
"...
Report Server Temporary Database
Each report server database uses a related temporary database to store session and execution data, cached reports, and work tables that are generated by the report server. Background server processes will periodically remove older and unused items from the tables in the temporary database.
...
Generally, it is safe to delete the contents of the temporary database at any time. However, you must restart the Report Server Windows service after you delete the contents."
Thanks for the reference.
I have just opened a case with Microsoft on this issue.
Will update this thread with any new information.
I restarted the SSRS service yesterday, but that did not flush the contents of the Segment table.
Also, there seems to be no internal pruning of this data; size just keeps increasing.
Let's see what we get from MS.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 5, 2011 at 2:33 pm
I am assuming this post implies that you can stop reporting services - truncate the segment table (TRUNCATE TABLE dbo.segment) then restart report services with no problems. Has anyone done this?
Francis
October 12, 2011 at 7:51 am
Well we did it. We spoke to Microsoft and they okayed this. We stopped reporting services, and truncated all the large tables in ReportServicesTempDb and then shrunk the database. We restarted Reportservices, the reports started up again and we have had no problems. I will post later to let you know if the database starts growing madly again.
The users are indicating there is no problems with any of the reports
Francis
October 12, 2011 at 8:10 am
Thanks, good to know.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 12, 2011 at 9:14 am
Microsoft have kindly provided me with the following query for obtaining those reports with the largest size contribution to ReportServerTempDB:
--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;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 12, 2011 at 9:31 am
Just a PS. Doesn't work on 2K5 :angry:
October 19, 2011 at 9:05 am
The actual truncate statements were:
USE ReportServerTempDB
truncate table SessionData
truncate table PersistedStream
truncate table Segment
truncate table SnapshotData
truncate table SegmentedChunk
truncate table SessionLock
truncate table ChunkData
truncate table ChunkSegmentMapping
fyi. The segment table started growing again - 12 G in 1 week. I will continue to monitor
Francis
October 19, 2011 at 9:08 am
I was talking about Mario's script. 🙂
October 19, 2011 at 9:08 am
Thanks, I truncated the Segment table last night and was able to get the db size down to a more manageable size.
From what Microsoft mentioned to me, the highest contributors are reports that are called often and retrieve a large amount of data.
Query I posted above should get you these reports, if you are on SQL 2008.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
October 19, 2011 at 9:11 am
As someone real smart said => 😀
Ninja's_RGR'us (10/12/2011)
Just a PS. Doesn't work on 2K5 :angry:
October 19, 2011 at 10:22 am
Ninja's_RGR'us (10/19/2011)
As someone real smart said => 😀Ninja's_RGR'us (10/12/2011)
Just a PS. Doesn't work on 2K5 :angry:
Duly noted... 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply