- Less than 64MB = 4 VLFs
- Greater than 64MB and less than 1GB = 8 VLFs
- Greater than or equal to 1GB = 16 VLFs
So for example if I initially create a 1GB transaction log file it will be divided into 16 64MB VLFs, then if it grew a few times by 10 Mb before being switched to 10% the following trend would be experienced and result in a quickly increasing number of VLFs when then compared to manually growing the file:
Action | Space Added | VLFs Created | Total Size | Total VLFs |
Initial Size | 1024 Mb | 16 | 1024 Mb | 16 |
Auto Grow | 10 Mb | 4 | 1034 Mb | 20 |
Auto Grow | 10 Mb | 4 | 1044 Mb | 24 |
Auto Grow | 10 Mb | 4 | 1054 Mb | 28 |
Auto Grow | 10 Mb | 4 | 1064 Mb | 32 |
Auto Grow | 106 Mb | 8 | 1170 Mb | 40 |
Auto Grow | 117 Mb | 8 | 1287 Mb | 48 |
Auto Grow | 128 Mb | 8 | 1415 Mb | 56 |
Auto Grow | 141 Mb | 8 | 1556 Mb | 64 |
Auto Grow | 155 Mb | 8 | 1711 Mb | 72 |
Auto Grow | 171 Mb | 8 | 1882 Mb | 80 |
Auto Grow | 188 Mb | 8 | 2070 Mb | 88 |
The only way to reduce the number of VLFs is to then Shrink the file and manually grow the file by a set size such as:
Action | Space Added | VLFs Created | Total Size | Total VLFs |
Initial Size | 1024 Mb | 16 | 1024 Mb | 16 |
Manual Grow | 1024 Mb | 16 | 2048 Mb | 32 |
Auto Grow | 204 Mb | 8 | 2252 Mb | 40 |
Ideally you should keep the number of VLFs to as small as possible. In SQL 2012 there is now a warning raised when a database has >10,000 VLFs, although there is no warning available in earlier versions of SQL Server. You can use the following TSQL though to report on the number of VLFs per database within your SQL Instance. Then if you compare this with your auto-grow settings for the database you can determine the reason for why the count is the way it is.
SET NOCOUNT ON;
/* declare variables required */DECLARE @DatabaseId INT;
DECLARE @TSQL varchar(MAX);
DECLARE cur_DBs CURSOR FOR
SELECT database_id FROM sys.databases;
OPEN cur_DBs;
FETCH NEXT FROM cur_DBs INTO @DatabaseId
--These table variables will be used to store the dataDECLARE @tblAllDBs Table (DBName sysname
, FileId INT
, FileSize BIGINT
, StartOffset BIGINT
, FSeqNo INT
, Status TinyInt
, Parity INT
, CreateLSN NUMERIC(25,0)
)IF '11' = substring(convert(char(12),serverproperty('productversion')), 1, 2)
BEGIN DECLARE @tblVLFs2012 Table (RecoveryUnitId BIGINT
, FileId INT
, FileSize BIGINT
, StartOffset BIGINT
, FSeqNo INT
, Status TinyInt
, Parity INT
, CreateLSN NUMERIC(25,0)
);
END
ELSE
BEGIN DECLARE @tblVLFs Table (
FileId INT
, FileSize BIGINT
, StartOffset BIGINT
, FSeqNo INT
, Status TinyInt
, Parity INT
, CreateLSN NUMERIC(25,0)
);
END
--loop through each database and get the infoWHILE @@FETCH_STATUS = 0
BEGIN PRINT 'DB: ' + CONVERT(varchar(200), DB_NAME(@DatabaseId));
SET @TSQL = 'dbcc loginfo('+CONVERT(varchar(12), @DatabaseId)+');';
IF '11' = substring(convert(char(12),serverproperty('productversion')), 1, 2)
BEGIN
DELETE FROM @tblVLFs2012;
INSERT INTO @tblVLFs2012
EXEC(@TSQL);
INSERT INTO @tblAllDBs
SELECT DB_NAME(@DatabaseId)
, FileId
, FileSize
, StartOffset
, FSeqNo
, Status
, Parity
, CreateLSN
FROM @tblVLFs2012;
END
ELSE
BEGIN
DELETE FROM @tblVLFs;
INSERT INTO @tblVLFs
EXEC(@TSQL);
INSERT INTO @tblAllDBs
SELECT DB_NAME(@DatabaseId)
, FileId
, FileSize
, StartOffset
, FSeqNo
, Status
, Parity
, CreateLSN
FROM @tblVLFs;
END
FETCH NEXT FROM cur_DBs INTO @DatabaseId
ENDCLOSE cur_DBs;
DEALLOCATE cur_DBs;
--just for formating if output to TextPRINT '';
PRINT '';
PRINT '';
--Return the data based on what we have foundSELECT a.DBName
, COUNT(a.FileId) AS [TotalVLFs]
, MAX(b.[ActiveVLFs]) AS [ActiveVLFs]
, (SUM(a.FileSize) / COUNT(a.FileId) / 1024) AS [AvgFileSizeKb]
FROM @tblAllDBs a
INNER JOIN (
SELECT DBName
, COUNT(FileId) [ActiveVLFs]
FROM @tblAllDBs
WHERE Status = 2
GROUP BY DBName
) b
ON b.DBName = a.DBName
GROUP BY a.DBName
ORDER BY TotalVLFs DESC;
SET NOCOUNT OFF;
Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.
No comments:
Post a Comment