Microsoft – SQL Server – Virtual Log File (VLF)

What are Virtual Log Files?

Querying for Virtual Log Files offers an insight into the journal of Log file fragments creation and usage.

As log file fragments are created the system keeps a record of their creation. Also, as the system uses the fragment the status column is updated, with a non-zero value .

Get Virtual Log File

   dbcc loginfo()
   go

Any Issues?

If a SQL Server Instance is not well managed, numerous Virtual Log Files can be created. Over creation results in a bit of fragmentation.

How to determine Fragmentation?

There are no hard rules to determine fragmentation. But, a system with over 300 or more VLF entries is probably fragmented.

What are the effects of Fragmentation?

      • When SQL Server Instance is starting, the database recovery takes longer as the system reviews each VLF and its contents. Each content is examined and a determination is made about rollback, etc.

     

        • Data Manipulate Language (DML) statements such as insert, delete, and update can also take a bit longer as the system transverses the log searching for clean buffers.

      How To fix:

      1] Backup Transaction Log

      2] Identify Transaction Log

              select
      		  db_name(dbid) as databaseName
      		, fileid
      		, name as fileSymbolicName
      		, groupid
      		, cast((size * 8 / 1024) as sysname) 
                           + ' MB' as sizeInMB
              from   master.sys.sysaltfiles
              where  groupid = 0
              --replace <database-name> with actual db-name
              and    dbid = db_id(<database-name>)
              ;

      3] Shrink Transaction Log files

              use ;
      
              -- shrink transaction log file, passing in file-id from
              -- sys.sysaltfiles
              dbcc shrinkfile();

      4] If Transaction Log does not shrink

          Query sys.databases and review the log_reuse_wait_desc column for reason why the transaction log might currently be in-use.

      Changes that will help forestall future Fragmentation?

          Setting appropriate levels for database log file growth
          Set reasonable growth size for the model database transaction log. This will help with new database created on that instance
          Have database job or monitoring script that identifies sub-optimally configured databases.
          Regular Scheduled Transaction Backup
          Review Application Code and ETL jobs to ensure granularity in projected Number of Records Affected
          Review SQL Server Profiler Traces and identify queries that generate high IO
      /*
         Find The Number of VLFs For All Databases
         By Ken Simmons
      http://sqlserverpedia.com/blog/sql-server-bloggers/find-the-number-of-vlfs-for-all-databases/
      */
      if object_id('tempdb..#LogInfo') is not null
      begin
      	drop table #LogInfo
      end	
      
      if object_id('tempdb..#LogInfo2') is not null
      begin
      	drop table #LogInfo2
      end
      
      CREATE TABLE #LogInfo(
                      FileID BIGINT,
                      FileSize BIGINT,
                      StartOffset BIGINT,
                      FSeqNo BIGINT,
                      Status BIGINT,
                      Parity BIGINT,
                      CreateLSN VARCHAR(50))
      CREATE TABLE #LogInfo2(
                      DatabaseName SYSNAME,
                      FileID BIGINT,
                      FileSize BIGINT,
                      StartOffset BIGINT,
                      FSeqNo BIGINT,
                      Status BIGINT,
                      Parity BIGINT,
                      CreateLSN VARCHAR(50))
      EXEC master.dbo.sp_MSFOREACHDB
          'USE [?] INSERT INTO #LogInfo EXECUTE (''DBCC LOGINFO'');
                 INSERT INTO #LogInfo2 SELECT ''?'', * FROM #LogInfo;
                 DELETE FROM #LogInfo'
      
      SELECT DatabaseName,
             COUNT(*) AS VLFs
      from #LogInfo2
      group by DatabaseName
      order by count(*) desc  
      
      drop table #LogInfo
      drop table #LogInfo2

      Get Database file Sizes and Growth Pattern\Size

      /*
      Datafile Growth in SQL Server - Getting the Statistics Part I
      http://sqlserver.livejournal.com/73512.html
      */
      select
      	  db_name(dbid) as databaseName
      	, fileid
      	, name as fileSymbolicName
      	, groupid
      	, cast((size * 8 / 1024) as sysname) + ' MB' as sizeInMB
      	, growth = (
         		      case
      			(status & 0x100000) when 0x100000
      				then cast(growth as sysname) + '%'
      				  else cast((growth * 8 / 1024) as
                                 sysname) + ' MB'
      			end
      		)
      from   master.sys.sysaltfiles
      where  dbid = db_id()
      ;

      References:

      1. SQL 2000 – YES! LOTS OF VLF’S ARE BAD! IMPROVE THE PERFORMANCE OF YOUR TRIGGERS AND LOG BACKUPS ON 2000
        http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/07/25/sql-2000-yes-lots-of-vlf-s-are-bad-improve-the-performance-of-your-triggers-and-log-backups-on-2000.aspx
      2. Linchi Shea – Performance impact: a large number of virtual log files – Part I
        http://sqlblog.com/blogs/linchi_shea/archive/2009/02/09/performance-impact-a-large-number-of-virtual-log-files-part-i.aspx
      3. Kimberly L. Tripp – Transaction Log VLFs – too many or too few?
        http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx
      4. Real World DBA – Virtual Log Files – VLF – And SQL Server Performance
         http://www.realworlddba.com/post/2011/06/07/Virtual-Log-Files-VLF-And-SQL-Server-Performance.aspx

      Leave a Reply

      Please log in using one of these methods to post your comment:

      WordPress.com Logo

      You are commenting using your WordPress.com account. Log Out / Change )

      Twitter picture

      You are commenting using your Twitter account. Log Out / Change )

      Facebook photo

      You are commenting using your Facebook account. Log Out / Change )

      Google+ photo

      You are commenting using your Google+ account. Log Out / Change )

      Connecting to %s