From: Dragon on
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.


From: Uri Dimant on
Dragon
http://sqlserver2000.databases.aspfaq.com/why-is-tempdb-full-and-how-can-i-prevent-this-from-happening.html




"Dragon" <nospam_baadil(a)hotmail.com> wrote in message
news:%23kkPgDzFJHA.4240(a)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.
>


From: Tom Moreau on
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(a)hotmail.com> wrote in message
news:%23kkPgDzFJHA.4240(a)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.


From: Tibor Karaszi on
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(a)dont.spam.me.cips.ca> wrote in message
news:%23d2jNJzFJHA.3408(a)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(a)hotmail.com> wrote in message
> news:%23kkPgDzFJHA.4240(a)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.
>
>


From: Dragon on
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(a)hotmail.nomail.com> wrote in
message news:e%23x7TLzFJHA.2508(a)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(a)dont.spam.me.cips.ca> wrote in message
> news:%23d2jNJzFJHA.3408(a)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(a)hotmail.com> wrote in message
>> news:%23kkPgDzFJHA.4240(a)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.
>>
>>
>
>