Hi,

We are running SQL 2000 and for some reason TempDB LDF is getting too big.
TEMPDB MDF is only about 35MB while LDF has grown to 11+GB in three days. I
have confirmed that it is growing by a few MB every few seconds. I read a
prior post with similar issue and ran the commands mentioned. Here are the
results.

use tempdb
go
DBCC OPENTRAN
*** No Open Transactions ***


DBCC INPUTBUFFER(54);
****
EventType Parameters EventInfo
-------------- ---------- ----------------------------------------------
Language Event 0 EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters

Any ideas how do I go about finding out what might be causing this to grow
this quick and this large?

Thank you.

Dragon
http://sqlserver2000.databases.aspfa...happening.html




"Dragon" <nospam_baadil@hotmail.com> wrote in message
news:%23kkPgDzFJHA.4240@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> We are running SQL 2000 and for some reason TempDB LDF is getting too big.
> TEMPDB MDF is only about 35MB while LDF has grown to 11+GB in three days.
> I have confirmed that it is growing by a few MB every few seconds. I read
> a prior post with similar issue and ran the commands mentioned. Here are
> the results.
>
> use tempdb
> go
> DBCC OPENTRAN
> *** No Open Transactions ***
>
>
> DBCC INPUTBUFFER(54);
> ****
> EventType Parameters EventInfo
> -------------- ---------- ----------------------------------------------
> Language Event 0 EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
>
> Any ideas how do I go about finding out what might be causing this to grow
> this quick and this large?
>
> Thank you.
>

Try running DBCC OPENTRAN in the other DB's. Keep in mind that a
transaction can span databases.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Dragon" <nospam_baadil@hotmail.com> wrote in message
news:%23kkPgDzFJHA.4240@TK2MSFTNGP06.phx.gbl...
Hi,

We are running SQL 2000 and for some reason TempDB LDF is getting too big.
TEMPDB MDF is only about 35MB while LDF has grown to 11+GB in three days. I
have confirmed that it is growing by a few MB every few seconds. I read a
prior post with similar issue and ran the commands mentioned. Here are the
results.

use tempdb
go
DBCC OPENTRAN
*** No Open Transactions ***


DBCC INPUTBUFFER(54);
****
EventType Parameters EventInfo
-------------- ---------- ----------------------------------------------
Language Event 0 EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters

Any ideas how do I go about finding out what might be causing this to grow
this quick and this large?

Thank you.

Also, try DBCC SQLPERF(logspace) and executing the CHECKPOINT command while in tempdb to see if that
clear things up.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:%23d2jNJzFJHA.3408@TK2MSFTNGP04.phx.gbl...
> Try running DBCC OPENTRAN in the other DB's. Keep in mind that a
> transaction can span databases.
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
>
> "Dragon" <nospam_baadil@hotmail.com> wrote in message
> news:%23kkPgDzFJHA.4240@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> We are running SQL 2000 and for some reason TempDB LDF is getting too big.
> TEMPDB MDF is only about 35MB while LDF has grown to 11+GB in three days. I
> have confirmed that it is growing by a few MB every few seconds. I read a
> prior post with similar issue and ran the commands mentioned. Here are the
> results.
>
> use tempdb
> go
> DBCC OPENTRAN
> *** No Open Transactions ***
>
>
> DBCC INPUTBUFFER(54);
> ****
> EventType Parameters EventInfo
> -------------- ---------- ----------------------------------------------
> Language Event 0 EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
>
> Any ideas how do I go about finding out what might be causing this to grow
> this quick and this large?
>
> Thank you.
>
>

Thank you Tibor.
I ran it against all of the database but no open trans.

Any other ideas?

Thanks.


