Monitoring the size of your database is one of the most important aspects of database administration. There are three storage sizes that come into play here. The maximum possible size, the currently allocated size, and how much is being used to store data.

The maximum size is the size that a database file can grow to. This is generally limited to the maximum allowed by the service level tier that you're using, or the physical size of the partition that your file is stored on. The Azure SQL database data storage graphic tells us that the maximum that our data file can grow to is 250 gigabytes. There is no point in allocating all 250 before it is needed, so only 2.64 gigabytes have been allocated of which only 2.4 gigabytes are being used.

These storage numbers only apply to the data file, that is the database's log file is not included in these numbers. On the other hand, looking at the property page of an SQL Server database we can see that the file type of rows, which is the data only storage, similar to the Azure graphic has an unlimited maximum size.

This database's allocated size will grow automatically in 64-megabyte chunks until the disk is full. However, the log file will only grow to a maximum size of 20 gigabytes. The allocated size of the database file is the actual file size when you view it within the computer's file system, so how much physical disk space is being taken up by the file.

Azure SQL databases use auto-growth so that the amount of disk space used is only as much as is required for the data being stored. In what is essentially a shared environment this makes a lot of sense, and the amount that and Azure database grows by can be viewed by querying sys.database_files.

While auto growth on the face of it is a good thing in that you manually don't have to increase the file size, there are some potential downsides. Every time the database needs to grow, which is in response to more data being added, the server has to take time out from processing queries to grow the file.

If the auto-growth amount specified is too small then the server will spend too much time allocating file space, and you will also end up with a file that is far more fragmented at the hard drive level, which will also impede performance.

One alternative is to set up a database without auto-growth, which can be done when you are specifying the data files, or you can specify a grow by size that will reduce the number of growth events. You may also want to change the auto-growth size temporarily, say when you are importing a very large dataset.

An Azure SQL managed instance database defaults to an auto-growth size of 16 megabytes. You could use the alter database command with modify file and the data files logical name specifying the file growth size. This value could be set to several hundred megabytes for the duration of the data import and then set back to the original production size.

It will come as no surprise that there is an Auto Shrink feature to go with auto grow. While Auto Shrink may be useful in terms of administration effort, Microsoft does say that it is not quite as effective at reclaiming disc space. Within the SQL Server community, the use of Auto Shrink is frowned upon, to put it mildly, and its use is very much discouraged due to performance degradation caused by it increasing index and file fragmentation.