a4apple (9/21/2012)
Hello Everyone,I have a proc, that is used as a job previously that pulls lot of data. From the past 2 days, it is getting failed due to the reason, Temp DB getting full. It is a proc with lot of Outer joins and Group By statements and pulling lot of data from XML too.. Is there any solution for this.
...
Any Suggestion would be really helpful. Thanks in advance.
Breaking up a large complex query into smaller chunks as #temp tables can help if SQL Server is creating large worktables (and bitmaps to support joins) in tempdb, which it's likely to do if there are different aggregation levels in your queries. Can you post the code? Better still, the actual plan.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden