Tempdb grow too much

[bensmail]

I've a big problem:
The tempdb grow too much and don't shrink automatically.
the tempdb for a transaction can reach 11 Gb and don't schrink.
How can i avoid this problem???

bensmail

[skhanal]

It could be caused by a long running transaction. Figure out what is running when tempdb grows.

Also check for open transactions using dbcc opentran. SQL Server can't shrink the database if there is an open transaction.

Make sure that tempdb database is running in simple recovery model.

[bensmail]

When i run a transaction the tempdb growth. After a commit the tempdb don't shrink, and there is no running transaction.... and when i run a new transaction it's hang

Bensmail

[bensmail]

I heard about "truncate log on chekpoint" how can i use this?
if i set this param means it will be shrink automatically after a commit?


bensmail

[skhanal]

In Enterprise Manager, right click on tempdb and select properties, go to options tab and select Simple in the recovery model. this is equivalent to truncate log on checkpoint.