Tempdb problem plz help me

Question

prasent my Tempdb size 150GB and i restarted SQL services but tempdb showing same size 150GB .plz how to clear the TEMPDB size .......... 

Answer 1

Shrink the teamdb and see the size. Please give us more info.

Whats your recovery model?

Is it in production?

Do you have any DR techniques configured?

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

Make sure that TempDB is set to autogrow and do not set a maximum size for TempDB, check the max size of the TempDB, have you tried to shirink the Temp DB ?

The most common reason for this is a long running, active transaction. The log_reuse_desc will be ACTIVE_TRANSACTION if this is the case. If so, you can run the queries below to find the culprit:
 -- Find oldest transaction
DBCC OPENTRAN

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers. Thanks!

Answer 3

make sure you don't set the size of tempdb as a fixed size. here is 150 GB....

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 4

It might be issue with your Initial size(run sp_helpfile on tempdb) for TEMPDB is set, so if it is set like that please change it with the

USE tempdb
GO
ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev,
SIZE = 2000MB)     --set here how much you need
GO

then restart the SQL server services & verify with the SP_helpfile.

Also check is there any scheduled job or any user activities that performs any bulk operation as soon as SQL gets up.

Incase if everything looks fine then shrink it(ensure there is no any open transactions exists & no users activity runs)

---

Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

Answer 5

 i restarted SQL services but tempdb showing same size 150GB

Hello,

After a SQL Server restart, the TempDB will be deleted and new created with the defined size.

So if after restart the TempDB is not a few MB, then I guess Wsion-Ho and Rama are right, there is a fixed size for the TempDB defined.

---

Olaf Helper

Blog Xing

Answer 6

simple recovery model

yes in production

Answer 7

Thank you for your reply