My tempdb has grown to 40 GB and is growing my the minute but I'm
unable to determine what process is to blame. If I look at
tempdb..sysobjects all the objects are created very recently and change
each time i rerun the proc. I would think that anything that has grown
tempdb to 40 GB would have been around for a while. I also don't see
any processes in sysprocesses that is an obvious culrit (very old
last_batch time, etc.)

Any ideas?

Thanks

http://www.aspfaq.com/2446
http://www.aspfaq.com/2471




<pshro***@gmail.com> wrote in message
Show quoteHide quote

Thanks for the links but they really are of no use to me. Sure I can
restart SQL Server but I'd like to figure out what is causing this
problem so I can try and address it.  The code on that page to
determine the size of object in tempdb doesn't work:

EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

I get and error every time I run it.

Server: Msg 15009, Level 16, State 1, Procedure sp_spaceused, Line 76
The object '[dbo].[#6B0A1EDF]' does not exist in database 'tempdb'.

DBCC OPENTRAN tells me there are no open transactions.

I'm really, really, realy sorry the information is useless to you; many
others have found otherwise.

Have you looked at sp_who2 and profiler while this activity is happening?

To answer JT - it's not the transaction log of tempdb, it's the
datafile.

Aaron - I've used sp_who2 and I don't see any SPIDS that have been
around for long which again I would expect to see for something that is
growing tempdb like this. I'm using profiler but I'm not sure what to
trace really. It's a very busy database server so I need to be precise
in what I trace otherwise I just wind up with thousands and thousands
of rows to look at.  I hate the idea of just restarting SQL Server
without knowing what the problem really is. It seems like a
stereotypical fix for a Microsoft product :(

Thanks for your help.

Are you basing this on "LastBatch"?  I don't think this is a reliable
measure for someone who may have been in the database doing a lot of little
things, as opposed to a single statement or batch...

The article was meant to point out potential things to look for, but I don't
think this is something you're going to solve in 10 minutes.  More to the
point, restarting SQL Server is indicated there as just temporary relief;
e.g. if the data file is pushing the boundaries of your disk space.  If it's
not, then you don't need to restart SQL Server, you just need to look for
some of the potential causes.  Hence the article.

Aaron - about using the last_batch column. I was thinking that would be
a good starting point because I would think that something that made
the log grow this way would be a long running process rather than small
batches doing a bit of work here nad there. What do you think?

Thanks

I really don't know anything about your system or the user(s) or
application(s) that might be causing this.  It could be a single user with
one long batch or a single user with 8,000 short batches or 8,000 users with
a long batch each or 8,000 users with 8,000 short batches each.

A

and of course this had to happend on a Friday afternoon ..

<pshro***@gmail.com> wrote in message
Show quoteHide quote

Arg. I wasn't paying attention. It IS the transaction log, not the
datafile that is growing. I verified that tempdb is already set to
simple recovery mode and then I backed up the log with TRUNCATE_ONLY.
That freed up all the remaining used tempdb space. Now hopefully it
won't start growing again...

Thanks for your help.

Is there an ETL or mass record insert/update/delete process that could be
responsible for this?

<pshro***@gmail.com> wrote in message
Show quoteHide quote

Sorry, this is not a good piece of code at all.  The following should help
narrow down the tables using up space in the data file, which hopefully will
help you figure out which stored procedure(s) cause the growth (unless you
name all your temp tables #temp).

SELECT OBJECT_NAME(id), rowcnt
FROM tempdb..sysindexes
WHERE OBJECT_NAME(id) LIKE '#%'
ORDER BY rowcnt DESC

However this still won't necessarily tell you everything since, as the
article points out, tempdb can be used for several internal things that
AREN'T directly in your code, such as sorting etc.

To what degree is the size of transaction log a factor? Consider setting the
recovery model for tempdb to "simple".

As for what spid is to blame, run sp_who2 'active' and look at the LastBatch
(last command time) and DiskIO usage column.

<pshro***@gmail.com> wrote in message
Show quoteHide quote

Have you tried running DBCC OPENTRAN in the tempdb?  That might give you a
spid that is causing this.

Show quoteHide quote

Beating a dead horse - Drop table from proc
Stored Procedure (Weird Question)
Can I do this?
How to get the 4th record and on from select query
Basic Query Help