"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in
message news:e%23x7TLzFJHA.2508@TK2MSFTNGP06.phx.gbl...
> Also, try DBCC SQLPERF(logspace) and executing the CHECKPOINT command
> while in tempdb to see if that clear things up.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
> news:%23d2jNJzFJHA.3408@TK2MSFTNGP04.phx.gbl...
>> Try running DBCC OPENTRAN in the other DB's. Keep in mind that a
>> transaction can span databases.
>>
>> --
>> Tom
>>
>> ----------------------------------------------------
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>>
>> "Dragon" <nospam_baadil@hotmail.com> wrote in message
>> news:%23kkPgDzFJHA.4240@TK2MSFTNGP06.phx.gbl...
>> Hi,
>>
>> We are running SQL 2000 and for some reason TempDB LDF is getting too
>> big.
>> TEMPDB MDF is only about 35MB while LDF has grown to 11+GB in three days.
>> I
>> have confirmed that it is growing by a few MB every few seconds. I read a
>> prior post with similar issue and ran the commands mentioned. Here are
>> the
>> results.
>>
>> use tempdb
>> go
>> DBCC OPENTRAN
>> *** No Open Transactions ***
>>
>>
>> DBCC INPUTBUFFER(54);
>> ****
>> EventType Parameters EventInfo
>> -------------- ---------- ----------------------------------------------
>> Language Event 0 EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
>>
>> Any ideas how do I go about finding out what might be causing this to
>> grow
>> this quick and this large?
>>
>> Thank you.
>>
>>
>
>

Thank you Uri. I will take a look at the link.


"Uri Dimant" <urid@iscar.co.il> wrote in message
news:uYWnrHzFJHA.5224@TK2MSFTNGP03.phx.gbl...
> Dragon
> http://sqlserver2000.databases.aspfa...happening.html
>
>
>
>
> "Dragon" <nospam_baadil@hotmail.com> wrote in message
> news:%23kkPgDzFJHA.4240@TK2MSFTNGP06.phx.gbl...
>> Hi,
>>
>> We are running SQL 2000 and for some reason TempDB LDF is getting too
>> big. TEMPDB MDF is only about 35MB while LDF has grown to 11+GB in three
>> days. I have confirmed that it is growing by a few MB every few seconds.
>> I read a prior post with similar issue and ran the commands mentioned.
>> Here are the results.
>>
>> use tempdb
>> go
>> DBCC OPENTRAN
>> *** No Open Transactions ***
>>
>>
>> DBCC INPUTBUFFER(54);
>> ****
>> EventType Parameters EventInfo
>> -------------- ---------- ----------------------------------------------
>> Language Event 0 EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
>>
>> Any ideas how do I go about finding out what might be causing this to
>> grow this quick and this large?
>>
>> Thank you.
>>
>
>

None of the commands I suggested reports open transactions. I suggested checking used space for the
log area and also that manually running the CHECKPINT command might clear things up (and if that is
the case you want to Google and possibly apply a CU/hotfix).

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Dragon" <nospam_baadil@hotmail.com> wrote in message news:upVMMPzFJHA.4460@TK2MSFTNGP02.phx.gbl...
> Thank you Tibor.
> I ran it against all of the database but no open trans.
>
> Any other ideas?
>
> Thanks.
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in message
> news:e%23x7TLzFJHA.2508@TK2MSFTNGP06.phx.gbl...
>> Also, try DBCC SQLPERF(logspace) and executing the CHECKPOINT command while in tempdb to see if
>> that clear things up.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
>> news:%23d2jNJzFJHA.3408@TK2MSFTNGP04.phx.gbl...
>>> Try running DBCC OPENTRAN in the other DB's. Keep in mind that a
>>> transaction can span databases.
>>>
>>> --
>>> Tom
>>>
>>> ----------------------------------------------------
>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>>> SQL Server MVP
>>> Toronto, ON Canada
>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>>
>>>
>>> "Dragon" <nospam_baadil@hotmail.com> wrote in message
>>> news:%23kkPgDzFJHA.4240@TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>>
>>> We are running SQL 2000 and for some reason TempDB LDF is getting too big.
>>> TEMPDB MDF is only about 35MB while LDF has grown to 11+GB in three days. I
>>> have confirmed that it is growing by a few MB every few seconds. I read a
>>> prior post with similar issue and ran the commands mentioned. Here are the
>>> results.
>>>
>>> use tempdb
>>> go
>>> DBCC OPENTRAN
>>> *** No Open Transactions ***
>>>
>>>
>>> DBCC INPUTBUFFER(54);
>>> ****
>>> EventType Parameters EventInfo
>>> -------------- ---------- ----------------------------------------------
>>> Language Event 0 EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
>>>
>>> Any ideas how do I go about finding out what might be causing this to grow
>>> this quick and this large?
>>>
>>> Thank you.
>>>
>>>
>>
>>
>
>

