Take the 2-minute tour ×

We have a SQL Server 2000 production environment where suddenly (ie. the last 3 days) something has caused the tempdb data file to grow very large (45 gigs with a database which is only 10 gigs). Yesterday, after it happened again we shrank the database and ran the major batch processes individually without any problems. However, this morning the database was back up to 45 gigs.

Is there a simple way to find out what is causing this database to grow so large? Ideally, something which could be looked at today but if that is not available something which can be set to get that information tomorrow.

BTW: Shrinking the database gets back the space within a few seconds.

share|improve this question
Sounds like a Server Vault question... but you might want to look at Sql Profiler. –  Jimmy Chandra Jul 13 '09 at 14:57

5 Answers 5

Agreed with Jimmy, you need use SQL Profiler to find which temporary objects created so intensive. This may be temporary tables that uses some reports or something like.

share|improve this answer
I'd set up some Profiler traces to run all night tracking likely events: track anything that took more than 10 seconds to run, anything that generated over 1M of data reads, anything that generated more than 1M of writes (that'd be three separate traces). It's hard to say, a lot depends on your environment and system; tracking this stuff down can take a lot of time (since you can only run overnight and check the next day). Since it's tempdb, its probably one single big action, and not a bunch of little guys (unless you have declare transactions). –  Philip Kelley Jul 13 '09 at 16:00

I wanted to thank everyone for their answers as they definitely led to the cause of the problem.

We turned on SQL profiler and sure enough a large bulk load showed up. As we are working on a project to move the "offending" job to work also in mysql we will probably just watch things for now.

share|improve this answer

do you have a job running that rebuilds the index? It is possible that it uses SORT_IN_TEMPDB

or any other large queries that do sorting might expand tempdb

share|improve this answer

This may have something to do with the recovery model that the TempDB is set to. It may be set to FULL instead of BULK-LOGGED. FULL recovery increases the transaction log size until a backup is performed.

Look at the data file size vs. the transaction log size.

share|improve this answer

I'm not a DBA but some thoughts:

  • Is it possible that there are temp tables being created but not dropped? ##tempTable?
  • Is it possible that there is a large temp table being created (and dropped) but the space isn't reclaimed?
  • Are you doing any sort of bulk loading where the system might use the temp table? (I'm not sure if you can) but can you turn on Auto-Shrink for the tempdb?
share|improve this answer

Your Answer


