TempDB and SSRS

We have a vendor application with a database, 19 GB in size.  The application also uses SSRS.  Yesterday I received an alert that the tempDB on the SQL 2008 R2 server had grown over 5 GB in space.  (The ReportServerTempDB is sized at 150 MB and has not grown.)  It eventually reached 60 GB before I killed the process since it was going to soon use all the disk space available.  The process was associated with the vendor application and when I spoke to the vendor they said that was normal and was due to SSRS.  We're new to using SSRS so I was curious if this is normal?  Should we anticipate tempDB growing that large and larger?  Thanks

Steve

 
August 19th, 2015 11:49am

Yes you should estimate the Tempdb growth based on QA/DEV and depending on transaction/activities. But you should also try to understand what is causing TempDB to grow when you running SSIS using DMV's. Please go through

Capacity Planning for TempDB and

Troubleshooting Insufficient Disk Space in tempdb

Free Windows Admin Tool Kit Click here and download it now
August 19th, 2015 12:10pm

SSRS by itself would not affect TempDB usage, however the reports that are run through SSRS definetely could affect Tempdb usage, it just depends on what the reports are doing. If the reports are massive: a lot of joins, sorts, aggrigations, temporary tables and so on, and you dont have a lot of memory on the box a lot of that activity would spill into TempDB.

So most likely as your data grows the usage of tempdb would increase as well, being that this is a vendor app/reports most likely you are not able to tune the reports themselves but you may be able to take a look at the indexing to make sure you have optimal indexing in place to support your reporting needs, but still you need to make sure your vendor allows those type of changes without braking your support agreement.

HTH

August 19th, 2015 12:13pm

Hi Steve,

In Reporting Services, 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.

So in your scenario, its not a normal phenomenon that the report server temp database is growing large. One cause of the issue is that the cleanup cycle is not executing as expected. For more information, please refer to this article: ReportServerTempDB growing large : One of the causes.

Besides, you can query out all the large tables in report server temporary database, then stop reporting services, truncate those large tables and shrink the database. Please refer to this similar thread: ReportServerTempDB at over 60 GB size - is it safe to truncate the Segment table?.

If you have any question, please feel free to ask.

Best regards,
Qiuyun Yu

Free Windows Admin Tool Kit Click here and download it now
August 19th, 2015 11:45pm

When more users connect to your SSRS and access reports then it is normal that your reportstempdb grows.

Your tempdb grows because of transactions caused by the Stored Procedures that input data to the reports.

Or it is possible that other data bases on that server causing this.

Your sql server memory cache may not be recycling. Use perfmon to further troubleshoot this issue.

August 20th, 2015 12:27am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics