Re: Transaction log too big
From: spp (spp_at_nospam.nospam)
Date: 02/25/05
- Next message: Tibor Karaszi: "Re: Indexes"
- Previous message: David Gugick: "Re: Login as sa"
- In reply to: Steen Persson: "Re: Transaction log too big"
- Next in thread: Tibor Karaszi: "Re: Transaction log too big"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 25 Feb 2005 07:35:40 +0200
Thank's very much for your replies.
Now I understand very well.
It seems I don't need to shrink file every day.
Thank's again.
"Steen Persson" <SPE@REMOVEdatea.dk> a scris în mesajul de
știri:OV1zJemGFHA.2976@TK2MSFTNGP09.phx.gbl...
>I don't if I can do it any better, but I'll try
>
> Let's say your log file takes up 200 Mb on your harddrive. The file is
> e.g.
> 75 % full which means it contains 150 Mb log data. You then backup the
> file
> and then it gets truncated - i.e. it's empty. It's only the logical file
> that's empty though - the physical file will still have the same size on
> disk. If the database is in "normal" operation, you'd expect that the
> logical log will grow to the same size until next time you perform a
> backup,
> so therefore there are no reason for reduxingthe physical size of the log
> i.e. shrinking it. If you'd shrunk the log after the backup, the physical
> size of the file would have been reduced to whatever size you'd specified
> (or minimum the default size of the log file). Now the log will be filled
> up
> again during normal operations, but sinze the physical file is still the
> same size, it hasn't got to grow since you hasn't done the shrinkfile. If
> there are more "activity" in the database now that there where in the
> past,
> it might be that the logfile will have to grow if the 200 Mb physical size
> isn't enough - and it will then do so if you have enabled autogrow (and
> have
> enough diskspace for it to grow on). Now your logfile might grow to 300
> Mb,
> so the next time you do a backup, you'll empty the logical log file, but
> the
> physical file stays the same - 300 Mb.
>
> The only cases where it's worth shrinking a log file (or a databasae file
> for that matter) is when the files have grown very big due to some
> "unregular" operations. With that I mean that if there has been done some
> operations that only occours once or maybe only once a year or so, the it
> might not be worth "carrying around" with a huge log file in backups etc.
> if
> you know it's never gonna need that much space again. If that's not the
> case
> in your situation, I'd just consider leaving the physical log filse size
> as
> it is.
>
> I hope that the above is correct - otherwise somebody must correct me. I'm
> not a full-blown expert in log file architecture, but I feel I get the
> headlines...:-).
>
> I don't know if this helped you any more or if we totally miss your point.
> I
> have the felling that we don't quite understand your issue since you keep
> having problems understanding what we are trying to tell you. As Tibor
> also
> mentioned, you can read about truncating Transaction log and related
> topics
> in BOL under SQL Server Architecture
>
> Regards
> Steen
>
>
> Tibor Karaszi wrote:
>> I suggest you read in Books Online about how backup and restore work.
>> I tried to explain that the log file has a size, and that file can be
>> used in various degrees (from 1% to 100% full). There is no penalty
>> to have a log file which isn't full. Au contraire, having allocated
>> space in a log file means that the forth coming log records doesn't
>> mean that SQL server have to grow the file as you do modifications in
>> the database.
>>
>> It seems that I have little luck explaining this, possibly because
>> language barriers (I'm Swedish). Perhaps someone else can jump in and
>> explain this better. Else, I, again, suggest you read up on the topic
>> in Books Online.
>>
>>
>> "spp" <spp@nospam.nospam> wrote in message
>> news:%23uDo5BlGFHA.3876@TK2MSFTNGP14.phx.gbl...
>>> Exactly that's may problem
>>> After I bakup the log it's remaining with the same size !?
>>>
>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> a
>>> scris în mesajul de știri:OaMebGkGFHA.544@TK2MSFTNGP12.phx.gbl...
>>>> Think of the backup file as a physical file. And the modifications
>>>> you perform are recorded in this physical file. Each time you do
>>>> log backup, you empty the physical file.
>>>>
>>>> --
>>>> Tibor Karaszi, SQL Server MVP
>>>> http://www.karaszi.com/sqlserver/default.asp
>>>> http://www.solidqualitylearning.com/
>>>>
>>>>
>>>> "spp" <spp@nospam.nospam> wrote in message
>>>> news:uH0OHCjGFHA.584@TK2MSFTNGP14.phx.gbl...
>>>>> I made a backup every night.
>>>>> Between them I let the log to grow as big as it's need (I saw that
>>>>> about 200 MB is enough).
>>>>> If I let the log to be as big as it's need it will be grow as much
>>>>> as 20GB (3 days ago was that big!!!!).
>>>>> So I think only a shrink could reduce it's size because backup
>>>>> log doesn't reduce it.
>>>>>
>>>>> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com>
>>>>> a scris în mesajul de
>>>>> știri:OI4gCtZGFHA.2588@TK2MSFTNGP09.phx.gbl...
>>>>>> Why not let this container (the log file) be as big as it need to
>>>>>> be for the modifications performed between your backup log
>>>>>> operations? There's no penalty for having a large file. However,
>>>>>> growing and shrinking costs performance.
>>>>>>
>>>>>> --
>>>>>> Tibor Karaszi, SQL Server MVP
>>>>>> http://www.karaszi.com/sqlserver/default.asp
>>>>>> http://www.solidqualitylearning.com/
>>>>>>
>>>>>>
>>>>>> "spp" <spp_users@hotmail.com> wrote in message
>>>>>> news:OO4r3kZGFHA.1084@tk2msftngp13.phx.gbl...
>>>>>>> Thank's for reply
>>>>>>> I must reduce the size because after bakup log it doesn't reduce
>>>>>>> and If I let it it grows very big.
>>>>>>> I put a maximum size (about 200 MB) and it's reaching this value
>>>>>>> in 2 days. So now when I backup my database the last thing will
>>>>>>> be a DBCC SHRINKFILE. It's OK ?
>>>>>>>
>>>>>>> "Steen Persson" <SPE@REMOVEdatea.dk> wrote in message
>>>>>>> news:ucLnq$XGFHA.624@TK2MSFTNGP15.phx.gbl...
>>>>>>>> There are no problems in performing a DBCC SHRINKFILE as such.
>>>>>>>> Tibor
>>>>>>> mention
>>>>>>>> the pro's and con's in his excellent article so if you still
>>>>>>>> feel that
>>>>>>> you
>>>>>>>> want to shrink the file after reading that, then just go ahead.
>>>>>>>> If you
>>>>>>> know
>>>>>>>> it's a "one timer" that the file has grown so big, then it
>>>>>>>> might be ok to shrink it. If you know it's going to be that
>>>>>>>> size on a regular basis, then I'd just leave it like it is.
>>>>>>>>
>>>>>>>> You'll need to do a backup before the shrink. Mostly because
>>>>>>>> you need to truncate the log in order reduze the logical log
>>>>>>>> size, but I'd also do it just for safety reason if something
>>>>>>>> goes wrong during the shrinkfile operation.
>>>>>>>>
>>>>>>>> Regards
>>>>>>>> Steen
>>>>>>>>
>>>>>>>>
>>>>>>>> spp wrote:
>>>>>>>>> Do I have to concern about using DBCC SHRINKFILE ?
>>>>>>>>> With it I solved my problem.
>>>>>>>>> Should I use it before Backup database ?
>>>>>>>>>
>>>>>>>>> "Tibor Karaszi"
>>>>>>>>> <tibor_please.no.email_karaszi@hotmail.nomail.com> a scris în
>>>>>>>>> mesajul de știri:e14YsPOGFHA.2180@TK2MSFTNGP12.phx.gbl...
>>>>>>>>>> I have listed some KB articles that help you with this in
>>>>>>>>>> below article:
>>>>>>>>>> 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/
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> "spp" <spp@nospam.nospam> wrote in message
>>>>>>>>>> news:%23%234$WMOGFHA.2296@TK2MSFTNGP15.phx.gbl...
>>>>>>>>>>> Today I found that my transaction log is about 20 GB
>>>>>>>>>>> After a Maintenace Plan it becomes about 5 MB.
>>>>>>>>>>> But at the end of the day it grows to 180 MB even I made a
>>>>>>>>>>> full backup and an transaction log backup every hour.
>>>>>>>>>>> I've tryed even WITH TRUNCATE_ONLY but it's the same big.
>>>>>>>>>>> I have full recovery model for my database
>>>>>>>>>>> What shoul I do to maintain it in a reasonably size.
>
>
- Next message: Tibor Karaszi: "Re: Indexes"
- Previous message: David Gugick: "Re: Login as sa"
- In reply to: Steen Persson: "Re: Transaction log too big"
- Next in thread: Tibor Karaszi: "Re: Transaction log too big"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
