none
SQL Server TempDB issues

    Question

  • Hi,

    We have a report which gets aborted due to tempDB space issues. The report stored procedure internally calls a SQLCLR stored procedure which in turn calls a windows service to convert RTF text to images. These images are clinical documents with a resolution of 200 dpi. Once the image conversion process completes the SQL stored procedure fetches the images along with other patient information. The stored procedure creates a lot of temporary tables, tables variables and unions. The report subscription works fine few of a times and aborts few times due to tempdb space issue. We also monitored the tempDB space during the report processing and it seems whatever memory is available at the start of the report is still available at the end of the report. This issue is creating a lot of problem at the production environment. Is there any solution which can solve this issue? It is very urgent. Please any help would be appreciated.

    Regards,

    Gayatri

    Wednesday, May 16, 2012 7:50 AM

Answers

  • Looks like you have posted your question in different forums. 

    If you are seeing tempdb not grow up, you need check tempdb setting. If you are seeing tempdb used too much space, you need modify your code. You can not process huge amount data at same time, that will eat out your tempdb. you can process data in trunk.


    • Proposed as answer by Peja Tao Thursday, May 17, 2012 2:26 AM
    • Marked as answer by Peja Tao Tuesday, May 22, 2012 7:00 AM
    Wednesday, May 16, 2012 3:45 PM
  • Hi Gayatri,

    I would like to suggest you to read this online document Working with tempdb in SQL Server 2005.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by Peja Tao Tuesday, May 22, 2012 7:00 AM
    Thursday, May 17, 2012 2:31 AM

All replies

  • is Tempdb auto growth on???

    You can shrink the tempdb also

    • Edited by sushil naik Wednesday, May 16, 2012 8:16 AM
    Wednesday, May 16, 2012 8:15 AM
  • Looks like you have posted your question in different forums. 

    If you are seeing tempdb not grow up, you need check tempdb setting. If you are seeing tempdb used too much space, you need modify your code. You can not process huge amount data at same time, that will eat out your tempdb. you can process data in trunk.


    • Proposed as answer by Peja Tao Thursday, May 17, 2012 2:26 AM
    • Marked as answer by Peja Tao Tuesday, May 22, 2012 7:00 AM
    Wednesday, May 16, 2012 3:45 PM
  • Hi Gayatri,

    I would like to suggest you to read this online document Working with tempdb in SQL Server 2005.


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by Peja Tao Tuesday, May 22, 2012 7:00 AM
    Thursday, May 17, 2012 2:31 AM