Skip to content

SQL Server sizing Transaction log

    • What is a transaction log file –file to record all changes to the database
    • Why backup transaction log file — to clear the transaction log and control size
    • Inside the transaction log file — Use DBCC LogInfo
    • Out of control transaction log file — primary reason, full recovery mode and no transactional backups

    What is the auto growth feature?

         An auto-growth event is a part of SQL Server that expands the size of a database file when it runs out of space.  If there is a transaction (such as many inserts) that requires more log space than is available, the transaction log file of that database will need to adjust to the new space needed by increasing the log file size. 

    What happens when auto growth is expanding?

    This can cause a performance issue, as this is a blocking operation.  The transaction that that initiated the log growth will be held until more space is allocated to the log file, determined by the auto growth setting

    Physical fragmented on the disk occurs as the pages required are not necessarily next to each other.  The more auto-growth events you have the more physical fragmentation you will have the files

    Avoid auto growth by pro actively configuring the auto growth

    Pre-size the data and log files

    Manually manage the growth of data and log files

    Auto growth should be used for safety reasons only

    Don’t rely on auto growth

    Maintain a level of at least 25 percent available space across disks to allow for growth and peak usage patterns

    Set the auto-grow settings to grow based on megabytes instead of a percentage, so as to have the auto growth consistent

    --Drop database auto
    --Drop database auto2
    
    --Create database with default setting based on the Model database configuration
    Use master
    go
    
    CREATE DATABASE [auto]
     CONTAINMENT = NONE
     ON  PRIMARY 
    ( NAME = N'auto', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\auto.mdf' , 
    SIZE = 3072KB ,      --<< initial size of data file 3mb      
    FILEGROWTH = 1024KB )  --<< growth by 1mg
    
     LOG ON 
    ( NAME = N'auto_log', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\auto_log.ldf' , 
    SIZE = 1024KB ,    --<< initial size of log file 1mb 
    FILEGROWTH = 10%)  --<< growth by 10%
    GO
    
    DBCC LogInfo;
    
    --Create database with set LOG FILE setting
    CREATE DATABASE [auto2]
     CONTAINMENT = NONE
     ON  PRIMARY 
    ( NAME = N'auto2', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\auto2.mdf' , 
    SIZE = 1024000KB ,       --<< initial size of data file 1000mb 
    FILEGROWTH = 102400KB )  
    
     LOG ON 
    ( NAME = N'auto2_log', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\auto2_log.ldf' , 
    SIZE = 102400KB , 
    FILEGROWTH = 102400KB ) --<< growth by 100mb (PRE SIZED SO THAT THE AUTO GROWTH DOES NOT ACTIVATE)
    GO
    
    DBCC LogInfo
    
    -- examine the database files
    sp_helpdb auto
    sp_helpdb auto2
    dbcc sqlperf (logspace)
    
    --move data from adventureworks2012 to auto and auto2 dtabase via import/export wizard OR 
    Select * Into LogGrowthTable from adventureworks2012.sales.SalesOrderDetai3l
    ------------------------------------------------------------------------
    --RESULTS:
    --As the insert is being recored in the transaction log that was 1mb in size, the initial size (1mb) of the tlog recording can't keep up with the
    --activity, and as such, needs to expand by 10% each time there is modifications to record.
    -- query to find auto growth setting for all or specified database (or use the SQL reports)
    
    USE [master]
    GO
     
    BEGIN TRY
        IF (SELECT CONVERT(INT,value_in_use) FROM sys.configurations WHERE NAME = 'default trace enabled') = 1
        BEGIN
            DECLARE @curr_tracefilename VARCHAR(500);
            DECLARE @base_tracefilename VARCHAR(500);
            DECLARE @indx INT;
     
            SELECT @curr_tracefilename = path FROM sys.traces WHERE is_default = 1;
            SET @curr_tracefilename = REVERSE(@curr_tracefilename);
            SELECT @indx  = PATINDEX('%\%', @curr_tracefilename) ;
            SET @curr_tracefilename = REVERSE(@curr_tracefilename) ;
            SET @base_tracefilename = LEFT( @curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc'; 
            SELECT
                --(DENSE_RANK() OVER (ORDER BY StartTime DESC))%2 AS l1,
                ServerName AS [SQL_Instance],
                --CONVERT(INT, EventClass) AS EventClass,
                DatabaseName AS [Database_Name],
                Filename AS [Logical_File_Name],
                (Duration/1000) AS [Duration_MS],
                CONVERT(VARCHAR(50),StartTime, 100) AS [Start_Time],
                --EndTime,
                CAST((IntegerData*8.0/1024) AS DECIMAL(19,2)) AS [Change_In_Size_MB]
            FROM ::fn_trace_gettable(@base_tracefilename, default)
            WHERE
                EventClass >=  92
                AND EventClass <=  95
                --AND ServerName = @@SERVERNAME
                --AND DatabaseName = 'myDBName'  
    			AND DatabaseName IN ('auto','auto2')
            ORDER BY DatabaseName, StartTime DESC;  
        END    
        ELSE   
            SELECT -1 AS l1,
            0 AS EventClass,
            0 DatabaseName,
            0 AS Filename,
            0 AS Duration,
            0 AS StartTime,
            0 AS EndTime,
            0 AS ChangeInSize 
    END TRY 
    BEGIN CATCH 
        SELECT -100 AS l1,
        ERROR_NUMBER() AS EventClass,
        ERROR_SEVERITY() DatabaseName,
        ERROR_STATE() AS Filename,
        ERROR_MESSAGE() AS Duration,
        1 AS StartTime, 
        1 AS EndTime,
        1 AS ChangeInSize 
    END CATCH
    
    --DBCC LogInfo;

    AutoGrowth 1

    -- Drop temporary table if it exists
    IF OBJECT_ID('tempdb..#info') IS NOT NULL
           DROP TABLE #info;
    
    -- Create table to house database file information
    CREATE TABLE #info (
         databasename VARCHAR(128)
         ,name VARCHAR(128)
        ,fileid INT
        ,filename VARCHAR(1000)
        ,filegroup VARCHAR(128)
        ,size VARCHAR(25)
        ,maxsize VARCHAR(25)
        ,growth VARCHAR(25)
        ,usage VARCHAR(25));
        
    -- Get database file information for each database   
    SET NOCOUNT ON; 
    INSERT INTO #info
    EXEC sp_MSforeachdb 'use ? 
    select ''?'',name,  fileid, filename,
    filegroup = filegroup_name(groupid),
    ''size'' = convert(nvarchar(15), convert (bigint, size) * 8) + N'' KB'',
    ''maxsize'' = (case maxsize when -1 then N''Unlimited''
    else
    convert(nvarchar(15), convert (bigint, maxsize) * 8) + N'' KB'' end),
    ''growth'' = (case status & 0x100000 when 0x100000 then
    convert(nvarchar(15), growth) + N''%''
    else
    convert(nvarchar(15), convert (bigint, growth) * 8) + N'' KB'' end),
    ''usage'' = (case status & 0x40 when 0x40 then ''log only'' else ''data only'' end)
    from sysfiles
    ';
    
    -- Identify database files that use default auto-grow properties
    SELECT databasename AS [Database Name]
          ,name AS [Logical Name]
          ,filename AS [Physical File Name]
          ,growth AS [Auto-grow Setting] FROM #info 
    WHERE (usage = 'data only' AND growth = '1024 KB') 
       OR (usage = 'log only' AND growth = '10%')
    ORDER BY databasename
    
    -- get rid of temp table 
    DROP TABLE #info;
    
    --alter the growth 
    ALTER DATABASE MyDB
    MODIFY FILE
    (NAME=MyDB_Log,FILEGROWTH=20MB);

    AutoGrowth 2

    --the size and number of VLF added at the time of expanding the transaction log is based on this following criteria:
    --transaction log size less than 64MB and up to 64MB = 4 VLFs
    --transaction log size larger than 64MB and up to 1GB = 8 VLFs
    --transaction size log larger than 1GB = 16 VLFs
    
    --1. CREATE A DATABASE WITH LOG FILE LESS THAN 64 MB THAT WILL CREATE 4 VLFS
    
    /*the following will show that improper sizing of the transaction log file and setting and relying on the default auto growth contributes to internal log fragmentation, and causes the VLFS to increase.*/
    
    --Note that the transaction log is 1 megabyte in size, and the autogrowth is set to grow in increments of 10% (bad practice)
    
    CREATE DATABASE [Log Growth]
    ON PRIMARY
    ( NAME = N'LogGrowth', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\LogGrowth.mdf' ,
    SIZE = 4096KB ,
    FILEGROWTH = 1024KB )
    
    LOG ON
    ( NAME = N'LogGrowth_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\LogGrowth_log.ldf' ,
    SIZE = 1024KB , --1 megabyte
    FILEGROWTH = 10%)
    GO
    
    USE [Log Growth]
    GO
    --Each row indicates a VLF
    
    DBCC LOGINFO
    GO
    
    --4 VLFS
    --look at the size of the database and note transaction log is 1 MB and data file is 4MB
    
    sp_helpdb [Log Growth]
    
    --Insert data into table from another database and view the transaction log size, data file size, and the percentage of transaction log used
    
    Use [Log Growth]
    go
    
    Select * Into LogGrowthTable from adventureworks2012.sales.SalesOrderDetai3l
    
    select count(*) from LogGrowthTable
    --log space used 28.7 %
    
    DBCC sqlPerf (LogSpace)
    --look at the size of the database and note transaction log is 14 MB and data file is 15MB
    
    sp_helpdb [Log Growth]
    --Each row indicates a VLF
    
    DBCC LOGINFO
    GO
    
    --47 VLFs created as a result of improper pre sizing of the transaction log, and relying upon the auto growth property to accommodate the expansion of file
    --Drop the database
    
    EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'LogGrowth'
    GO
    USE [master]
    GO
    DROP DATABASE [Log Growth]
    GO
    

    AutoGrowth 3

    --The following demonstration will illustrate the number of VLF created depending upon the sizing of the transaction log
    --Inserting the same amount of data into the table, but this time sizing the transaction log before inserting data by managing the autogrowth size so as to avoid VLFS from being created
    
    --transaction log size larger than 64MB and up to 1GB = 8 VLFs
    
    CREATE DATABASE [Log Growth]
    ON PRIMARY
    ( NAME = N'LogGrowth', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\LogGrowth.mdf' ,
    SIZE = 1000 MB ,
    FILEGROWTH = 100 MB )
    
    LOG ON
    ( NAME = N'LogGrowth_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\LogGrowth_log.ldf' ,
    SIZE = 500 MB , --500 MEGA BYTES - PRE SIZING THE LOG FILE SO AS TO AVOID AUTO GROWTH FROM KICKING IN. AUTOGROWTH SET TO 100 GROWTH RATE AS A FAILSAFE
    FILEGROWTH = 100 MB) --file auto growth NOT set to 10%, but allocated 100 in MEGA BYTES
    GO
    
    USE [Log Growth]
    GO
    --Each row indicates a VLF
    
    DBCC LOGINFO
    GO
    
    --8 VLFS
    --look at the size of the database and note transaction log is 500 MB and data file is 1000
    
    sp_helpdb [Log Growth]
    
    --Insert data into table from another database and view the transaction log size, data file size, and the percentage of transaction log used
    
    Use [Log Growth]
    go
    
    Select * Into LogGrowthTable from adventureworks2012.sales.SalesOrderDetai3l
    
    select count(*) from LogGrowthTable
    
    --121317
    --log space used 2.504434 %
    
    DBCC sqlPerf (LogSpace)
    --Each row indicates a VLF
    
    DBCC LOGINFO
    GO
    
    --VLFs have not increased in number as a result of pre sizing the transaction log to 500 MB and therefore having the need to rely on auto growth from kicking in
    --Drop the database
    
    EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'LogGrowth'
    GO
    USE [master]
    GO
    DROP DATABASE [Log Growth]
    GO
    

    Create Database with default setting

    
    --Drop database auto
    --Drop database auto2
    
    
    --Create database with default setting based on the Model database configuration
    
    Use master
    go
    
    CREATE DATABASE [auto]
     CONTAINMENT = NONE
     ON  PRIMARY 
    ( NAME = N'auto', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\auto.mdf' , 
    SIZE = 3072KB ,      --<< initial size of data file 3mb      
    FILEGROWTH = 1024KB )  --<< growth by 1mg
    
     LOG ON 
    ( NAME = N'auto_log', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\auto_log.ldf' , 
    SIZE = 1024KB ,    --<< initial size of log file 1mb 
    FILEGROWTH = 10%)  --<< growth by 10%
    GO
    
    DBCC LogInfo;
    
    
    --Create database with set LOG FILE setting
    
    CREATE DATABASE [auto2]
     CONTAINMENT = NONE
     ON  PRIMARY 
    ( NAME = N'auto2', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\auto2.mdf' , 
    SIZE = 1024000KB ,       --<< initial size of data file 1000mb 
    FILEGROWTH = 102400KB )  
    
     LOG ON 
    ( NAME = N'auto2_log', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\auto2_log.ldf' , 
    SIZE = 102400KB , 
    FILEGROWTH = 102400KB ) --<< growth by 100mb (PRE SIZED SO THAT THE AUTO GROWTH DOES NOT ACTIVATE)
    GO
    
    DBCC LogInfo
    
    -- examine the database files
    
    sp_helpdb auto
    sp_helpdb auto2
    
    dbcc sqlperf (logspace)
    
    --move data from adventureworks2012 to auto and auto2 dtabase via import/export wizard OR 
    
    Select * Into LogGrowthTable from adventureworks2012.sales.SalesOrderDetai3l
    
    
    ------------------------------------------------------------------------
    --RESULTS:
    
    --As the insert is being recored in the transaction log that was 1mb in size, the initial size (1mb) of the tlog recording can't keep up with the
    --activity, and as such, needs to expand by 10% each time there is modifications to record.
    
    -- query to find auto growth setting for all or specified database (or use the SQL reports)
    
    USE [master]
    GO
     
    BEGIN TRY
        IF (SELECT CONVERT(INT,value_in_use) FROM sys.configurations WHERE NAME = 'default trace enabled') = 1
        BEGIN
            DECLARE @curr_tracefilename VARCHAR(500);
            DECLARE @base_tracefilename VARCHAR(500);
            DECLARE @indx INT;
     
            SELECT @curr_tracefilename = path FROM sys.traces WHERE is_default = 1;
            SET @curr_tracefilename = REVERSE(@curr_tracefilename);
            SELECT @indx  = PATINDEX('%\%', @curr_tracefilename) ;
            SET @curr_tracefilename = REVERSE(@curr_tracefilename) ;
            SET @base_tracefilename = LEFT( @curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc'; 
            SELECT
                --(DENSE_RANK() OVER (ORDER BY StartTime DESC))%2 AS l1,
                ServerName AS [SQL_Instance],
                --CONVERT(INT, EventClass) AS EventClass,
                DatabaseName AS [Database_Name],
                Filename AS [Logical_File_Name],
                (Duration/1000) AS [Duration_MS],
                CONVERT(VARCHAR(50),StartTime, 100) AS [Start_Time],
                --EndTime,
                CAST((IntegerData*8.0/1024) AS DECIMAL(19,2)) AS [Change_In_Size_MB]
            FROM ::fn_trace_gettable(@base_tracefilename, default)
            WHERE
                EventClass >=  92
                AND EventClass <=  95
                --AND ServerName = @@SERVERNAME
                --AND DatabaseName = 'myDBName'  
    			AND DatabaseName IN ('auto','auto2')
            ORDER BY DatabaseName, StartTime DESC;  
        END    
        ELSE   
            SELECT -1 AS l1,
            0 AS EventClass,
            0 DatabaseName,
            0 AS Filename,
            0 AS Duration,
            0 AS StartTime,
            0 AS EndTime,
            0 AS ChangeInSize 
    END TRY 
    BEGIN CATCH 
        SELECT -100 AS l1,
        ERROR_NUMBER() AS EventClass,
        ERROR_SEVERITY() DatabaseName,
        ERROR_STATE() AS Filename,
        ERROR_MESSAGE() AS Duration,
        1 AS StartTime, 
        1 AS EndTime,
        1 AS ChangeInSize 
    END CATCH
    
    --DBCC LogInfo;

    Create tables with auto growth

    go
    
    create table test1
    (fname varchar (50))
    
    insert into test1
    values ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
    go 1000
    
    
    
    use Auto2
    go
    
    create table test1
    (fname varchar (50))
    
    insert into test1
    values ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
    go 1000

    Find auto growth via SQL script

    --Query to display recent Data and TLog file autogrowth and autoshrink events
    --for all databases on the instance. Based on query used by the SSMS Standard
    --Report named "Disk Usage" which has a section that displays this data. Data
    --is retrieved from the default trace.
     
    --Works for SQL 2005 and above.
     
    USE [master]
    GO
     
    BEGIN TRY
        IF (SELECT CONVERT(INT,value_in_use) FROM sys.configurations WHERE NAME = 'default trace enabled') = 1
        BEGIN
            DECLARE @curr_tracefilename VARCHAR(500);
            DECLARE @base_tracefilename VARCHAR(500);
            DECLARE @indx INT;
     
            SELECT @curr_tracefilename = path FROM sys.traces WHERE is_default = 1;
            SET @curr_tracefilename = REVERSE(@curr_tracefilename);
            SELECT @indx  = PATINDEX('%\%', @curr_tracefilename) ;
            SET @curr_tracefilename = REVERSE(@curr_tracefilename) ;
            SET @base_tracefilename = LEFT( @curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc'; 
            SELECT
                --(DENSE_RANK() OVER (ORDER BY StartTime DESC))%2 AS l1,
                ServerName AS [SQL_Instance],
                --CONVERT(INT, EventClass) AS EventClass,
                DatabaseName AS [Database_Name],
                Filename AS [Logical_File_Name],
                (Duration/1000) AS [Duration_MS],
                CONVERT(VARCHAR(50),StartTime, 100) AS [Start_Time],
                --EndTime,
                CAST((IntegerData*8.0/1024) AS DECIMAL(19,2)) AS [Change_In_Size_MB]
            FROM ::fn_trace_gettable(@base_tracefilename, default)
            WHERE
                EventClass >=  92
                AND EventClass <=  95
                --AND ServerName = @@SERVERNAME
                --AND DatabaseName = 'myDBName'  
    			AND DatabaseName IN ('auto','auto2')
            ORDER BY DatabaseName, StartTime DESC;  
        END    
        ELSE   
            SELECT -1 AS l1,
            0 AS EventClass,
            0 DatabaseName,
            0 AS Filename,
            0 AS Duration,
            0 AS StartTime,
            0 AS EndTime,
            0 AS ChangeInSize 
    END TRY 
    BEGIN CATCH 
        SELECT -100 AS l1,
        ERROR_NUMBER() AS EventClass,
        ERROR_SEVERITY() DatabaseName,
        ERROR_STATE() AS Filename,
        ERROR_MESSAGE() AS Duration,
        1 AS StartTime, 
        1 AS EndTime,
        1 AS ChangeInSize 
    END CATCH