How To Check Free Space in Data and Log Files 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)

Open SQL Server Management Studio (SSMS) and connect to the SQL Server instance.
In the Object Explorer, expand the Databases node and then select the database for which you want to check the free space.
Right-click on the database and select “Properties” from the context menu.
In the Properties window, select the “Files” page.
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 of a single database:

-- Retrieve information about database files and their properties
-- including file type, file name, filegroup name, file location, file size, used space, free space, and percentage of free space.
-- The results are sorted by file type in descending order and then by file name.

-- Step 1: Select the necessary columns and provide descriptive aliases
SELECT
  A.TYPE_DESC AS FileType,
  A.NAME AS FileName,
  fg.name AS FileGroupName,
  A.PHYSICAL_NAME AS FileLocation,
  CONVERT(DECIMAL(10, 2), A.SIZE / 128.0) AS FileSize_MB,
  CONVERT(DECIMAL(10, 2), A.SIZE / 128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT) / 128.0) AS UsedSpace_MB,
  CONVERT(DECIMAL(10, 2), A.SIZE / 128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT) / 128.0) AS FreeSpace_MB,
  CONVERT(DECIMAL(10, 2), ((A.SIZE / 128.0 - CAST(FILEPROPERTY(A.NAME, 'SPACEUSED') AS INT) / 128.0) / (A.SIZE / 128.0)) * 100) AS FreeSpacePercentage,
  'By ' +
    CASE
      WHEN is_percent_growth = 0 THEN CAST(growth / 128 AS VARCHAR(10)) + ' MB -'
      WHEN is_percent_growth = 1 THEN CAST(growth AS VARCHAR(10)) + '% -'
      ELSE ''
    END +
    CASE
      WHEN max_size = 0 THEN 'DISABLED'
      WHEN max_size = -1 THEN ' Unrestricted'
      ELSE ' Restricted to ' + CAST(max_size / (128 * 1024) AS VARCHAR(10)) + ' GB'
    END +
    CASE
      WHEN is_percent_growth = 1 THEN ' [autogrowth by percent, BAD setting!]'
      ELSE ''
    END AS AutoGrow

-- Step 2: Retrieve information from the sys.database_files system view
FROM sys.database_files A

-- Step 3: Perform a left join with the sys.filegroups system view
LEFT JOIN sys.filegroups fg ON A.data_space_id = fg.data_space_id

-- Step 4: Order the results by file type in descending order and then by file name
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.

Now you can also use the following T-SQL command to check the free space in data and log files of all database:

-- Drop the temporary table if it exists
IF OBJECT_ID('tempdb..#DatabaseReport') IS NOT NULL
    DROP TABLE #DatabaseReport;

-- Create a temporary table to store the report results
CREATE TABLE #DatabaseReport
(
    [Database] NVARCHAR(128),
    [File Type] NVARCHAR(60),
    [File Name] NVARCHAR(128),
    [Filegroup] NVARCHAR(128),
    [File Location] NVARCHAR(260),
    [Total Size (MB)] DECIMAL(10, 2),
    [Used Space (MB)] DECIMAL(10, 2),
    [Free Space (MB)] DECIMAL(10, 2),
    [Free Space (%) ] DECIMAL(10, 2),
    [Autogrowth] NVARCHAR(400)
);

-- Cursor to iterate over databases
DECLARE @DatabaseName NVARCHAR(128);

DECLARE DatabaseCursor CURSOR FOR
    SELECT [name]
    FROM sys.databases
    WHERE [state] = 0 -- Exclude offline databases

OPEN DatabaseCursor;
FETCH NEXT FROM DatabaseCursor INTO @DatabaseName;

-- Loop through each database and execute the query dynamically
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SqlQuery NVARCHAR(MAX);

    -- Generate the query to retrieve file information for each database
    SET @SqlQuery = 'USE [' + @DatabaseName + ']; ' +
        'INSERT INTO #DatabaseReport ' +
        '(' +
            '[Database], ' +
            '[File Type], ' +
            '[File Name], ' +
            '[Filegroup], ' +
            '[File Location], ' +
            '[Total Size (MB)], ' +
            '[Used Space (MB)], ' +
            '[Free Space (MB)], ' +
            '[Free Space (%)], ' +
            '[Autogrowth]' +
        ')' +
        'SELECT ' +
            '''' + @DatabaseName + ''', ' +
            'A.TYPE_DESC AS [File Type], ' +
            'fg.name AS [Filegroup], ' +
            'A.NAME AS [File Name], ' +
            'A.PHYSICAL_NAME AS [File Location], ' +
            'CONVERT(DECIMAL(10, 2), A.SIZE / 128.0) AS [Total Size (MB)], ' +
            'CONVERT(DECIMAL(10, 2), A.SIZE / 128.0 - CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT) / 128.0) AS [Used Space (MB)], ' +
            'CONVERT(DECIMAL(10, 2), A.SIZE / 128.0 - CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT) / 128.0) AS [Free Space (MB)], ' +
            'CONVERT(DECIMAL(10, 2), ((A.SIZE / 128.0 - CAST(FILEPROPERTY(A.NAME, ''SPACEUSED'') AS INT) / 128.0) / (A.SIZE / 128.0)) * 100) AS [Free Space (%)], ' +
            '''By '' + ' +
            'CASE ' +
                'WHEN is_percent_growth = 0 THEN CAST(growth / 128 AS VARCHAR(10)) + '' MB -'' ' +
                'WHEN is_percent_growth = 1 THEN CAST(growth AS VARCHAR(10)) + ''% -'' ' +
                'ELSE '''' ' +
            'END + ' +
            'CASE ' +
                'WHEN max_size = 0 THEN ''DISABLED'' ' +
                'WHEN max_size = -1 THEN '' Unrestricted'' ' +
                'ELSE '' Restricted to '' + CAST(max_size / (128 * 1024) AS VARCHAR(10)) + '' GB'' ' +
            'END + ' +
            'CASE ' +
                'WHEN is_percent_growth = 1 THEN '' [Autogrowth by percent, BAD setting!]'' ' +
                'ELSE '''' ' +
            'END AS [Autogrowth] ' +
        '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;';

    -- Execute the dynamically generated query
    EXEC sp_executesql @SqlQuery;

    FETCH NEXT FROM DatabaseCursor INTO @DatabaseName;
END;

CLOSE DatabaseCursor;
DEALLOCATE DatabaseCursor;

-- Select the report results from the temporary table
SELECT
    [Database],
    [File Type],
    [File Name],
    [Filegroup],
    [File Location],
    [Total Size (MB)],
    [Used Space (MB)],
    [Free Space (MB)],
    [Free Space (%)],
    [Autogrowth]
FROM #DatabaseReport
ORDER BY [Database], [File Type], [File Name];

-- Drop the temporary table
DROP TABLE #DatabaseReport;

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.

Using SQL Server Management Studio (SSMS)

Using T-SQL Commands

Leave a Reply Cancel reply