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. This is how my temp db looks like right now.

I used the following query on my DB and got the

SELECT

name AS FileName,

size*1.0/128 AS FileSizeinMB,

CASE max_size

WHEN 0 THEN 'Autogrowth is off.'

WHEN -1 THEN 'Autogrowth is on.'

ELSE 'Log file will grow to a maximum size of 2 TB.'

END,

growth AS 'GrowthValue',

'GrowthIncrement' =

CASE

WHEN growth = 0 THEN 'Size is fixed and will not grow.'

WHEN growth > 0 AND is_percent_growth = 0

THEN 'Growth value is in 8-KB pages.'

ELSE 'Growth value is a percentage.'

END

FROM tempdb.sys.database_files;

GO

tempdev400.000000Autogrowth is on.10Growth value is a percentage.

templog300.000000Autogrowth is on.10Growth value is a percentage.

tempdev1400.000000Autogrowth is on.128Growth value is in 8-KB pages.

tempdev2400.000000Autogrowth is on.128Growth value is in 8-KB pages.

tempdev3400.000000Autogrowth is on.128Growth value is in 8-KB pages.

Any Suggestion would be really helpful. Thanks in advance.