SQL Server temp DB issue

Question

Hello everyone,

I am using SQL Server 2008 on Windows Server 2008. I find the temp DB mdf and temp ldf is very big (about 13G), and it is almost the same size of all other real application database files (mdf+ldf).

My questions,

1. What temp DB is used for?
2. Is it normal situation that temp DB is so big? If not, any ways to clean-up temp DB without impacting system performance and reliability?

thanks in advance, George

Answer 1

1: TempDb is used for things like sort. Anythin temporary. Stupid developers needlessly using the DISTINCT statement are hitting it part. Plus temporary tables.

2: No. There are rare cases this may be the case, but these are unusual edge cases. Tempdb cleanup.... restart server. Tempdb is totally regenerated on every start. Then start looking for queries going to tempdb. This will require some manual work. Check for DISTINCT clauses used needlessly.

Answer 2

You can shrink the tempdb mdf/ldf files using the shrinkfile, shrinkdatabase DBCC commands.

use tempdb
go

dbcc shrinkfile (tempdev, 'target size in MB')
go

dbcc shrinkfile (templog, 'target size in MB')
go

Writing code in an efficient manner can avoid the growth of the tempdb. Avoid using "cursors" in your code which are major killers of performance and the larger the dataset of the cursor the larger the size of the tempdb. Avoid using temp tables when its not necassary. using #temptable, ##temptable for the entire scope when its not required will cause performance issues again.

SQL 2005/2008 has CTEs where in you can query data on a temp table and the scope is just one line after that.

;With CTETable as
(
  SELECT <Columnlist> FROM <TableName> {Joins} WHERE <Condition> GROUP BY <Column List> ORDER BY <COlumn> ASC
)

SELECT ColumnList from CTETABLE
....
Statements

The scope of that CTEtable expires after "SELECT ColumnList from CTETABLE" Line. This way it is more effective. Writing effective queries would also help.