Skip navigation

Why has my tempdb in SQL Server filled up?

A. First make sure that you have actually expanded tempdb - as it defaults to 2Mb on the master device only. Create new device(s) for it and expand it onto these devices. Do NOT expand it on master.

How big is tempdb? Remember that for joins/sorts SQL Server may need a significant amount of space depending on the size of the input tables. If you're doing an order by or a group by then SQL is probably using a temp table as an intermediate step. If you're doing a cartesian join, then you're going to need a LOT of space.

After that do a "select type, name from tempdb..sysobjects" to see what is in tempdb - this should give you clue(s) as to what is generating the objects in question and why they are not being deleted.

A prime cause of this is ODBC which has the option per DSN of generating temporary stored-procedures for all the queries per user connection. Most of the time you are better off disabling this option which you can do via the ODBC applet in the control panel.


TAGS: SQL
Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish