Re: Tran Log Growth high tried truncating and shrinking
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 01/05/05
- Next message: Andrew J. Kelly: "Re: Performance degraded after DBCC DBReindex"
- Previous message: bing: "Re: help on SQL UPDATE needed"
- In reply to: Simon: "Re: Tran Log Growth high tried truncating and shrinking"
- Next in thread: Simon: "Re: Tran Log Growth high tried truncating and shrinking"
- Reply: Simon: "Re: Tran Log Growth high tried truncating and shrinking"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 5 Jan 2005 09:44:21 -0500
> I am not shrinking the database - only the T log. And only after trans
> have been committed and a full backup taken. I can't see the harm in
> removing the old transaction data.
It is not the shrinking that is harmful it is the growth that will
inevitably happen that can cause problems. At best it will hamper
performance as the growing of the file is resource intensive and will
temporarily halt any new transactions until the growth is complete. If you
shrink your file to say 1 GB and it grows to 7GB you will have many periods
where the autogrow will kick in and cause potential performance issues. You
can even force the timeout of queries that can not happen in the time it
takes the file to grow. You can use profiler to trace your system and
there are events for file growth. You can then see what was going on in the
system around the time that an autogrow happened. You can even set up an
alert so you can have a look if you are around.
-- Andrew J. Kelly SQL MVP "Simon" <reply@togroup.com> wrote in message news:%23BEevGz8EHA.3368@TK2MSFTNGP10.phx.gbl... > Many thanks Andrew / Olu, > > I am looking to find the cause of the growth but no luck as yet. > > I am not shrinking the database - only the T log. And only after trans > have been committed and a full backup taken. I can't see the harm in > removing the old transaction data. > > Even if maintenance / input tasks are causing a large number of > trtansactions to be written to the db I can't believe the log should reach > 7Gb+ in only a few days. > > Is there any way of seeing when the trans log growth occurs? Does the time > / date get logged anywhere? > > Thanks again for your help it is appreciated, Simon. > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:uw600sy8EHA.3596@TK2MSFTNGP12.phx.gbl... >> Simon, >> >> Shrinking the db all the time is a bad thing. If it keeps growing then >> all you do by shrinking is cause more overhead and potential problems. As >> Olu mentioned you need to determine why it is growing so much before you >> do anything and I too believe the optimizations of the tables are most >> likely causing this and that is normal. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Simon" <reply@togroup.com> wrote in message >> news:uXnYrTy8EHA.808@TK2MSFTNGP10.phx.gbl... >>> Hello all, I have a problem with a SQL 2000 DB LDF growing to 7-8Gb. The >>> DB itself is around 8.5Gb. >>> >>> I have truncated the log and shrunk it back to 500Mb but in 3 - 4 days >>> it returns to its monsterous size. >>> >>> I do a full backup each night and regular backups of the log during the >>> day. A maintenance plan should shrink and tidy every Sunday (the default >>> setting I believe.) >>> >>> All advice is greatly appreciated, rgds, Simon. >>> >> >> > >
- Next message: Andrew J. Kelly: "Re: Performance degraded after DBCC DBReindex"
- Previous message: bing: "Re: help on SQL UPDATE needed"
- In reply to: Simon: "Re: Tran Log Growth high tried truncating and shrinking"
- Next in thread: Simon: "Re: Tran Log Growth high tried truncating and shrinking"
- Reply: Simon: "Re: Tran Log Growth high tried truncating and shrinking"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
