Can we set the VLFs(Virtual Log file) on our own wish

• forsqlserver

  SSCoach

  Points: 18902

  More actions

  May 3, 2011 at 12:40 am

  #389422

  :-)Can we set the VLFs(Virtual Log file) on our own wish...or May We know how much VLFs is currently using by our log file.

  If yes:Will this number be same for all databases log file?

  Thanks

• DBA328

  Hall of Fame

  Points: 3952

  More actions

  May 3, 2011 at 1:10 am

  #1318903

  The Database Engine chooses the size of the virtual log file dynamically when log files are created or extended, so you should not able to manully modify virtual log file size.

  http://msdn.microsoft.com/en-us/library/ms178037.aspx

  http://msdn.microsoft.com/en-us/library/ms179355.aspx

  However, you could view virutal log files status with command DBCC LOGINFO. This will give you information about your virtual logs inside your transaction log. The primary thing to look at here is the Status column. Since this file is written sequentially and then looped back to the beginning, you want to take a look at where the value of "2" is in the output. This will tell you what portions of the log are in use and which are not in use Status = 0. Another thing to keep an eye on is the FSeqNo column. This is the virtual log sequence number and the latest is the last log. If you keep running this command as you are issuing transactions you will see these numbers keep changing.