| Author |
TempDb "log in use" growing. Fixed by manual checkpoint or SQL Restart
|
|
| Paul Cahill 2006-02-17, 7:23 am |
| SQL 2000 SP4
We had this issue occasionally on our production database but it went away
when we changed from "Simple" recovery model to "Complex"
Everynow and then SQL Server seems to stop automatically checkpointing a
database. The log file in use will then start to grow.
We can fix this by setting up a manual checkpoint job or by stopping and
restarting SQL Server.
Today I noticed this had been happening on tempdb. The tempdb log had grown
to over 10GB.
I checkpointed the database and shrunk tempdb's log back to 500Mb.
There are no long running transactions. "In use" goes above 70% and then
auto file grow fires.
I have tried default and explicit recovery intervals.
A manual checkpoint zaps the in use value back to zero.
I've seen a couple of other posts to this effect, so it's not isolated.
Any ideas.
Regards
Paul Cahill
| |
| Andrew J. Kelly 2006-02-17, 9:23 am |
| See if this is your issue:
http://www.kbalertz.com/Feedback_909369.aspx
--
Andrew J. Kelly SQL MVP
"Paul Cahill" <anon@anon.com> wrote in message
news:%23nkMDI8MGHA.2604@TK2MSFTNGP09.phx.gbl...
> SQL 2000 SP4
>
> We had this issue occasionally on our production database but it went away
> when we changed from "Simple" recovery model to "Complex"
>
> Everynow and then SQL Server seems to stop automatically checkpointing a
> database. The log file in use will then start to grow.
> We can fix this by setting up a manual checkpoint job or by stopping and
> restarting SQL Server.
>
> Today I noticed this had been happening on tempdb. The tempdb log had
> grown to over 10GB.
> I checkpointed the database and shrunk tempdb's log back to 500Mb.
>
> There are no long running transactions. "In use" goes above 70% and then
> auto file grow fires.
> I have tried default and explicit recovery intervals.
> A manual checkpoint zaps the in use value back to zero.
>
> I've seen a couple of other posts to this effect, so it's not isolated.
> Any ideas.
>
> Regards
> Paul Cahill
>
>
>
>
>
>
| |
| Paul Cahill 2006-02-17, 9:23 am |
| Certainly sounds like it.
Thanks Andrew.
Paul
"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
news:%23ApYHi8MGHA.3408@TK2MSFTNGP12.phx.gbl...
> See if this is your issue:
> http://www.kbalertz.com/Feedback_909369.aspx
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Paul Cahill" <anon@anon.com> wrote in message
> news:%23nkMDI8MGHA.2604@TK2MSFTNGP09.phx.gbl...
>
>
|
|
|
|