Temp Table and Temp DB Space issues

Bobby P 271 Reputation points

We are recently having issues with Temporary Tables and Temp space failing on some of our jobs. We have asked our DBA if increasing Temporary space and Temp DB is an option and apparently it is not as Temp Space is already allocated fairly high.

Just wondering if anyone else has had such issues with Temp space and if there's a way we can break up our Job and SQL Server Stored Procedures into manageable pieces that won't stress Temporary space and Temp DB.

Believe me...we have explored all of our options with our DBA Group so this seems as though this might be the last resort.

Hoping someone out there can provide a solution as to maybe I/O transaction counts to control SQL Server ISUD and control Temporary space.

Thanks in advance for your review and am hopeful for a reply.

  1. Lakshmi Narayana Garikapati 1,330 Reputation points • Microsoft External Staff • Moderator

    Hi @Bobby P, Thanks for reaching out SQL Forum

    @Erland Sommarskog "Thanks for the prompt responses

    @Bobby P,

    I’m following up to check if the response provided below was helpful.

    Please let us know if your issue has been resolved. If you still have any questions or need further assistance, don’t hesitate to get in touch we’re happy to continue supporting you.

    We appreciate your participation and thank you for being an active member of the community.

    Best regards,

    Lakshm

  2. Lakshmi Narayana Garikapati 1,330 Reputation points • Microsoft External Staff • Moderator

    @Bobby P I’m following up to check if the response provided below was helpful.

    Please let us know if your issue has been resolved. If you still have any questions or need further assistance, don’t hesitate to get in touch we’re happy to continue supporting you.

    We appreciate your participation and thank you for being an active member of the community.

    Best regards,

    Lakshm

Sign in to comment

Answer recommended by moderator

Erland Sommarskog 134.7K Reputation points • MVP • Volunteer Moderator

I would expect that many of us have run into similar issues in one shape of form.

And that is about all I can say about the matter, since I don't know anything about your stored procedures. There are many ways to fill up to tempdb. Temp tables that are excessively large is one way. But it can also be hash or sort spills or spool operators.

I guess that when you get error 1105 that tempdb is full, these errors do not appear randomly, but typically in one or two places. These are the pieces of code you need to scrutinise.

0 comments No comments
Sign in to comment

0 additional answers

Sign in to answer

Your answer