We have an ASP.NET web app and a SQL Server Reporting Services report server using SQL Server Express 2008 R2. Once in a while when we tried to run the reports it will throw the error ReportServerSQLEXPRESSTempDB cannot be opened due to inaccessible files or insufficient memory or disk space Restarting the Report Service doesn't help, only restarting the SQL Server itself resolves the issue. The server limitation is not an issue because it has 16 GB RAM and 1TB of HD and plenty of spaces left. And it only happens once in a while so it's not a permission issue. Any idea why? Could this be caused by the Express edition's own limitation? Thank you Bill Bill

Hello Bill, Thank you to post your question on TechNet SQL Forum. Express edition of SQL server has some limitation on the database. For SQL server 2008 R2 express edition, it only uses 1 CPU, 1 GB RAM, 10GB maximum database size, even if you have more resources. Therefore, we can check the condition to see whether the resources are exhausted when you access the temp DB for reporting service. For more information, please review the following article on MSDN. http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.105).aspx If you have any idea about this issue, please tell us freely. Regards, Edward TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.Edward Zhu TechNet Community Support

Hi Edward, Thank you for the reply, yes I'm aware of the Express edition's limitations. More information regarding the tempdb, it's set to Autogrow with unrestricted growth (10% increment), Auto Shrink is set to false. The Partition it's on has 1 TB of disk space, when the error happened the tempdb mdf file was less than 100 MB in size and the log file was even less. It starts to look more and more to me like a memory issue than a hardware/space issue, especially knowing that Express has the 1 GB RAM limitation. Do you know how the Express handles the situation when the RAM is full? Does it write to the tempdb or does it just throw the error? I would think if it throws the error then we would see the error a lot more often but it only happens once in a while. (for us it's about once a month on average). You also said that we can check the condition to see whether the resources are exhausted when you access the temp DB for reporting service, can you point me to the right direction as to how? Thanks again.Bill

Hello Bill, Thanks for your response. 1GB RAM indicates to the total memory size of buffer that the SQL Server express uses. If the buffer is full, then the SQL server will move some Least Frequent Used (LFU) data out of the memory. It is possible to write to a temp place on disk, but it will not write it down to the tempdb. Consequently, we found tempdb file is still small when the issue occurs. For more information about tempdb, please refer to the following article on MSDN. http://msdn.microsoft.com/en-us/library/ms190768.aspx For reporting service, there is a ReportServerTempDB. It contains the information about the temp information for executing the report server. For example, it will store the chunk segment to improve the performance. In this database, we can query the ChunckSegmentMapping table to see the current chunk memory usage. For further information about the whole SQL server, we can review the DMVs in master database. For example, sys_dm_os_memory_* views are very useful for you to get the current status of the memory. As well, you can run the DBCC MEMORYSTATUS to get the detail memory usage report on the server. For more information about the above information, please refer to the following articles. http://msdn.microsoft.com/en-us/library/ms176083 http://support.microsoft.com/kb/907877 If you have any questions about this issue, please let us know. Regards, EdwardEdward Zhu TechNet Community Support

Hello Bill, Thanks for your response. 1GB RAM indicates to the total memory size of buffer that the SQL Server express uses. If the buffer is full, then the SQL server will move some Least Frequent Used (LFU) data out of the memory. It is possible to write to a temp place on disk, but it will not write it down to the tempdb. Consequently, we found tempdb file is still small when the issue occurs. For more information about tempdb, please refer to the following article on MSDN. http://msdn.microsoft.com/en-us/library/ms190768.aspx For reporting service, there is a ReportServerTempDB. It contains the information about the temp information for executing the report server. For example, it will store the chunk segment to improve the performance. In this database, we can query the ChunckSegmentMapping table to see the current chunk memory usage. For further information about the whole SQL server, we can review the DMVs in master database. For example, sys_dm_os_memory_* views are very useful for you to get the current status of the memory. As well, you can run the DBCC MEMORYSTATUS to get the detail memory usage report on the server. For more information about the above information, please refer to the following articles. http://msdn.microsoft.com/en-us/library/ms176083 http://support.microsoft.com/kb/907877 If you have any questions about this issue, please let us know. Regards, EdwardEdward Zhu TechNet Community Support

Hi Edward, Thank you very much for the information. I just finished reading all of the articles and my head is spinning :), but I still can't seem to pinpoint what exactly is causing the issue and how I can prevent it from happening. I have a few questions: 1. when the issue happens, if I run DBCC MEMORYSTATUS on the tempdb, which particular stat(s) should I look at in order to pinpoint the resource that has been exhausted? 2. What are some of the main factors that are most likely to cause this issue? The 1 GB RAM limit, large queries, unoptimized queries, too many users running reports at the same time? 3. If I'm looking for a quick fix, would switching to another SQL Server editions like Web or Standard help eliminate this kind of errors since they would utilize all of the possible RAM on the server, which has 16 GB of it? Thanks again. Bill

