• 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 1000Find 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