Rajendra Gupta
Disk usage report in SSMS

Get details of SQL Server Database Growth and Shrink Events

June 25, 2019 by

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.

Default trace configuration for SQL Server database

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

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.

Database Properties

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

Standard reports for SQL Server Database

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.

Disk usage report in SSMS

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

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.

Disk usage report in SSMS

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

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.

Data and log file events

Scripts to check auto growth events in SQL Server database:

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

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.

SQL query output to get auto growth events details for the SQL Server Database

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

    sys.trace_events for auto growth

  • 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.

help page of Find-DbaDbGrowthEvent DBATools command

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.

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

Find-DbaDbGrowthEvent DBATools command

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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
7,538 Views