Sorry Tibor. I was replying to the earlier post and I guess I mixed the
names and didn't see your names.

Anyway,
I have confirmed that checkpoint does bring the used log file to the start.
I shrunk the DB and let it auto grow by 10MB to make sure I can keep and eye
on it and by running CheckPoint I saw that I wsa able to keep the file size
from growning.

I will goodle for Checkpoint hotfix.

Thanks.


"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in
message news:%23kXtVYzFJHA.768@TK2MSFTNGP05.phx.gbl...
> None of the commands I suggested reports open transactions. I suggested
> checking used space for the log area and also that manually running the
> CHECKPINT command might clear things up (and if that is the case you want
> to Google and possibly apply a CU/hotfix).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Dragon" <nospam_baadil@hotmail.com> wrote in message
> news:upVMMPzFJHA.4460@TK2MSFTNGP02.phx.gbl...
>> Thank you Tibor.
>> I ran it against all of the database but no open trans.
>>
>> Any other ideas?
>>
>> Thanks.
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote
>> in message news:e%23x7TLzFJHA.2508@TK2MSFTNGP06.phx.gbl...
>>> Also, try DBCC SQLPERF(logspace) and executing the CHECKPOINT command
>>> while in tempdb to see if that clear things up.
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://sqlblog.com/blogs/tibor_karaszi
>>>
>>>
>>> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
>>> news:%23d2jNJzFJHA.3408@TK2MSFTNGP04.phx.gbl...
>>>> Try running DBCC OPENTRAN in the other DB's. Keep in mind that a
>>>> transaction can span databases.
>>>>
>>>> --
>>>> Tom
>>>>
>>>> ----------------------------------------------------
>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>>>> SQL Server MVP
>>>> Toronto, ON Canada
>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>>>
>>>>
>>>> "Dragon" <nospam_baadil@hotmail.com> wrote in message
>>>> news:%23kkPgDzFJHA.4240@TK2MSFTNGP06.phx.gbl...
>>>> Hi,
>>>>
>>>> We are running SQL 2000 and for some reason TempDB LDF is getting too
>>>> big.
>>>> TEMPDB MDF is only about 35MB while LDF has grown to 11+GB in three
>>>> days. I
>>>> have confirmed that it is growing by a few MB every few seconds. I read
>>>> a
>>>> prior post with similar issue and ran the commands mentioned. Here are
>>>> the
>>>> results.
>>>>
>>>> use tempdb
>>>> go
>>>> DBCC OPENTRAN
>>>> *** No Open Transactions ***
>>>>
>>>>
>>>> DBCC INPUTBUFFER(54);
>>>> ****
>>>> EventType Parameters EventInfo
>>>> -------------- ---------- ----------------------------------------------
>>>> Language Event 0 EXECUTE
>>>> msdb.dbo.sp_sqlagent_get_perf_counters
>>>>
>>>> Any ideas how do I go about finding out what might be causing this to
>>>> grow
>>>> this quick and this large?
>>>>
>>>> Thank you.
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Cool, seems you have found the issue. I vaguely recall this problem and there's a hotfix available.
Hopefully you'll find such.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Dragon" <nospam_baadil@hotmail.com> wrote in message news:Owk0UizFJHA.5448@TK2MSFTNGP04.phx.gbl...
> Sorry Tibor. I was replying to the earlier post and I guess I mixed the names and didn't see your
> names.
>
> Anyway,
> I have confirmed that checkpoint does bring the used log file to the start. I shrunk the DB and
> let it auto grow by 10MB to make sure I can keep and eye on it and by running CheckPoint I saw
> that I wsa able to keep the file size from growning.
>
> I will goodle for Checkpoint hotfix.
>
> Thanks.
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in message
> news:%23kXtVYzFJHA.768@TK2MSFTNGP05.phx.gbl...
>> None of the commands I suggested reports open transactions. I suggested checking used space for
>> the log area and also that manually running the CHECKPINT command might clear things up (and if
>> that is the case you want to Google and possibly apply a CU/hotfix).
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "Dragon" <nospam_baadil@hotmail.com> wrote in message
>> news:upVMMPzFJHA.4460@TK2MSFTNGP02.phx.gbl...
>>> Thank you Tibor.
>>> I ran it against all of the database but no open trans.
>>>
>>> Any other ideas?
>>>
>>> Thanks.
>>>
>>>
>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in message
>>> news:e%23x7TLzFJHA.2508@TK2MSFTNGP06.phx.gbl...
>>>> Also, try DBCC SQLPERF(logspace) and executing the CHECKPOINT command while in tempdb to see if
>>>> that clear things up.
>>>>
>>>> --
>>>> Tibor Karaszi, SQL Server MVP
>>>> http://www.karaszi.com/sqlserver/default.asp
>>>> http://sqlblog.com/blogs/tibor_karaszi
>>>>
>>>>
>>>> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
>>>> news:%23d2jNJzFJHA.3408@TK2MSFTNGP04.phx.gbl...
>>>>> Try running DBCC OPENTRAN in the other DB's. Keep in mind that a
>>>>> transaction can span databases.
>>>>>
>>>>> --
>>>>> Tom
>>>>>
>>>>> ----------------------------------------------------
>>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>>>>> SQL Server MVP
>>>>> Toronto, ON Canada
>>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>>>>
>>>>>
>>>>> "Dragon" <nospam_baadil@hotmail.com> wrote in message
>>>>> news:%23kkPgDzFJHA.4240@TK2MSFTNGP06.phx.gbl...
>>>>> Hi,
>>>>>
>>>>> We are running SQL 2000 and for some reason TempDB LDF is getting too big.
>>>>> TEMPDB MDF is only about 35MB while LDF has grown to 11+GB in three days. I
>>>>> have confirmed that it is growing by a few MB every few seconds. I read a
>>>>> prior post with similar issue and ran the commands mentioned. Here are the
>>>>> results.
>>>>>
>>>>> use tempdb
>>>>> go
>>>>> DBCC OPENTRAN
>>>>> *** No Open Transactions ***
>>>>>
>>>>>
>>>>> DBCC INPUTBUFFER(54);
>>>>> ****
>>>>> EventType Parameters EventInfo
>>>>> -------------- ---------- ----------------------------------------------
>>>>> Language Event 0 EXECUTE msdb.dbo.sp_sqlagent_get_perf_counters
>>>>>
>>>>> Any ideas how do I go about finding out what might be causing this to grow
>>>>> this quick and this large?
>>>>>
>>>>> Thank you.
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Yes Tibor I got it. Thank you for .

