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
name AS FileName,
size*1.0/128 AS FileSizeinMB,
WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Log file will grow to a maximum size of 2 TB.'
growth AS 'GrowthValue',
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.'
tempdev 400.000000 Autogrowth is on. 10 Growth value is a percentage.
templog 300.000000 Autogrowth is on. 10 Growth value is a percentage.
tempdev1 400.000000 Autogrowth is on. 128 Growth value is in 8-KB pages.
tempdev2 400.000000 Autogrowth is on. 128 Growth value is in 8-KB pages.
tempdev3 400.000000 Autogrowth is on. 128 Growth value is in 8-KB pages.
Any Suggestion would be really helpful. Thanks in advance.
.. Visit www.sqlsaga.com
for more t-sql code snippets and BI related how to articles.