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
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() ;
- SQL 2000 – YES! LOTS OF VLF’S ARE BAD! IMPROVE THE PERFORMANCE OF YOUR TRIGGERS AND LOG BACKUPS ON 2000
- Linchi Shea – Performance impact: a large number of virtual log files – Part I
- Kimberly L. Tripp – Transaction Log VLFs – too many or too few?
- Real World DBA – Virtual Log Files – VLF – And SQL Server Performance