Until we apply the hotfix, I created a SQL Job to simply run the checkpoint
every minute. It takes only 15ms and keeps my TempDB happy. Server resources
are not taxed anyway.


"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote in
message news:ezUlU49FJHA.2112@TK2MSFTNGP02.phx.gbl...
> Cool, seems you have found the issue. I vaguely recall this problem and
> there's a hotfix available. Hopefully you'll find such.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Dragon" <nospam_baadil@hotmail.com> wrote in message
> news:Owk0UizFJHA.5448@TK2MSFTNGP04.phx.gbl...
>> Sorry Tibor. I was replying to the earlier post and I guess I mixed the
>> names and didn't see your names.
>>
>> Anyway,
>> I have confirmed that checkpoint does bring the used log file to the
>> start. I shrunk the DB and let it auto grow by 10MB to make sure I can
>> keep and eye on it and by running CheckPoint I saw that I wsa able to
>> keep the file size from growning.
>>
>> I will goodle for Checkpoint hotfix.
>>
>> Thanks.
>>
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m> wrote
>> in message news:%23kXtVYzFJHA.768@TK2MSFTNGP05.phx.gbl...
>>> None of the commands I suggested reports open transactions. I suggested
>>> checking used space for the log area and also that manually running the
>>> CHECKPINT command might clear things up (and if that is the case you
>>> want to Google and possibly apply a CU/hotfix).
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://sqlblog.com/blogs/tibor_karaszi
>>>
>>>
>>> "Dragon" <nospam_baadil@hotmail.com> wrote in message
>>> news:upVMMPzFJHA.4460@TK2MSFTNGP02.phx.gbl...
>>>> Thank you Tibor.
>>>> I ran it against all of the database but no open trans.
>>>>
>>>> Any other ideas?
>>>>
>>>> Thanks.
>>>>
>>>>
>>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.co m>
>>>> wrote in message news:e%23x7TLzFJHA.2508@TK2MSFTNGP06.phx.gbl...
>>>>> Also, try DBCC SQLPERF(logspace) and executing the CHECKPOINT command
>>>>> while in tempdb to see if that clear things up.
>>>>>
>>>>> --
>>>>> Tibor Karaszi, SQL Server MVP
>>>>> http://www.karaszi.com/sqlserver/default.asp
>>>>> http://sqlblog.com/blogs/tibor_karaszi
>>>>>
>>>>>
>>>>> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
>>>>> news:%23d2jNJzFJHA.3408@TK2MSFTNGP04.phx.gbl...
>>>>>> Try running DBCC OPENTRAN in the other DB's. Keep in mind that a
>>>>>> transaction can span databases.
>>>>>>
>>>>>> --
>>>>>> Tom
>>>>>>
>>>>>> ----------------------------------------------------
>>>>>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>>>>>> SQL Server MVP
>>>>>> Toronto, ON Canada
>>>>>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>>>>>
>>>>>>
>>>>>> "Dragon" <nospam_baadil@hotmail.com> wrote in message
>>>>>> news:%23kkPgDzFJHA.4240@TK2MSFTNGP06.phx.gbl...
>>>>>> Hi,
>>>>>>
>>>>>> We are running SQL 2000 and for some reason TempDB LDF is getting too
>>>>>> big.
>>>>>> TEMPDB MDF is only about 35MB while LDF has grown to 11+GB in three
>>>>>> days. I
>>>>>> have confirmed that it is growing by a few MB every few seconds. I
>>>>>> read a
>>>>>> prior post with similar issue and ran the commands mentioned. Here
>>>>>> are the
>>>>>> results.
>>>>>>
>>>>>> use tempdb
>>>>>> go
>>>>>> DBCC OPENTRAN
>>>>>> *** No Open Transactions ***
>>>>>>
>>>>>>
>>>>>> DBCC INPUTBUFFER(54);
>>>>>> ****
>>>>>> EventType Parameters EventInfo
>>>>>> -------------- ---------- ----------------------------------------------
>>>>>> Language Event 0 EXECUTE
>>>>>> msdb.dbo.sp_sqlagent_get_perf_counters
>>>>>>
>>>>>> Any ideas how do I go about finding out what might be causing this to
>>>>>> grow
>>>>>> this quick and this large?
>>>>>>
>>>>>> Thank you.
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>