As a mid-level DBA, you know that SQL Server relies on the log file to record transactions and modifications in the database, ensuring integrity until data is written to the data file. Internally, one physical log file is comprised of many virtual log files. If you’ve ever been curious about how the log file works, this session will help you understand that. I’ll cover how transactions are written to the log, how virtual log files are used, and the optimal settings for databases of various sizes.
If you liked this video, check out our free SQL Server videos and our free upcoming webcasts.
Rizwan Hassan September 13, 2012 | 11:09 am
Hi Jess,
when will you make the PPT available for download?
Thanks!
Jes Schultz Borland September 14, 2012 | 8:25 am
A link to the slides has been included, so you can review the slides and the links I included.
Pingback: Something for the Weekend - SQL Server Links 14/09/12
Ayman El-Ghazali September 24, 2012 | 7:13 am
I was lucky enough to attend this session while at work with 0 interruptions. Good stuff!
Mark April 29, 2013 | 10:46 am
Hi Jes,
Great video thanks! I have a db (data file 600GB, t-log 200GB) whose t-log has 1700 VLFs. That’s about 117MB per VLF(actually the larger ones are 128MB).
Seeing the ‘recommended’ # of VLFs around (~50), it seems like we have many VLFs but the size of our VLFs doesn’t seem too bad.
Our growth rate is set to 5GB (so based on 16VLFs created, it would create VLFs with size of ~312MB).
I’m trying to determine if we are ‘OK’ or if i should go further and shrink the t-log and expand it to toal 200GB in increments, but what would be the driving force (VLF size or # of VLFs)?
Thanks in advance!
Jes Schultz Borland May 1, 2013 | 3:34 pm
The focus should be on the number of VLFs more than the size of them. I’d consider 1,700+ VLFs too many, even for a log file that large. If I were you, I’d pick a time when there would be very little activity in the database and try to shrink and regrow it!