Purging TempDB

Hi DB Experts

I have a question on shrinking tempDB. Currently, I am using MS SQL 2005 Server, I have a software that used SQL DB but I had created a separate database to store my data. The tempDB is totally not used at all.

Problem - Whenever I export data into my created database on MS SQL server, tempDB also grew, I noticed that the files grew so large that it crashed the server. I am running 50GB free space on my drive where by the MS SQL server was installed.

Question - May I know are there any solution to shrink or freeze the growth of tempDB size?

Best regards

TEWCT

This statement

The tempDB is totally not used at all.

is totally wrong!

TempDb is constantly used as a staging area (or 'scratchpad') when gathering data for queries.

TempDb will continue to grow as long as there is a need. You can take the server off line (or stop the server service) and remove the TempDb database, and it will be recreated when the server starts up.

|||

You should in fact size your tempdb appropriately to increase performance.

If the export is causing your tempdb to grow to 50GB you should carefully look at the queryplans used in the export (or you are exporting a massive amount of data).

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Here's some more info on how to properly plan for TempDB sizing and also how to troubleshoot when it grows too large. The second link also includes instructions on how to limit the size of TempDB.

http://msdn2.microsoft.com/en-us/library/ms176029(sql.90).aspx

http://msdn2.microsoft.com/en-us/library/ms175527(SQL.90).aspx

Hope that helps,

-Steve

|||

Hi Steve and guys

Greetings. Thks for your advices & responses. Apologized, I am not a SQL guru. Am I right to say tempDB will definitely be used even if I created a separated DB for my application; even when I perform any simple query or update? My growth rate is about 500MB per day which I have been monitoring it over 3 days for now.

I had also performed some research from MSDN library that Steve had adviced, it seemed that it is a Microsoft methodology that SQL server behaviour;

i) TempDB size will definitely grow;

ii) DB tuning is required

iii) Factor more disk space for the growth?

iv) Restarting SQL service on regular basis

I would like to know if there is any specific way other then to re-provision and solve this SQL server tempDB growing issue?

|||

In a production server, the growth of TempDb 'should' eventually stablize. There may be regularly scheduled reports, etc., that require a large amount of space. There may be 'regular' data import/exports (ETL) activities that will require a large amount of space.

After your database is in production, and the initial 'load' activity has subsided, you may be able to reduce the size of TempDb by restarting the SQL Service -but if there is extensive data querying for reports or ETL activities, it may just grow again. So give it time, determine the 'appropriate' size, and let it be.

|||

Also have a look at this article:

How to shrink the tempdb database in SQL Server
http://support.microsoft.com/kb/307487/en-us

|||Hi guys. Thanks for your information. I will try that out and update at a later stage.