February 27, 2008 at 4:05 pm
A friend has advised his companies transaction log has grown to 60GB. I suggested this was because they only take a full database backup each evening (they do not backup transaction logs).
My question(s):
1) I am under the impression that if you backed your transaction logs + took a full database backup then the log file would truncate. Is this correct? (I intend to back up my companies transaction logs hourly + full nightly database backup, however Live system currently not actually Live, still under development).
2) How 'safe' is it to shrink the 60GB transaction log file? What would be the best approach to ensure all transactions are committed (take a transaction log backup I would presume!!). Then..........
Many thanks, looking forward to the day when I can give a little back.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 27, 2008 at 8:05 pm
1) That is correct. The transaction log backup will capture the committed transactions to the backup file/media and remove them from the log file (in other words, truncating it).
2) It is safe enough to shrink it, but do it when the system isn't being used, it will create a bit of disk I/O. Also, decide how big the file needs to be before shrinking it. No point shrinking it to 5MB if it is going to grow to 1GB again (especially if it grows in 1MB chunks. Can you say 'fragmented'?). One way to determine this would be to implement your tran log backups then watch how big they are for at least a week or so. This will give you some idea of how much space the transaction log will need for day-to-day running. Another general rule of thumb I have seen around is to size the transaction log to about 15-25% the size of the data file(s) plus a measure of 'it depends'.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 27, 2008 at 11:23 pm
You are corrct, just note that only a transaction log backup truncated the inactive portion of a llog. A database backup does not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 27, 2008 at 11:59 pm
Many thanks for your responses, as always very much appreciated.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply