Monday, August 18, 2014

Understanding risks of segment table in Reporting Services

Recently I have been struggling with a problem we found in our staging ReportServer database where table Segment was occupying 99% of all the space assigned for the whole database, and talking about numbers it was almost 150 gigabytes (Take into account we had many users and reports on that environment). We raise this as a concern and start comparing the production environment with the staging one. Production was having no more than 20 gigabytes so clearly there was a problem out there.

2 links that I took into account for my research were:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/333cff93-46b3-4e94-93aa-164963cafd18/cleaning-up-reportservertempdb-segment-and-related-tables

http://www.sqlservercentral.com/Forums/Topic1183933-1550-1.aspx

both of them are suggesting to delete data or truncating the table, as well as also truncating other tables like:


  • SnapshotData
  • ChunkData
  • ChunkSegmentMapping
  • SegmentedChunk
I tried these sugestions both in the ReportServer and ReportServer TempDB, leading into a problem in all the environment; 
  • Report structure was intact. you were still able to navigate through folders
  • Reports were not showing up. once you click on ANY report you will eventually found the error below
  • Report Models were also gone.


An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help

After some trial and error in a test environment, I was able to find why these problems were happening, understanding more how these internal tables work that I wasn't aware of them before:
  • When you deploy a new report in the environment, automatically SnapshotData,ChunkSegmentMapping, SegmenetedChunk and Segment table are filled with data. Probably some binaries from those files are stored there, while Catalog table only has the metadata for them (path, name, id, etc).
  • If you ever delete one of those rows from SEgment table, you will end up having inconsistency issues, despite the fact that the table (as well as the others) dont have any Primary key - Foreign key relationship.
  • All these applies to Report Models, where they are used to store data coming from the datasources in order to be used as a temporal storage to keep away from using the original datasource directly. Probably all that data is stored in Segment table encrypted for future use.

To sum up, I'm still not sure if there's a way to delete the data coming from those tables without doing any damage to the whole environment, but unless you are totally sure what you are doing, do not attempt to delete/ truncate those tables.

No comments:

Post a Comment