Sql Server 2000 - tempdb growing very large

views:

455

answers:

+1 Q:

Sql Server 2000 - tempdb growing very large

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.

+1 A:

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.

Alex_L 2009-07-13 15:02:04

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 2009-07-13 16:00:15

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

SQLMenace 2009-07-13 15:05:05

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.

ichiban 2009-07-13 15:05:06

@ichiban - recovery model of tempdb is always simple and cannot be changed.

Alex_L 2009-07-13 15:10:20

@Alex_L - This should be a comment to my answer instead of an answer in itself.

ichiban 2009-07-13 15:44:38

sorry, I cannot comment your answer, because I have not 50 points of reputation :)

Alex_L 2009-07-13 15:49:45

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?

Frank V 2009-07-13 15:41:14

+1 A:

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.

Richard Berthold 2009-07-15 01:59:01

ansaurus

tags:

views:

answers:

Sql Server 2000 - tempdb growing very large

related questions