July 22, 2003 at 2:11 am
Is there a way to find out virtual log size of log file.
July 22, 2003 at 10:17 am
What do you mean by virtual log?
Shas3
July 22, 2003 at 11:56 pm
Hi,
EXTRACT FROM BOL for your quick reference;
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.
July 23, 2003 at 1:01 pm
Very interesting and basic piece of information, I never really focused on this. Thanks for the info. With little research on this I found that with the undocumented DBCC LOGINFO(‘DBNAME’) you can find out how many virtual log files, their sizes and creation time. Using this you can also find out where the active part of your Tlog is (Status=2 is active). This also gives very good explanation why your Transaction log shrinking doesn’t work some times. You cannot shrink your log file If your Active part of the log is at the end of the Tlog. Btw in 2000 they changed the creation time to Create LSN.
Shas3
July 24, 2003 at 12:26 am
PERFECT !!!!
It was for this particular reason I was looking for the info.
Thanks once again
Deepak
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply