TEMPDB Grew to over 200GB!!!!!

[winsys]

I have a SQL 2000 server that had the tempdb database grow to over 240GB until it totally filled the drive. I'm trying to track down why this happened. There were 5 SQL jobs that ran at about the same time the SQL server log reported that templog.ldf...error112...not enough space on disk. 2 of the jobs were backups and they ran successfully. the other 3 jobs were transaction log backup jobs. All 3 of these jobs failed because the databases detailed in the jobs have the recovery method set to simple. Could these 3 failed jobs have caused the tempdb to grow so large or should look elsewhere?

Thanks!

 

[SQLDenis]

do you have any jobs that rebuild indexes?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mrdenny]

You will want to look elsewhere.

As SQLDenis said a good place to start is index rebuilds.

Another place to look is bad code that is using a fetch that is never ending. Incorrect joins, can also cause this problem, as well as over use of temp or global temp tables.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[winsys]

Thanks for pointing me in the right direction guys.
I will keep investigating.

 

[SQLSister]

Are you backing up the transaction log (not the database the log itself?) if you are not then the log is never getting truncated.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[winsys]

As I stated earlier, the databases are using the simple recovery method. So, the transaction log gets truncated with each nightly backup.

 

[mrdenny]

So, the transaction log gets truncated with each nightly backup.

The log actually gets truncated everytime the database is checkpointed (about once per minute or so).

Not to be a stickler or anything, but I'm a sticker for details.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com