Temp Table full

Question

Temp Table full

Eswin

SSCrazy Eights

Points: 8320
More actions
July 7, 2009 at 9:20 pm

#97676

Hi expertz
During the past two days my tempdb log file grew almost to the size of the space available in my drive at least twice a day.
I moved the tempdb to another drive and today morning when i came i found that my templog got filled up to the size of the new drive and i was not able to shrink and some users complained they were not able to connect to the server since temp was full.
So i had to re-start the server.
What should i do to find the root cause of the issue?
What all events and data columns should i check when in run my Trace for the issue?
Why was i not able to shrink the templog using
"Dbcc shrinkfile('templog',100)" ?
What should i do avoid repetition of this issue?
Is restricting the growth of tempdb file a solution ?
Please help.........
Tanx 😀

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

Eswin (7/7/2009)
What should i do to find the root cause of the issue?
What all events and data columns should i check when in run my Trace for the issue?

Hi,

One of the root causes is

First find out the hash tables (temp tables) and replace it to the declare tables in the procedures/Reports.

ARUN SAS

Eswin SSCrazy Eights Points: 8320 More actions · Answer 2

Thanks for the reply

arun.sas (7/7/2009)
replace it to the declare tables in the procedures/Reports.
ARUN SAS

can you give more details.........

Tanx 😀

Sivaram-684323 Old Hand Points: 309 More actions · Answer 3

Temporary Solution for your Problem is Delete the Temp db and Restart the SQL server. after that you change the temporary table where you used in a cursor or loop. Instead of Temporary table you make a Table declaration.

arun.sas SSChampion Points: 11831 More actions · Answer 4

arun.sas (7/7/2009)
Eswin (7/7/2009)
What should i do to find the root cause of the issue?
What all events and data columns should i check when in run my Trace for the issue?
Hi,
One of the root causes is
First find out the hash tables (temp tables) and replace it to the declare tables in the procedures/Reports.
ARUN SAS

Hi,

/*Sample*/

CREATE Table #MyTable (

ID int,

Quantity int)

insert into #MyTable

...

select * from #MyTable

shows a million records

this data now in the tempDb

Where as we may use the declare tables like

declare @MyTable table

(

ID int,

Quantity int)

insert into @MyTable

...

select * from @MyTable

shows a million records..

now the data in the running DB

Few/more of the procedures having this hash tables,

You could find out these hash tables and replace it to declare table to reduce the tempDb hitting.

ARUN SAS

Eswin SSCrazy Eights Points: 8320 More actions · Answer 5

I think there many limitations for using DECLARE @variable TABLE command in sql server 2000 and since mine is an 2000 server many people say its not advisable to use table variables.

Is there any other go.

What about restricting the growth of tempdb files?

Tanx 😀