Tempdb IS hugh
-
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
-
TempDb will grow when you use temporary tables or variables to get data.
you can find the stored procs using trace...
Brij
-
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)
Intro to Tally Tables and Functions -
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
-
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."
-
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply