I have few stored procedure which have temp table, i am not dropping temp table because i think when user session get over the temp table automatically drop,so why temp db taking huge space?
are you doing the standard maintenance on that database.
backup, trim logs etc?
I agree with this answer in most cases, that is it's better to explicitly drop temp tables when you're done. However extremely high numbers of table creations and subsequent drops can case contention in tempdb. How many calls per second are being made to your stored procedures?
If a temp table takes a huge amount of space, drop it yourself. For small temp tables, you're better off not dropping them yourself.
Once the tempdb data files have grown to support your queries - it isn't going to shrink to the original size until you restart SQL Server.
If your daily/weekly/monthly processes require a larger tempdb database - it is much better to size the files to a bit larger than needed and leave it alone.
Is your issue the fact that the database is actually showing space used - or is it just the size?
thanks, for your answer now i am explicitly droping temp table and check.
yes we are taking the backup daily.