SQL Server Performance Forum – Threads Archive

Quest about log backup

Hi,
As there was trasactional log backup failure, my LOG grew upto 10.9 GB, when i backedup i could see it is 4.3GB .Actually i expected its size would fall to some few KB .Why this happend? does it mean it still has that much amount of uncommited trasactions ? Any explanation would be appreciated !! –Rajiv

Could be various reasons. How long did the backup take? Whilst the transaction log backup was running you could have had 4.3 GB worth of transactions in that space of time, which accounts for the current size. I’ve seen the log grow by as much as 30 GB in the space of 5 minutes on high-transaction systems. If you want to check if there are any open transactions, run DBCC OPENTRAN in the database concerned. Karl Grambow www.sqldbcontrol.com

Sorry it was a mistake,it was my datafile size i observed as 4.3GB . LOg file size is then and now(after backup) 10.9GB (physical size).I think i need to shrink or truncate to reduce physical size . Now come to batch transactions on some (not all) weekends, hw do i manage this situation?

Look at DBCC SHRINKFILE in books on line. I generally have the job that is going to kick off any volume of work do a backup of the log first so it is clean, do the batch job, backup the log again and then do the shrinkfile.

IF there is a scheduled job contributing Tlog size then you must leave the Tlog to a nominal value where it can reach its size and continue the Tlog backups. I don’t see any reason in SHRINK and leave it to AUTO-GROW when it is required. You are wasting server resources in this manner. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>