Tempdb IS hugh

Question

Post reply

Tempdb IS hugh

Michael McCabe

Newbie

Points: 1
More actions
May 25, 2006 at 6:15 am

#190703

Hi all,
I was wondering if any of you can help me, I have an application that uses an Access backend, and we need to execute several large reports from this, the system needs to be multi user, to speed up the process of report generation, we periodically import the data from the access daabase into SQL server using a DTS package and the produce the reports from SQL.
The problem That I am having is occationally the TempDB within the SQL serer grows Hugh >4GB, can anyone tell me how to prevent this or better still how to identify which strored procedure used by the reporting engine is forcing the tempDB to grow to this size

Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

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

brij_rauthan SSCommitted Points: 1521 More actions · Answer 1

TempDb will grow when you use temporary tables or variables to get data.

you can find the stored procs using trace...

Brij

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

Shoot... only 4 gig? We set our temp db to 12 gig... ON PURPOSE! Got resources? Use 'em.

--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)

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

Yes, all things are relative. 4 GB may be huge, or it may be small, it all depends on the perspective.

Michael, 'large reports' is a defenite 'tempdb-enlarger'. It may be that 4 GB is just 'normal' for you, it probably depends mostly on your reports, how they are written and what they actually do in conjunction with the volume of the data that the reports use.

/Kenneth

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

Size is relative. My temdb's vary from 1 Gb to 10 Gb. It's just what is 'normal' for that server. Oh, I definitely agree with Jeff ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

KSWile SSC Rookie Points: 40 More actions · Answer 5

If you need to shrink tempdb, the only way I know of is to re-start the SQL Server Service. This will reset the database file size. Since it's a special system db, you can't shrink it by the normal means. If anyone knows of another way, I'd love to hear it. Obviously this causes issues if you can't have any database downtime. Although some people in my corporation disagree with the need for doing this, we have our SQL Server's on a weekly reboot schedule. One big plus to doing this is that tempdb won't overtake my disk space.

As the others mentioned though, if you have the disk space and will be constantly creating objects in tempdb, you'll get better performance by setting it to be larger so it won't need to grow every time you run a report.