Ticking Clock - TempDB LDF Getting too big
in [Server Configuration]
|
From: Dragon on 15 Sep 2008 08:45 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 15 Sep 2008 08:54 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 15 Sep 2008 08:54 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 15 Sep 2008 08:59 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 15 Sep 2008 09:05 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. >> >> > >
|
Next
|
Last
Pages: 1 2 Prev: MSSQL / MS Cluster Issue Next: DB Tuning Advisor - Statement does not reference any tables |