| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2007-05-21 : 16:45:19
|
| Guys,I recently deleted a database and then restored it from the backup. Since then I have noticed a strange behavior with tempdb database. It is constantly growing (~ 1GB per hour), until it fills up our whole hard disk space.Currently I set up a job to run periodically to shrink it, which works like a charm. However, I see it as a temporary patch. I still want to find out what is causing it.Can those with experience with tempdb growth share their experiences/suggestions?Thanks a lot |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-21 : 16:47:23
|
| Did you check db activities? Try trace them in profiler. |
 |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-05-21 : 16:53:23
|
| rmiao, can you be a bit more specific. What exactly would you advise to trace?Thank you |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-21 : 23:49:40
|
| I'll trace all processes on tempdb. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-22 : 02:00:28
|
| "Currently I set up a job to run periodically to shrink it"Note that shrinking TEMPDB whilst it is in use can cause it to become corrupted. Might be a problem for a heavily used server!Kristen |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-05-22 : 09:52:26
|
| rmiao, thanks for the advice - I'll do that now.Kristen - I am shrinking the transaction log file, not the database file of the TEMPD. Can this type of shrinking corrupt the database as well?Thank you |
|
|
|
Kristen
Test
22859 Posts |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-05-22 : 12:27:04
|
| Thanks Kristen ... I'll definitely share once I find out the solution ... |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-05-22 : 16:57:49
|
| Guys,I rebooted the machine ... and the problem seems to have vanished. Now my transaction log for the TEMPDB database is at 2GB, but it is using at most 70%. It actually drops down to ~2% every now and then, and then goes up to ~70%, but never exceeds that.I am not sure what the problem was, but apparently, some hanging process/query, was filling up TEMPDB, indefinitely. I learned, once again, that a simple reboot could solve a big problem.Thanks for all the valuable input! |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-22 : 22:59:47
|
| Tempdb is recreated every time you restart sql, it may grow again if you don't solve root cause. |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2007-05-23 : 09:38:10
|
| rmiao, you are right about a general case - that is, the root cause better be solved.However, in my case, the reboot seems to have completely solved it (i.e. its been almost 24 hours since reboot with everything running as usual, and there are no problems).Once again, I believe that there was some lone process running (which should not be running), which, onced killed, by a reboot, solved the issue.Thank you |
|
|
|
|
Database restore causing constant tempdb growth