March 8, 2005 at 12:29 am
Hi,
Our Transactionlog is getting bigger every day. When I make a backup of the transactionlog with the enterprise manager, I see the logical part is smal and the physical file is big, but when I shrink the file with DBCC Shrinkfile the file is not getting smaller. When I backup the transactionlog again and afterwards I shrink the file, the physical file is getting smaller. How is this possible?
Ed
March 8, 2005 at 12:55 am
See Books Online topics 'Shrinking the transaction Log', 'Truncated transaction logs' and 'Transaction Log Physical Architecture' for explanation, you can use undocumented command DBCC LOGINFO ('db_name') to view virtual logs making up physical transaction log. In its output see status 2 - these are the parts of active portion of the log.
Do you really need to regularly shrink the log? Why don't you set it to sufficient size to hold your workload and just perform log backups?
March 9, 2005 at 11:51 am
Good transaction log maintenance means consistent transaction log size and consistent transaction log backups in my book (this assumes a stable and well written application, whether 'home-grown or from a vendor). Our policy is to set the log size at 10-20% for a start with log backups every 30 minutes. However this is just a starting point. You must monitor and benchmark things to find the 'final happy medium'. This 'medium' can vary from server to server as well as from database to database on a given server. Also, we always limit 'autogrow' to 50% of the size to prevent disk fragmentation and space issues. As an example one of our heavily used production databases (it has discreet online and batch processing windows) is set up as follows:
DB data:
Initial Size: 10240 Mb
Growth: 5120 Mb
Restrict Growth to: 20481 Mb
DB Log:
Initial Size: 1024 Mb
Growth: 512 Mb
Restrick Growth to: 1537 Mb
(at preesent we have 400+ users and about 8 Gb of the data space used in this database)
We execute transaction log backup every 15 minutes. The sizes are typically less than 100 Mb during the day's onilne window. At night, when batch processing begins they vary from 100 Mb to 900 Mb during heavy batch processess. So, the bottom line is: NO, you do not have to 'shrink' tranasction logs regulary (provided you've done your home work !).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
September 14, 2005 at 1:19 pm
I was recommened that on Maximum file size , it should be set to "Unrestricted file size". Can you please elaborate as how does setting it some maximum value controls the file size and what happens when log reaches that defined size?
thanks
September 14, 2005 at 1:41 pm
"Unrestricted file size" ? Do you have "unrestricted disk capacity" ? Need I say more here ?
As for "what happens" whe the log file reaches the maximum size, well, transactions against the database stop being processed. However, as I mentioned earlier in the post, with regular transaction log backups, some monitoring and some size adjustment, this should never happen.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply