Ticking Clock - TempDB LDF Getting too big
in [Server Configuration]
|
From: Dragon on 15 Sep 2008 09:06 Thank you Uri. I will take a look at the link. "Uri Dimant" <urid(a)iscar.co.il> wrote in message news:uYWnrHzFJHA.5224(a)TK2MSFTNGP03.phx.gbl... > 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: Tibor Karaszi on 15 Sep 2008 09:22 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(a)hotmail.com> wrote in message news:upVMMPzFJHA.4460(a)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(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. >>> >>> >> >> > >
From: Dragon on 15 Sep 2008 09:40 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(a)hotmail.nomail.com> wrote in message news:%23kXtVYzFJHA.768(a)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(a)hotmail.com> wrote in message > news:upVMMPzFJHA.4460(a)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(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. >>>> >>>> >>> >>> >> >> > >
From: Tibor Karaszi on 16 Sep 2008 05:24 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(a)hotmail.com> wrote in message news:Owk0UizFJHA.5448(a)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(a)hotmail.nomail.com> wrote in message > news:%23kXtVYzFJHA.768(a)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(a)hotmail.com> wrote in message >> news:upVMMPzFJHA.4460(a)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(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. >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
From: Dragon on 16 Sep 2008 08:25 Yes Tibor I got it. Thank you for help. 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(a)hotmail.nomail.com> wrote in message news:ezUlU49FJHA.2112(a)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(a)hotmail.com> wrote in message > news:Owk0UizFJHA.5448(a)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(a)hotmail.nomail.com> wrote >> in message news:%23kXtVYzFJHA.768(a)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(a)hotmail.com> wrote in message >>> news:upVMMPzFJHA.4460(a)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(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. >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > >
First
|
Prev
|
Pages: 1 2 Prev: MSSQL / MS Cluster Issue Next: DB Tuning Advisor - Statement does not reference any tables |