Get details of SQL Server Database Growth and Shrink Events

It is essential for the DBA to need to ensure the SQL Server database performance. Performance tuning is an open-ended task, and you need to ensure the monitoring of various database parameters.

Overview of Default trace in SQL Server

Starting from SQL Server 2005, SQL Server captures a few critical events in the default traces. The default trace is enabled on each SQL Server instance. It is a lightweight trace consisting of five trace files in the installation directory of SQL Server.

We can check the default trace configuration using the following query.

1	SELECT* FROM sys.configurations WHERE configuration_id = 1568

If the default trace is not running, we can use sp_configure commands to enable it using the following query.

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure 'default trace enabled', 1;

RECONFIGURE;

In the default trace, SQL Server calculates following auto grow and shrink events for the SQL Server database.

Data file auto grow
Data file auto shrink
Log file auto grow
Log file auto shrink

Once we create a SQL Server database, we define auto growth for each data and log file.

SQL Server expands the size of a database (data and log file) based on the auto growth setting of an individual file to avoid space issues in the existing transactions. Once an auto-growth event occurs, SQL Server holds up transaction processing for the time being. We should monitor and manage the auto growth setting for a database. If the auto-growth event takes a little longer time, it might influence database performance and availability.

In case of any auto-growth event, SQL Server looks for additional space in the disk where the data or log file exists. If we have a very frequent auto-growth event, it causes physical fragmentation as well. The physical fragmented database takes a long time to complete the transactions. We should take action to minimize the auto-growth events for highly transactional databases.

By default, Auto Growth is set to grow 10% percent of existing file size in the SQL Server database. It might be suitable for small databases, however, if you look at the perspective of a large database file, it is not the right configuration.

Let’s look at the following examples. In example 1, SQL Server needs to expand the data file by 100 GB while in example 2, SQL Server expands data file by 10 GB. It will take a long time to expand the data file by 100 GB, and it might put additional load on the database as well.

Example 1	Example 2
Database File Size – 1 TB	Database File Size – 100 GB
Auto Growth – By 10%	Auto Growth – By 10%
Auto Growth File size increment: 100 GB	Auto Growth File size increment: 10 GB

We should take the following approach to avoid auto-growth events.

Proactively grow data and log file size for the SQL Server database. If database files have sufficient free space, it will not cause any auto growth
Monitor auto growth events and set appropriate size for the auto growth in fixed MB instead of percentage (%) growth

Suppose we set the data file auto growth to 512 MB instead of a 10% setting. In the following table, we can see that auto growth does not require any dependency on the data file size if we use fixed MB growth.

Example 1	Example 2
Database File Size – 1 TB	Database File Size – 100 GB
Auto Growth – By 512 MB	Auto Growth – By 512 MB
Auto Growth File size increment: 512 MB	Auto Growth File size increment: 512 MB

SQL Server database Shrink

We might come across a situation where the database files have sufficient free space, but the disk does not meet the free space threshold (assume we maintain a 20% free disk space threshold). Many times, DBA executes DBCC SHRINKDATABASE command to shrink database and return free space to the disk. We should not shrink a database unless it is very much required to do so.

Some of the issues you might face due to shrinking databases are as follows:

Shrink operation is slow and takes a long time to complete, especially for large database files
It might cause blocking during its execution time
It brings index fragmentation, and you need to do index maintenance tasks
Due to index fragmentation, database performance will be slow

Different ways to check Auto Growth and Shrink events for SQL Server database

At this point, you should be aware that it is vital for a DBA to monitor the auto growth and database shrink events. It is especially vital for the high transactional database. In this section, we will identify different ways to get details of these events.

SSMS Disk Usage Report

Connect to a SQL instance and right-click on a database for which we want to get details of Auto Growth and Shrink Events.

Go to Reports -> Standard Reports and Disk Usage

It opens the disk usage report of the specified database. In this disk usage report, we get the details of the data file and log file space usage.

In the following screenshot, we get a message – No entry found for autogrow/auto shrink event for SQLShackDemo database in the trace log.

It shows no auto growth and auto shrink event occurred in the trace log for our SQL Server databases. You should note that as per the message it indicates that only auto events are captured, but default trace captures events that occurred due to executing shrink command.

Let’s shrink the transaction log file of SQLShackDemo database using the following DBCC SHRINKFILE command.

USE [SQLShackDemo]

DBCC SHRINKFILE (N'SQLShackDemo_log' , 0, TRUNCATEONLY)

Rerun the disk usage report, and it shows the event for this. You may notice that it shows the event name Log File Auto Growth.

Let’s execute a workload on SQLShackDemo database, and it should cause data and log file growth.

CREATE TABLE Temp

(ID INT,

Name CHAR(8000)

);

SET NOCOUNT ON;

DECLARE @i INT;

SET @i = 1;

WHILE @i < 10000

BEGIN

INSERT INTO Temp

VALUES

(1,

'Rajendra'

);

SET @i = @i + 1;

END;

Once query execution is completed, rerun the disk usage report. In the following screenshot, you can see data and log file auto-growth events.

You need to notice and monitor the frequency of auto-growth along with the duration (ms) for the SQL Server database. It gives you the necessary data to correlated performance issues with auto events and time is taken in completing the request. If the duration is more even for a small increment, you should also consult your storage team for disk-related performance issues.

Scripts to check auto growth events in SQL Server database:

Let’s first execute the script and get the details.

DECLARE @current_tracefilename VARCHAR(500);

DECLARE @0_tracefilename VARCHAR(500);

DECLARE @indx INT;

SELECT @current_tracefilename = path

FROM sys.traces

WHERE is_default = 1;

SET @current_tracefilename = REVERSE(@current_tracefilename);

SELECT @indx = PATINDEX('%\%', @current_tracefilename);

SET @current_tracefilename = REVERSE(@current_tracefilename);

SET @0_tracefilename = LEFT(@current_tracefilename, LEN(@current_tracefilename) - @indx) + '\log.trc';

SELECT DatabaseName,

te.name,

Filename,

CONVERT(DECIMAL(10, 3), Duration / 1000000e0) AS TimeTakenSeconds,

StartTime,

EndTime,

(IntegerData * 8.0 / 1024) AS 'ChangeInSize MB',

ApplicationName,

HostName,

LoginName

FROM ::fn_trace_gettable(@0_tracefilename, DEFAULT) t

INNER JOIN sys.trace_events AS te ON t.EventClass = te.trace_event_id

WHERE(trace_event_id >= 92

AND trace_event_id <= 95)

ORDER BY t.StartTime;

We get the following output, and it shows useful information for all file grow events. We also get the application name, hostname and login name that caused this file size growth.

Let’s understand the query quickly.

We use the sys.trace_events catalog view to get SQL Server events and filter those events for our SQL Server database. In the following query, we can see the trace event id 92 to 95 with their description. The event id and description do not change with the new versions of SQL Server

1
2
3
4

SELECT *
FROM sys.trace_events
WHERE trace_event_id >= 92
AND trace_event_id <= 95;
We use sys.traces catalog view to get details of current running traces on the system. If the is_default property value for any running trace is 1, it shows for the default trace. We also get the trace file location using this catalog view
We use the fn_trace_gettable table-valued function to read the content of a trace file and return it in a tabular format. In this query, it reads the default trace file and gives us the required information

Find-DbaDbGrowthEvent DBATools Command

We can also use DBATools PowerShell command to get details of auto-growth events in the SQL Server database. It reads the default trace and provides us with the information for the auto- growth the event.

The DBATools command to check auto growth events is Find-DbaDbGrowthEvent.

Let’s check the syntax, synopsis and description of Find-DbaDbGrowthEvent command.

1	> get-help Find-DbaDbGrowthEvent

Find-DbaDbGrowthEvent command checks for the AutoGrow events in the Default Trace for all databases. It includes the following events.

92 – Data File Auto Grow
93 – Log File Auto Grow
94 – Data File Auto Shrink
95 – Log File Auto Shrink

Let’s execute the command for my SQL instance and get the output in a grid format.

1	> Find-DbaDbGrowthEvent -SqlInstance Kashish\SQL2019CTP \| Out-GridView

It opens a new interactive output window, and we can see all auto growth events in this.

Conclusion

In this article, we explored the overview of auto growth and shrink activity in the SQL Server database. We also learned a different way to extract the growth events details from the default trace of SQL Server. If you have any comments or questions, feel free to leave them in the comments below.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023