Re: Transaction log too big
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 02/25/05
- Next message: Mary Poppins via SQLMonster.com: "Memory management"
- Previous message: Tibor Karaszi: "Re: why no temp functions?"
- In reply to: Steen Persson: "Re: Transaction log too big"
- Next in thread: Steen Persson: "Re: Transaction log too big"
- Reply: Steen Persson: "Re: Transaction log too big"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 25 Feb 2005 10:11:45 +0100
Good explanation, Steen! Might I just do one clarification, in order to avoid potential confusion
for future readers:
> 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.
For SQL Server backups, the file size is not what determines the size of the backup. The size of the
backup is determined by the used space inside the files. If someone is doing file level backups,
well that is another matter, of course.
Oh, and I also agree that shrinking has a place for "unregular" operations. I'm not an unreasonable
man. :-)
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ http://www.sqlug.se/ "Steen Persson" <SPE@REMOVEdatea.dk> wrote in message news: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: Mary Poppins via SQLMonster.com: "Memory management"
- Previous message: Tibor Karaszi: "Re: why no temp functions?"
- In reply to: Steen Persson: "Re: Transaction log too big"
- Next in thread: Steen Persson: "Re: Transaction log too big"
- Reply: Steen Persson: "Re: Transaction log too big"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
