Effects of shrinking tempdb

[WINKEY]

We have sql server 7 database. The tempdb is set to unrestricted growth. During the day I have noticed the tempdb grows to 1 gig very quickly thus slowing the performance down. So I have a sql server job running every four hours to shrink the tempdb. So far I have not noticed any problems. But from the articles I have read on sql server knowledge base as well as here the shrink tempdb job should be run during the slow time or offpeak hours. Is this true for sql server7. If so any suggestions on how to fix my problem of tempdb filling up too fast.
Thanks in advance

 

[tlbroadbent]

Don't shrink tempdb. We only shrink tempdb if the size gets really "out of line" and then only during off hours. "Out of line" varies from server to server. On some servers, 1GB would be large. On others 2Gb is normal based on the server usage.

Shrinking tempdb during the day impacts performance in two ways.

1) the shrink requires a lot of resources and will slow or block other processes.

2) After tempdb shrinks, it will have to grow again. Growing a database is a slow processes because it requires physiucal IO.

Tempdb only grows because the applications/processes running on the server use it. The only way to reduce the growth of tempdb is to reduce the usage of tempdb through careful application redesign that reduces the size or eliminates creation of temporary tables and other objects in tempdb.

Sometimes, temp tables are unnecessarily created where derived tables or sub-queries would work. In SQL 2000 you can replace temp tables with table variables which have less impact on tempdb.

Sometimes more data is inserted into temp tables than is needed or used. Make sure queries select only the columns and rows needed for processing. Programmers frequently use "SELECT *" instead of naming the columns. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.