asked on

SQL 2008 TEMPDB is filling up with over 20GB in space

Hello Experts, I'm running SQL 2008 server with 2 different VB/.NET applications and 14 databaases (7 per application). Since 3 weeks back, due to some specific process/query my temp DB has begun to grow. So when I reboot the serevr, I have 30 GB available, after a specific process is executed, I'm left with 20MB?!?!?!

I need to find out which DB / Query is writing to the temp MB and making it grow to thsi size without deleting the temp records, please advise.

Thank you in advance for your help.

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EddieIT

ASKER

The above came back with a blank result. Maybe because I rebooted teh SQL services in order to empty out some space. Any way to retrieve the history information?

TempDBA

No. Not now. Whenever you reboot or restart the service, the tempdb is created afresh. So, everything will be gone. You can schedule a job that runs the query posted by the expert and stores data into a table. You can analyze the table later.

EddieIT

ASKER

Can I get a qyery example?

TempDBA

A query example means?

Scott Pletcher

You will simply have to get more disk space for tempdb.

Tempdb is used for all sorts of things by SQL. It will take you time to track down tempdb space usage.

30GB is so tiny, in modern terms, that you probably cannot realistically expect to limit tempdb to that size nowadays.

And you should go ahead and pre-allocate tempdb, including the log, to the size it will need, because of the overhead of it growing while SQL is running.

Also, change the increment to a fixed amount that is not too small (such as 50MB or 100MB, rather than 1-10MB).