tempDB filled 140GB HDD in under 2 days?

Question

tempDB filled 140GB HDD in under 2 days?

2Tall

SSChampion

Points: 14644
More actions
March 7, 2009 at 9:56 am

#155387

I had an issue this morning where tempDB had grown to 140GB. The data file for the application resides on the same drive so no data could be written....
I feel the cause of the issue is a report. I have attempted to execute a report (scheduled, delivered by email, render format excel) a number of times, but each occasion it fails. Report Server returns an error: Failure sending mail. An error has occurred during report processing.
I implemented the following :http://www.mssqltips.com/tip.asp?tip=1388 in a test lab.
Can anyone suggest how I might monitor what is happening, i.e. run a custom trace?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

I don't know why SSRS will cause TempDB to grow because it generally rejects code using temporary tables. So you need to find where and what code is used to generate your reports.

The best place to start is move the database used for your reports data source and profile the code. I was in a team of five reports developers with more than 100 stored procedures but our TempDB did not grow so it must be local to your setup.

Kind regards,
Gift Peddie

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 2

I recently went through such a thing... for the second time...

It was simply "bad" code where a developer didn't understand the relationship between a few tables and created what amounts to an accidental cross join. Heh... I got that call on my hour long drive home one night... TempDB had grown to 250 gig due to an improper join between a 7 million row table and and 700 k row table.

I've not worked much with some of the traces available in 2k5 but, my understanding is, that there is one that detects when TempDB grows and the proc or event that caused the growth. I'd recommend setting TempDB back to your normal planned size, starting the trace, and see what happens. In the end, you should be able to identify the culprit and begin troubleshooting the problem with the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gift Peddie SSC Guru Points: 73570 More actions · Answer 3

Hi Jeff,

The link below says Trace Flag 1118 is not really needed in SQL Server 2005 and above because of changes made. The user is supposed do some configurations because Trace Flag 1118 may only work with some issues in 2005.

http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/04/managing-tempdb-in-sql-server-tempdb-configuration.aspx

http://support.microsoft.com/kb/917047

Kind regards,
Gift Peddie

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 4

Gift Peddie (3/7/2009)
Hi Jeff,
The link below says Trace Flag 1118 is not really needed in SQL Server 2005 and above because of changes made. The user is supposed do some configurations because Trace Flag 1118 may only work with some issues in 2005.
http://blogs.msdn.com/sqlserverstorageengine/archive/2009/01/04/managing-tempdb-in-sql-server-tempdb-configuration.aspx
http://support.microsoft.com/kb/917047

Thanks for the great links, Gift. But, I'm not sure how either applies to what I just said about accidental cross-joins eating TempDB for lunch. Would you explain, please, 'cause I'm missing it... the morning coffee is definitely wearing off. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gift Peddie SSC Guru Points: 73570 More actions · Answer 5

No I know how cross JOINs without the where clause can make TempDB grow because most RDBMS query processors use those JOINs to increase JOIN performance. But these links are the current solutions to manage TempDB growth per Microsoft.

Kind regards,
Gift Peddie

Jeff Moden SSC Guru Points: 1004393 More actions · Answer 6

Gift Peddie (3/7/2009)
No I know how cross JOINs without the where clause can make TempDB grow because most RDBMS query processors use those JOINs to increase JOIN performance. But these links are the current solutions to manage TempDB growth per Microsoft.

Ah... got it. Thanks for posting them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

2Tall SSChampion Points: 14644 More actions · Answer 7

Thanks guys, I also got the call on the drive home after a night out 🙂

We recently had a developer write code for a complex report (took a few days to develop). The code was untested before delivery. I ran when in a test environment with dithering results. With a handful of records in the database the report initially failed when run through the front end app but executed when scheduled (took just over 1 hour).

I code uses a number of temp tables. To be fair to the developer it was a tricky one!

Pretty sure the issue is caused by the report.

Once again, many thanks.

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper