Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
efelito
Constraint Violating Yak Guru
478 Posts |
 Posted - 2002-07-26 : 11:01:11
|
| I've got an odd situation on a server I just put into production at the begining of the week. The transaction log in my tempdb database seems to be filling up for some reason. I notice today that it was about 70% full (300 MB), but no space was being used in the data file and there were no open transactions. On a whim I manually ran checkpoint in QA, and it cleared right up. It's almost like checkpoints haven't been firing in tempdb for some reason. The truncate on checkpoint option is set in tempdb, like it should be. Just wondering if any one else has seen this behavior before. I'll keep a close eye on the server to see if it creaps up again. I'm running SQL 7 SP4 on Win2K SP1.Jeff BanschbachConsultant, MCDBA |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-07-26 : 11:26:37
|
quote:
BOL:If the database is in log truncate mode, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values: - The log becomes 70 percent full. - The number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.
All it takes is posting on this site for the magic answer to appear in BOL. I think I have the answer. It looks like tempdb only checkpoints when it gets 70 percent full. The tempdb on the server I was worried about was at 69.87% when I ran the manual checkpoint. I don't think the second rule applies to tempdb since it never has to be recovered. I checked my other servers and they all seem to be behaving the same way. Just caught my eye on this server because the transaction log is fairly large. I learned something new already today! Does that mean I can go home now??  Jeff BanschbachConsultant, MCDBA |
 |
|
|
|
|
|
tempdb Transaction Log Filling