TempDB growth and file size
We have a problem with the size of the tempdb.mdf file. The tempdb had grown to 25Gb and consumed all the available disk space. SQL server was restarted and the tempdb was reset back to the default size. The following day the tempdb suddenly increased in size from 200mb to 25GB within a very short space of time. There were a couple of event log entries from sqlservger regarding thelack of disk. Since then the server is running without any problems but the level of free space is virtually zero on the drive with tempdb.mdf file.
What would cause the tempdb to grow suddenly and to thissize?
Can I control the size the tempdb can grow to?
SQL 2005 (x64) sp1
W2K3 R2 SP1
[1463 byte] By [
SimonOM] at [2008-1-9]
Very common operation which consume tempdb :-
(a) Store explicitly created Temp tables , Table variables, Stored procedure, cursors etc.
(b) Stores Private and Global variable used throughout the instance
(c) Worktables associated with ORDER BY, GROUP BY, CURSOR
(d) Many System administration activities like DBCC commands uses TempDB
(e) If you have choose Snapshot Isolation Level then it uses Tempdb
(f) If your Report Server Uses Caching, then tempdb is used extensively
(g) If your have used SORT_IN_TEMPDB option while rebuilding indexes.
Refer :
Concurrency enhancements for the tempdb database --
Madhu
I think our problem may have been caused by a large set of parameters being passed to a select statement which was then truncated and left with just an ",," at the end of it.
