August 9, 2004 at 8:47 am
Hi
Does anyone know how the virtual log file size is determined by SQL Server and whether it is possible to adjust it?
I have inherited a db which has been set to Full Recovery Mode but has not had the transaction log backed up and it had grown to several gb. I've set the Recovery Mode to Simple, backed up the log and shrunk the log file but I couldn't get it to any smaller than 50mb.
I wondered if there might be open transactions but DBCC Opentran confirmed that there were not and the size of the log file for the model DB is 1mb. Finally I ran DBCC LogInfo and realised that my problem was that I had the minimum two virtual log files but that they were 25mb each in size.
This actually is not a particular problem but then I started looking at some of my other, busier databases (which are set to Full Revovery Mode and the Logs of which are backed up hourly) and noticed that I seem to have a lot of much smaller virtual log files comprising my transaction log. This, BOL tells me, can hinder recovery performance but I can't find anywhere describing what should be done about it.
Any suggestions?
Thanks
August 9, 2004 at 8:53 am
just out interest, if you switched to simple mode, why did you back up the log ??
just run a checkpoint command????????
MVDBA
August 9, 2004 at 9:57 am
Just to clarify, I backed up the log using the WITH TRUNCATE_ONLY option. I'm not aware of any practical difference between that and issuing a CHECKPOINT statement for a db in simple recovery mode. Is there any?
Sean
August 9, 2004 at 10:30 am
The small virtual log files is the growth increment which you provide for your transaction logs. Suppose if you have the logfile set to default limits of 10 % increment and 1 mb size, the log file would grow by 10 % only i.e. .1 mb. Now, if you have a long running transaction and your log file fills up, your transaction will have to wait until it grows further and this cycle would continue resulting in a performance hit. Not only for long transaction but also if you have many transaction being written to log file, you might run into the same scenario. The workaround is size your growth factor to a substantial size.
August 10, 2004 at 11:18 am
From BOL.
"
Each transaction log file is divided logically into smaller segments called virtual log files. Virtual log files are the unit of truncation for the transaction log. When a virtual log file no longer contains log records for active transactions, it can be truncated and the space becomes available to log new transactions.
The smallest size for a virtual log file is 256 kilobytes (KB). The minimum size for a transaction log is 512 KB, which provides two 256-KB virtual log files. The number and size of the virtual log files in a transaction log increase as the size of the log file increases. A small log file can have a small number of small virtual log files (for example, a 5-MB log file that comprises five 1-MB virtual log files). A large log file can have larger virtual log files (for example, a 500-MB log file that comprises ten 50-MB virtual log files).
Microsoft® SQL Server™ 2000 tries to avoid having many small virtual log files. The number of virtual log files grows much more slowly than the size. If a log file grows in small increments, it tends to have many small virtual log files. If the log file grows in larger increments, SQL Server creates a smaller number of larger virtual log files. For example, if the transaction log is growing by 1-MB increments, the virtual log files are smaller and more numerous compared to a transaction log growing at 50-MB increments. A large number of virtual log files can increase the time taken to perform database recovery.
As records are written to the log, the end of the log grows from one virtual log file to the next. If there is more than one physical log file for a database, the end of the log grows through each virtual log file in each physical file before circling back to the first virtual log file in the first physical file. Only when all log files are full will the log begin to grow automatically.
"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply