Monitoring Autogrowth Events in Databases in SQL Server

Views: 1.146
Reading Time: 3 minutes

Hello readers,
Good afternoon!

In this post I will demonstrate how to monitor the autogrowth growth of a database in SQL Server so that it is possible to identify the user who caused the growth and when the autogrowth events occurred in the database.

These results are possible thanks to the system function. :: fn_trace_gettable, which allows you to query trace events that run by default on all SQL Server instances.

Creating a test bank

Before we test the query that shows us the database's AutoGrowth events, let's create a test bank, with the default SQL Server options (initial size 5 MB and autogrowth every 1 MB) and insert some records to force the end to run. space and the bank has to grow automatically.

Your database should now be 788 MB and have autospace hundreds of times. Let's now find out when these events were and who caused the space increase.

To make the test cooler, I suggest opening two connections with different users and entering the data at the same time.

Identifying trace event types

Using the query below, we can quickly identify all EventClass that we can use in the :: fn_trace_gettable function:

SQL Server - fn_trace_gettable EventClass

Checking Growth Events

Now that our test base is done and the data has been entered, let's look at the database's AutoGrowth events using the query below:

SQL Server - Autogrowth Monitor

With this we can analyze which database suffered the autogrowth event, which specific file, how long the server took to increase the space, how much space was increased, which software is processing the query that caused the increase of the space, hostname, and login of the user running this query.

That's it folks!
I hope this tip helps you in some way.

Hugs and until the next post!