In sql is there another way to clear temp db and its log other than restarting the service?

Question

As we create and drop temporary tables, inserts data into those tables, the size of the temp db and it's log cause the database to grow in size unlimitedly. It reaches upto 100s of gb and fills the hard disk. This can cause the lack of size in database server and the application may crash. We need to restart the sqlexpress service which is I think is a bad idea. The stopping of service cause the site/application to go down. So what is the alternative for this problem

Answer 1

You can always try shrink database files:

USE [tempdb]
GO
DBCC SHRINKFILE (N'templog' , 0)
GO
DBCC SHRINKFILE (N'tempdev' , 0)
GO

This will release all unused space from the tempdb. But MSSQL should reuse the space anyway. So if your files are such big, you need to look into your logic and find places where you create really big tables and try to reduce their sizes and/or their lifetime.

Also you shouldn't avoid dropping unused temporary tables.

And you can try to reduce session lifetime. It will guarantee that old unused tables will be dropped.