Hi Edward, Thank you very much for the information. I just finished reading all of the articles and my head is spinning :), but I still can't seem to pinpoint what exactly is causing the issue and how I can prevent it from happening. I have a few questions: 1. when the issue happens, if I run DBCC MEMORYSTATUS on the tempdb, which particular stat(s) should I look at in order to pinpoint the resource that has been exhausted? 2. What are some of the main factors that are most likely to cause this issue? The 1 GB RAM limit, large queries, unoptimized queries, too many users running reports at the same time? 3. If I'm looking for a quick fix, would switching to another SQL Server editions like Web or Standard help eliminate this kind of errors since they would utilize all of the possible RAM on the server, which has 16 GB of it? Thanks again. Bill

Hello Bill, Thanks for your response. For the first question, there is a good document about the command in Microsoft KB. http://support.microsoft.com/kb/907877 Please refer to the article to get more information. For this issue, we are still not able to confirm the factors. From the error message, it is possible that the memory pressure or high concurrent processing. In order to narrow down this issue, to check the memory status when the issue occurs is very necessary. In the DBCC MEMORYSTATUS, we need focus on the following items. VM Committed & VM Reserved: we need check whether the SQL Server used all memory resource.Buffer Pool section: If the buffer pool exceeds the limitation, it will cause the issue as well. If the root cause of this issue is due to the memory pressure, using Standard or higher edition can solve this kind of issue. However, if the report serve is a product environment, we strongly recommend you upgrade your reporting server to higher edition, so that you will get the stable environment. If you have further questions about this issue, please tell us freely. Regards, Edward TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.Edward Zhu TechNet Community Support

Hello Bill, Thanks for your response. For the first question, there is a good document about the command in Microsoft KB. http://support.microsoft.com/kb/907877 Please refer to the article to get more information. For this issue, we are still not able to confirm the factors. From the error message, it is possible that the memory pressure or high concurrent processing. In order to narrow down this issue, to check the memory status when the issue occurs is very necessary. In the DBCC MEMORYSTATUS, we need focus on the following items. VM Committed & VM Reserved: we need check whether the SQL Server used all memory resource.Buffer Pool section: If the buffer pool exceeds the limitation, it will cause the issue as well. If the root cause of this issue is due to the memory pressure, using Standard or higher edition can solve this kind of issue. However, if the report serve is a product environment, we strongly recommend you upgrade your reporting server to higher edition, so that you will get the stable environment. If you have further questions about this issue, please tell us freely. Regards, Edward TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.Edward Zhu TechNet Community Support

Hello Bill, Thanks for your response. For the first question, there is a good document about the command in Microsoft KB. http://support.microsoft.com/kb/907877 Please refer to the article to get more information. For this issue, we are still not able to confirm the factors. From the error message, it is possible that the memory pressure or high concurrent processing. In order to narrow down this issue, to check the memory status when the issue occurs is very necessary. In the DBCC MEMORYSTATUS, we need focus on the following items. VM Committed & VM Reserved: we need check whether the SQL Server used all memory resource.Buffer Pool section: If the buffer pool exceeds the limitation, it will cause the issue as well. If the root cause of this issue is due to the memory pressure, using Standard or higher edition can solve this kind of issue. However, if the report serve is a product environment, we strongly recommend you upgrade your reporting server to higher edition, so that you will get the stable environment. If you have further questions about this issue, please tell us freely. Regards, Edward TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.Edward Zhu TechNet Community Support

Hi Edward, Thank you for all the help. I will make sure to run DBCC MEMORYSTATUS next time it happens again and will update with the stats. Bill

Hi Edward, We are looking into upgrading our SQL Server to either Web or Standard. I was comparing the features via the link below Features supported by Editions of SQL Server 2008 R2 Under the Reporting section, it shows the Web edition has a 4 GB maximum for the so called Reporting Services memory limit, and the same for the Express Edition with Advanced Services, which is the one we are using that has the issue. Here are my questions: 1. What's the difference between this reporting service memory limit of 4 GB and the maximum memory utilized of 1 GB RAM for the Express Edition? 2. Since the Web edition and the Express w/ Advance Services edition have the same reporting service memory limit of 4 GB, should I avoid the Web edition and upgrade to the standard edition instead? Thanks again.Bill

Hello Bill, Thanks for your questions. 1. What's the difference between this reporting service memory limit of 4 GB and the maximum memory utilized of 1 GB RAM for the Express Edition? The RS memory limit is not equal to the maximum memory utilized. RS memory means the memory limitation of the reporting service, but the maximum memory utilized means the memory limitation of the database server. We can consider that the reporting service is a windows service or web application. When it runs, it need memory and the limitation is 4GB. If the application wants to request more memory, it will get the error, like out of memory. The maximum memory utilized of database is the limitation of the RAM usage, which is not the total memory limitation. Therefore, they can be different. 2. Since the Web edition and the Express w/ Advance Services edition have the same reporting service memory limit of 4 GB, should I avoid the Web edition and upgrade to the standard edition instead? To choose which edition of SQL server is due to your business requirement. In my opinion, I will choose Standard edition, since we need subscription feature of Reporting service in our business. This feature is available on Standard or higher edition, but not on Web edition. If we need the data-driven subscription, then I will choose enterprise or datacenter edition. Therefore, choosing the suitable edition is not only to consider the performance requirement, sometimes it is about the future functions. I hope my answer is helpful to you. If you have any questions about this issue, please tell us freely. Regards, Edward TechNet Subscriber Support If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here. Edward Zhu TechNet Community Support