Re: Transaction log too big
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 02/24/05
- Next message: Cranfield: "Re: 837 patch and MS03-031"
- Previous message: Sophie Guo [MSFT]: "RE: Which Groups and what are effective permissions based on multiple groups?"
- In reply to: spp: "Re: Transaction log too big"
- Next in thread: spp: "Re: Transaction log too big"
- Reply: spp: "Re: Transaction log too big"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 24 Feb 2005 08:22:32 +0100
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: Cranfield: "Re: 837 patch and MS03-031"
- Previous message: Sophie Guo [MSFT]: "RE: Which Groups and what are effective permissions based on multiple groups?"
- In reply to: spp: "Re: Transaction log too big"
- Next in thread: spp: "Re: Transaction log too big"
- Reply: spp: "Re: Transaction log too big"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
Loading
