The only way to find out what process caused the autogrowth is to use Extended events esp. EVENT --> sqlserver.database_file_size_change & sqlserver.databases_log_file_size_changed and ACTION --> sqlserver.sql_text.
Looks like @DBA_ANDY already did the hard work of writing an XEvent
-- Original Author : @DBA_ANDY http://nebraskasql.blogspot.com/2016/06/finding-file-growths-with-extended.html
-- Modified by : Kin to filter on tempdb autogrowth events
SET NOCOUNT ON
/* Create Extended Events Session */
IF EXISTS (SELECT 1 FROM master.sys.server_event_sessions WHERE name = 'DemoFileSize')
DROP EVENT SESSION [DemoFileSize] ON SERVER
GO
CREATE EVENT SESSION [DemoFileSize] ON SERVER
ADD EVENT sqlserver.database_file_size_change(SET collect_database_name=(1)
ACTION(package0.collect_system_time,sqlos.task_time,
sqlserver.client_app_name,sqlserver.client_hostname,
sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,
sqlserver.server_instance_name,sqlserver.session_id,
sqlserver.sql_text,sqlserver.username)
WHERE ( [database_id] = ( 2 ) -- This is tempdb database id
),
/* Note - predicate/filter - will collect only for tempdb */
ADD EVENT sqlserver.databases_log_file_size_changed(
ACTION(package0.collect_system_time,sqlos.task_time,
sqlserver.client_app_name,sqlserver.client_hostname,
sqlserver.client_pid,sqlserver.database_id,sqlserver.database_name,
sqlserver.server_instance_name,sqlserver.session_id,
sqlserver.sql_text,sqlserver.username)
WHERE ( [database_id] = ( 2 ) -- This is tempdb database id
)
/* Note - predicate/filter - will collect only for tempdb */
ADD TARGET package0.event_file(SET filename=N'D:\XEvent_logs\DemoFileSize.xel',-- change HERE !!
max_file_size=(500),max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
ALTER EVENT SESSION [DemoFileSize] ON SERVER
STATE = START;
GO
shred the XEvent xml ...
SELECT
Case when file_type = 'Data file' Then 'Data File Grow' Else File_Type End AS [Event Name]
, database_name AS DatabaseName
, dateadd(minute, datediff(minute, sysutcdatetime(), sysdatetime()), timestamp1) as LocalTimeStamp
/* added the timestamp and in XE is UTC - this code converts it to local server time zone */
, file_names
, size_change_mb
, duration
, client_app_name AS Client_Application
, client_hostname
, session_id AS SessionID
, sql_txt ----------------> This is the process TSQL text !
, sql_username
, Is_Automatic
FROM
(
SELECT
(n.value ('(data[@name="size_change_kb"]/value)[1]', 'int')/1024.0) AS size_change_mb
, n.value('(@timestamp)[1]', 'datetime2') as timestamp1
, n.value ('(data[@name="database_name"]/value)[1]', 'nvarchar(50)') AS database_name
, n.value ('(data[@name="duration"]/value)[1]', 'int') AS duration
, n.value ('(data[@name="file_type"]/text)[1]','nvarchar(50)') AS file_type
, n.value ('(action[@name="client_app_name"]/value)[1]','nvarchar(50)') AS client_app_name
, n.value ('(action[@name="session_id"]/value)[1]','nvarchar(50)') AS session_id
, n.value ('(action[@name="client_hostname"]/value)[1]','nvarchar(50)') AS Client_HostName
, n.value ('(data[@name="file_name"]/value)[1]','nvarchar(50)') AS file_names
, n.value ('(data[@name="is_automatic"]/value)[1]','nvarchar(50)') AS Is_Automatic
, n.value ('(action[@name="sql_text"]/value)[1]','nvarchar(500)') AS sql_txt
, n.value ('(action[@name="username"]/value)[1]','nvarchar(50)') AS sql_username
FROM
(
SELECT CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(
N'D:\XEvent_logs\DemoFileSize*.xel' -- CHANGE HERE !!
, NULL
, NULL
, NULL
)
) AS Event_Data_Table
CROSS APPLY event_data.nodes('event') AS q(n)) xyz
ORDER BY timestamp1 desc
Below is the output:
Important things :
I just tested this in SQL 2016 (13.0.4001.0). Restore retain the auto-growth setting of the database (for which backup was taken). Here is the code I tested with.
Create database
CREATE DATABASE [AutoGrowTest]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'AutoGrowTest', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
LOG ON
( NAME = N'AutoGrowTest_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest_log.ldf' , SIZE = 8192KB , FILEGROWTH = 65536KB )
GO
Change auto-growth
USE [master]
GO
ALTER DATABASE [AutoGrowTest] MODIFY FILE ( NAME = N'AutoGrowTest', SIZE = 262144KB , FILEGROWTH = 131072KB )
GO
Backup database
BACKUP DATABASE [AutoGrowTest] TO DISK = N'C:\AutoGrowthTestFull.bak' WITH NOFORMAT, NOINIT, NAME = N'AutoGrowTest-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Restore with diff name
USE [master]
RESTORE DATABASE [AutoGrowTest_restore] FROM DISK = N'C:\AutoGrowthTestFull.bak'
WITH FILE = 1, move N'AutoGrowTest' TO
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest_restore.mdf'
, move N'AutoGrowTest_log' TO
N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AutoGrowTest_restore_log.ldf'
, nounload, stats = 5
GO
It retained auto-growth setting of original database.
Please note auto-growth setting changed in SQL 2016 than what is mentioned in some comments and answers.
Best Answer
Basics of Transaction Log files
Transaction Log files (TLog) are like a notepad with pages that hold all the information on anything that has changed during the day. The TLog (notepad) has a beginning and an end when it is opened. The TLog (notepad) will also know where it was before based on the LSN (log sequence number) (notepad: Post-IT Index). If deletes/inserts/updates are finished they are marked as COMMITed and (can be) removed from the TLog (notepad) when a backup occurs which results in a CHECKPOINT being set in the TLog file. In the notepad analogy the old notes get removed from where they were and because your notepad is always the same size, the removed pages get replaced with fresh white empty pages; be it at the beginning or at the end for your notepad. Why? Because your notepad only has space for 100 pages. The removed notepad pages are then stored in your central storage (binder).
Following is a simplistic view of the TLog file.
TLog after database has come online
Transaction Logs are Circular
Equally important: The TLog is circular in nature (round robin), so if modifications can't be stored at the end of the TLog and there is free space at the beginning, the transaction will be stored there.
As you can see in above example the transactions were written to the TLog and circled around in order to log further changes.
TLog Full, Now What?
Now if the TLog is full it has the following options:
Let's follow on with the simplistic model of my TLog file and the situation where the TLog file has to grow. Two transactions modify data.
Before
LSN 28 is logged for the first transaction
TLog has to grow
LSN 29 is logged for the second transaction
How Does This Relate to Your Questions?
You should have the basics now, so let's have a look at how this all relates to your questions.
If your application is modifying data or if the maintenance task is modifying data all modifications have to be stored in the TLog file. If the TLog has to grow because there is insufficient free space in the file and if the growth settings permit, then the TLog file will grow.
Yes, But You Changed Things
percent (%) or MB growth?
Changing the growth settings from % to MB is a good thing to do, but it doesn't eliminate the need for the database to store transactions somewhere, if the space in the TLog is all used up.
Additional reading:
Removing Maintenance Plans
This can reduce the need for the TLog file to grow because INDEX REORGANIZE and INDEX REBUILD are no longer performed. But, ...
Adding Ola Solution
... adding the Index Optimisation Jobs from Ola will perform Index maintenance too. So you've just replaced Index Maintenance with Index Maintenance. No real gains.
Ok, So What Do You Have to Do Now?
Adequate TLog file sizing
Don't try and shrink the TLog file too much. It will grow again. Instead try sizing the TLog file according to your observations, allow for a little growth and monitor how full the TLog file is.
Frequent TLog Backups
If you have a look at my simplistic model, you may have noted that the TLog didn't have a lot of CHECKPOINTS logged. If the database has to handle a lot of transactions modifying data, then shortening the interval between TLog backups can help keep the TLog from growing, because a TLog backup creates a CHECKPOINT which allows the data to be written to the database. Let's add an example using my over simplistic model.
Additonal Backup
A backup is performed and logged in the TLog and during the backup a user modifies data:
We now have everything in the database (up to LSN 30) and the SQL Server could free up the TLog to look like this:
Great, the TLog can now continue logging modifications at the beginning again.
Optimal Virtual Log File Sizing (advanced topic)
Having an adequate VLF size (not too big, not too small) can help keep TLogs at bay.
Additional reading:
VLF are like subsets of pages in my simplistic model or you could look at them as groupings of pages. Having to many pages in the group (VLF) can be good or bad depending on a couple of factors. Please read up on them in the linked articles. Basically it boils down to:
Additional Resources