Determine Free Space In SQL Server Database

In our previous section we have discussed about Using Stored Procedures in SQL Server For Better Performance and know various advantages of using them in SQL Server. Now, In this article we will discuss about how to check free space in SQL Server Database. As we already know that the SQL Server is a relational database management system, developed by Microsoft that is used for storage and retrieval of data whenever required. SQL Server Database contains one primary database file (.mdf) and one secondary database file(.ndf) which is associated with Master Database file. Each MDF file is attached with a separate transaction Log Database File (LDF). The users of SQL Server often faces problem when the size of their SQL database increases as it may leads to Database full error. One of the main task of database administrator is to keep track on free space available within the database and database files.

Why Determining Free Space Is Required in SQL Server database?

Even though the default Autogrow and Autoshrink settings provided by Microsoft, can help users in managing the size of the database without user’s intervention, but managing the database files manually is better option. Keeping an eye on free space of the database, it not only help in managing the database, but this information also helps in shrinking data files as needed if there is free space in the files. Let’s know how to find free space in database.

Different Ways to Determine Free Space in SQL Server database

In order to manage the SQL database and database files in better way, we need to determine free space present within the database files.
First, we need to have a database and get the information about the files.

We can see the total size of the database and the current size of the data files but it does not tell us about the free space present in the database. In the output, we can see two files “Solivia” and “Solivia_log” with their respective sizes. One is the Master Data File and other is Log File of the database. Only these two files are present in the database and hence they are displayed.
Ways to find the free space:
1) Use sp_spaceused to check free space in SQL Server

USE Solivia
GO
sp_spaceused
GO

The command will give you information about the total free space of the database but the free space of the transaction log file is not properly defined. The output is shown below-

2) Use DBCC SQLPERF to check free space in SQL Server Database

USE Solivia
GO
DBCC SQLPERF(logspace)
GO

This command provides transaction log space usage statistics of entire database of the SQL Server. LOGSPACE gives the current size of the transaction log and the percentage of log space used for every database. Output is shown below:

3) Use DBCC SHRINKFILE to determine free space in SQL log file

USE Solivia
GO
DBCC SHRINKFILE(Solivia)
DBCC SHRINKFILE(Solivia_log)
GO

The DBCC SHRINKFILE shrinks the size of the specified data or log file for the current database or empties a file by moving the data from the specified file to other files in the same file group, allowing the file to be removed from the database.This command can shrink a file to a size less than the size specified, which resets the minimum file size to a new value.

Here is the detailed description of the output. The column which is in minimum size, tells us the size we initially made for the file when it was created. It also tells about the information that could help in shrinking the files.
Description of the columns of the output:

The drawback is that if there is free space in the file, it will shrink the file even if we use the command with a much higher value for the file size and clear SQL Server transaction log.
4) Use FILEPROPERTY to find free space in a database
We can calculate free space by using this command, based on the current size and what is being used for.

USE Solivia
GO
SELECTDB_NAME()AS DbName,
name ASFileName,
size/128.0 AS CurrentSizeMB,
size/128.0 -CAST(FILEPROPERTY(name,'SpaceUsed')ASINT)/128.0 AS FreeSpaceMB 
FROMsys.database_files;

Below is the result of the above command:

Observation:

We have discussed several ways to determine free space in SQL Server database and database files, the FILEPROPERTY option gives the best result. Moreover, the Minimum Size value from DBCC SHRINKFILE also gave us good output. The users of SQL Server can use any of the ways that works best for their database. Another option for determining the free space in SQL Server database is to use a third party tool, which can be used to view log files detail.