The World of Daman Beatty

Determining database Free Space and File Size Needed for SQL Server

0

There are many ways through which the database architects assess the database free space, but not all of those are professional methods. Here, in this article, we discuss the determination of database file size and free size to help monitor the shrink and script solutions. On defining the initial size of a database file can be defined with MAXSIZE and Autogrowth parameters. In typical cases, the initial size may be the same as the model databases in case if the users forget to define the size parameter on creating a new database.

Setting up the file size of a new database

  • Initially, database size can be easily set up with the use of the ‘SIZE’ parameter. The maximum space occupied by the given database can be set up with the ‘MAXSIZE’ parameter. If you do not use the MAXSIZE parameter to define the maximum size, then the default value will be taken as UNLIMITED.
  • The speed with which the given database files to reach the maximum space can be set using the FILEGROWTH parameter. This parameter will leave us with options to define it either:
    – With a specific absolute value or
    – With by giving the percentage

In both these cases, we should define values in the MB sizes. Typically, there is no boundary to the growth of a database file.

Auto Growth parameter

You must know that the SQL Server may not be able to commit any further transaction to the disk storage if the disk is full of the MAXIMIZE parameter is not defined. So, it is always advisable to define this parameter in advance to avoid falling into such situations. UNLIMITED also would fill the storage space, but before doing so, it will make various spaces much tinier than those were before. This may have a troublesome impact as there may not be enough space to execute the operating system programs. We may also consider the size of multiple databases to define the MAXSIZE attribute.

In larger platforms, a huge table data will be usually dispensed to multiple files to decrease the amount of disk contention. To enhance I/O performance, multiple file-group is supported by SQL Server with some secondary files. Data like index and client information could be stored in these secondary filegroups. This same methodology can be applied to the database log files also in which the users can create various files within a single DB and store log files on a different drive other than the MDF (main data file).

If no MAXSIZE parameter specified by the user, then the DB file can increase in size without boundaries. It is also suggested to construct and alert to avoid any chances of the disk getting full. When some specific conditions are existing, we may be able to report an issue or using this alert. Some of such frequently confronted issues to be handled this way are:

  • Free space in the log files or data files.
  • Log data for large transactions.
  • Not shrinking of increased log file size, and it is getting stuck due to large transactions.

If the Log sipping or Transaction backup is not set up, then the DB log file size may pop up regularly till the backup happens. On creation of a Log backup, free space will exist there.

Database File having free space

To shrink the given file in SQL Server, command as DBCC SHRINKFILE() can be used. Once this command runs, it can release the free space for the input parameter. As defined by RemoteDBA, it may look like the below.

The file may get shrunk with the file name or with the ID on running the above command. The shrinking size may be considered as given with the command in the MB unit.

When there are any transactional blocks or stuck in database transactions, the log files may be increased rapidly and cross the desired file size in terms of output. The issues related to transaction lock may arise with the Query performance, which could be the same issue at the client application. If transactions get truncated, and the log backup gets generated, there will be free space available. When there is a bulky DROP, Delete, or the Truncate operation being executed on the database table, there may be a huge amount of free space for more data files. This space could be reclaimed just by dropping the index too.

Such a query can also be performed in each of the databases to identify the free space for all. It can be done on result sets, and the space monitor criteria must be applied. The users can try the procedure of sp_msforeachdb() to monitor the T-SQL queries on each of the databases of SQL Server.

A typical T-SQL Query for free space and total space may be as below.


Considering the above query, for each database file, the file size may be stored in a temporary table named #FileSize. So, calculative logic of appropriate filters can be applied by the DBA Alert with calculation logic to be set as below:

  • If Free Space size (nMB) exceed the limit
  • If Free space percentage (n%) exceeds the limit compared to the total space
  • If the given size of the DB file exceeds the size limit (nMB)

In such cases, the DBA should take the necessary action by considering the priority and level of alert on the database.

How to reclaim space?

When the given disk is about to get full as per the alert, we may consider moving some files to another disk. If the given database is in the full recovery mode and log shipping is not configured in advance, then put the database to simple recovery. If that database is already in full recovery model, but the log shipping is not configured, then try to take Full backup and back up Transaction Log.

You may also change the parameter-based Max Size value for a given DB file if it has to be expanded. May also try to disable the auto-growth parameter and limit the (n)size to reclaim space. Here, we have discussed how to monitor the database file size and free space by monitoring the script. Feel free to put your comments and raise your queries in the comment section.