Checking the Free Space in Data and Log Files in a Database in SQL Server

In a database, data and log files are used to store the data and log records, respectively. It is important to monitor the free space in these files to ensure that there is enough space for new data to be inserted and that the database can continue to function properly. In this tutorial, we will show you how to check the free space in data and log files in a database in SQL Server.

Using SQL Server Management Studio (SSMS)

1. Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance.
2. In the Object Explorer, expand the Databases node and then select the database for which you want to check the free space.
3. Right-click on the database and select “Properties” from the context menu.
4. In the Properties window, select the “Files” page.
5. Here, you will see a list of the data and log files for the database, along with the size and free space for each file.

Using T-SQL Commands

You can also use the following T-SQL command to check the free space in data and log files:

Transact-SQL

--To check the Free space in data and log files SELECT [TYPE] = A.TYPE_DESC ,[FILE_Name] = A.name ,[FILEGROUP_NAME] = fg.name ,[File_Location] = A.PHYSICAL_NAME ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0) ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)) ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0) ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100) ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -' WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted' ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id order by A.TYPE desc, A.NAME;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

--To check the Free space in data and log files SELECT     [TYPE] = A.TYPE_DESC     ,[FILE_Name] = A.name     ,[FILEGROUP_NAME] = fg.name     ,[File_Location] = A.PHYSICAL_NAME     ,[FILESIZE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0)     ,[USEDSPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0))     ,[FREESPACE_MB] = CONVERT(DECIMAL(10,2),A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)     ,[FREESPACE_%] = CONVERT(DECIMAL(10,2),((A.SIZE/128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT)/128.0)/(A.SIZE/128.0))*100)     ,[AutoGrow] = 'By ' + CASE is_percent_growth WHEN 0 THEN CAST(growth/128 AS VARCHAR(10)) + ' MB -'         WHEN 1 THEN CAST(growth AS VARCHAR(10)) + '% -' ELSE '' END         + CASE max_size WHEN 0 THEN 'DISABLED' WHEN -1 THEN ' Unrestricted'             ELSE ' Restricted to ' + CAST(max_size/(128*1024) AS VARCHAR(10)) + ' GB' END         + CASE is_percent_growth WHEN 1 THEN ' [autogrowth by percent, BAD setting!]' ELSE '' END FROM sys.database_files A LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id order by A.TYPE desc, A.NAME;

This will show you the total size, used space, and unused space for the data and log files of the specified database.

That’s it! You now know how to check the free space in data and log files in a database in SQL Server using both SSMS and T-SQL commands. This information is important when you want to ensure that your database has enough space to function properly and to avoid potential issues due to lack of space.

I hope this helps! Let me know if you have any questions or need further assistance.