tempdb rapid growth

[niall5098]

Hi There,

I had an issue last week whereby the log file for the tempdb exploded and ate up all the space on the d: drive. The user database was on the same drive and there fore fell over. As soon as i logged in i set about clearing up space, shrinking database logs as well tempdb log. eventually i got the disk space cleared and the application functioning.

However i still don't know what caused the spike. The job that are on the server run daily and have never caused an issue before. we have disk space monitoring and the graph shows that the space just shot up at the time. I have been assured that nobody was running anything on the server at the time so i am curious as to why this happened (and to prevent from happening again).

Is there any logs, built in tables that i can check that may provide some insight into why the tempdb log file grew so rapidly.

Thanks in advance

 

[DrSql]

Is this first occurnace?
Then you should explore if anyone else running any long running queries that uses tempdb also suggest to look into maintenance?
Any recent schema changes?
Is there any un-expected grwoth to DB?

Monitor for next week for same window also if needed run some trace.

Good luck


Dr.Sql
Good Luck.

 

[SQLBill]

Quote: I have been assured that nobody was running anything on the server at the time

I usually find no one ADMITS to doing anything, but someone usually did.

Things that cause large growths of the TLOG.

1. You have Full Recovery mode set and no Log backups are being done.

2. Database Reindexing.

3. A large transaction happened without commits. For example, a DELETE that affects a large number of rows can cause the log to grow large since it needs to know what the database looked like should the transaction fail and need to rollback.

I normally find #3 is the most common cause of log growth and users usually feel they didn't do anything out of the ordinary even though they rarely do large transactions.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[niall5098]

Thanks for the response.

This was the first occurance.

I will look into schema changes, if any.

As for the recovery model, as far as i am aware, you can't put the tempdb into full recovery model, and there would no re-indexing on the tempdb.