Growing Log File

20 Sep 2006 4:56 PM

Emma

A third party application we are using uses MS SQL 2000 as its database.
Unfortunately, the log file for the associated database grows from about
500MB to over 40GB in one day and we keep shrinking/truncating the log file
manually.

Are there any tools I can use in determining why the log file is growing so
fast and/or view the content of the file?

Thanks

20 Sep 2006 5:09 PM

Roger

Hi Emma,

I use lumigent log explorer to see the log content.

Hope this help.

Roger

Show quoteHide quote

"Emma" <E***@discussions.microsoft.com> escreveu na mensagem
news:126BA0C5-6C97-4C5F-B32D-B2E2258AD458@microsoft.com...
>A third party application we are using uses MS SQL 2000 as its database.
> Unfortunately, the log file for the associated database grows from about
> 500MB to over 40GB in one day and we keep shrinking/truncating the log
> file
> manually.
>
> Are there any tools I can use in determining why the log file is growing
> so
> fast and/or view the content of the file?
>
> Thanks

20 Sep 2006 5:46 PM

Tibor Karaszi

What recovery model is the database in? Are you doing regular log backups?
What does usage of log file say over the day, using DBCC SQLPERF(LOGSPACE)?

Assuming you do get usage down to, say 500MB, and it repeatedly. over the day grow to 40 GB:
You have to hunt down who is causing this. SQL Server doesn't grow a log file just for fun. It is
likely some batch processing. Possibly index rebuilds (they are logged). Start looking in your Agent
jobs.

Also, check out: http://www.karaszi.com/SQLServer/info_dont_shrink.asp

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

Show quoteHide quote

"Emma" <E***@discussions.microsoft.com> wrote in message
news:126BA0C5-6C97-4C5F-B32D-B2E2258AD458@microsoft.com...
>A third party application we are using uses MS SQL 2000 as its database.
> Unfortunately, the log file for the associated database grows from about
> 500MB to over 40GB in one day and we keep shrinking/truncating the log file
> manually.
>
> Are there any tools I can use in determining why the log file is growing so
> fast and/or view the content of the file?
>
> Thanks

20 Sep 2006 5:48 PM

Tracy McKibben

Emma wrote:

> A third party application we are using uses MS SQL 2000 as its database.
> Unfortunately, the log file for the associated database grows from about
> 500MB to over 40GB in one day and we keep shrinking/truncating the log file
> manually.
>
> Are there any tools I can use in determining why the log file is growing so
> fast and/or view the content of the file?
>
> Thanks

What recovery model (Simple, Full, Bulk-Logged) is the database in? Do
you have a scheduled transaction log backup in place?

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

21 Sep 2006 5:29 AM

Mailhome

Hi ,

we had the same issue and the link which helped me a lot was

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

we had this log file increment in tempdb which the tools like LUGIMENT
and Quest software's Spot light failed to open !

finally found it to be some issue with data queue length and was ok
after a restart
hope it also helps you

thanks
madhava

Emma wrote:
Show quoteHide quote

> A third party application we are using uses MS SQL 2000 as its database.
> Unfortunately, the log file for the associated database grows from about
> 500MB to over 40GB in one day and we keep shrinking/truncating the log file
> manually.
>
> Are there any tools I can use in determining why the log file is growing so
> fast and/or view the content of the file?
>
> Thanks

Thread options