When Does Autogrowth Happen For SQL Server Data Files?

Here’s the short version of this post: When it needs to, and not a moment before.

I know what you are thinking. You are thinking “but when will it need to grow”? Great question, really, and the answer is the same. The data file will grow automatically (providing you have autogrowth enabled, of course) when SQL Server needs to insert data and there is no more room left in the current file.

What this means is that there is no magic percentage that SQL Server uses in order to trigger an autogrowth event. This is important to understand when you are trying to configure alerts based upon your files being full. I blogged about my issues with alerts based upon free space available and autogrowth settings before. I know that some DBAs assume that the autogrowth event gets triggered when the data file gets to a certain point, say 90% full, but that is not true. We can test this right now.

Let’s create a database in SQL2012 using the defaults:

CREATE DATABASE tst_autogrowth;
GO

Let’s check out the current size of my new database:

USE tst_autogrowth;
GO

SELECT a.fileid
, CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)) AS [FILE_SIZE_MB]
, CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)) AS [SPACE_USED_MB]
, CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2)) AS [FREE_SPACE_MB]
, CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.000/(a.size/128.000),2)) AS [FREE_PCT_MB]
, left(a.NAME,15) AS [NAME]
, left(a.FILENAME,30) AS [FILENAME]
FROM sys.sysfiles a;
GO

The results I see are as follows:

Right now I have 0.88 megabytes free in my data file, which is about  901.12 kilobytes (give or take a few bits). The result set also shows that I have about 29% free space left in the file (or, if you prefer, 71% full).

Let’s insert some data. I will create a wide table as follows:

CREATE TABLE fill_me
    (col1 char(8000));
GO

Now that we have a table, let’s put some data inside of it. I want to know at what point will the file grow, and it can’t grow unless we insert some data. I’ve created a table where each row will fill one 8k page. It will take 112.64 pages in order to fill up the 901.12 kilobytes that we have available. So let’s start by inserting 100 rows:

SET NOCOUNT ON;
INSERT INTO fill_me (col1)
VALUES ('a');
GO 100

Now if we rerun the statement from earlier our result set should look similar to this:

 

This shows me that I now only have 0.06 megabytes left in my data file, and that it is 98% full, and (most important for the purpose of this post) the file has yet to grow!

This is because SQL Server knew it had enough space in the file to complete the insert. If there is space available for the transaction to complete, then there is no need to grow the data file. Let’s keep going, just to see how much we can get stuffed into this file. I estimate I can insert 12 more rows before the file will need to grow, so let us try that next:

SET NOCOUNT ON;
INSERT INTO fill_me (col1)
VALUES ('a');
GO 12

The results now show me this:

It’s completely full! Well, not completely, I mean there is a tiny bit of space left, but not enough for one more row. In fact, if I run the insert one more time I get back this result set:

We now have triggered the autogrowth event to happen. My data file was set to grow by 1 megabyte (which it did) and I can now see that I have 0.94 megabytes of free space and the data file is about 77% full.

How This Affects Your Alerting

You only want to be alerted when action is needed. If there is no action to take then you should want to collect the information to review later. You want the data, not the distraction.

With autogrowth enabled, however, the need for the alert is a moving target. In my example above I inserted tiny amounts of data in order to demonstrate that the file will not grow until absolutely necessary. However, I could have just as easily inserted a large image to be stored as a blob and caused my file to grow (likely many times over).

Even if you set an alert for your data file at 80% full you have no idea if someone is going to start a large insert operation when you are right at 79% full that will end up triggering an autogrowth event. If you have autogrowth enabled, how quickly would you react to that user that has initiated that transaction? Are you going to kill that session so the transaction rolls back?

In other words you can’t predict the future, nor can you prevent it from happening.

It would seem to make more sense to track disk space usage rather than data file usage. You still won’t stop that end user, but at least you can reduce the amount of alerts and noise possibly being generated by dozens of data files. I always liked to trigger an alert when my disk was over 85% full and there was less than 2GB of space left. That way I was more confident that I needed to take some action in order to keep things running uninterrupted.