Find SQL Server data and log files that are almost out of space

On many SQL Servers database file size, either data or log, may be restricted to a maximum size to ensure there is adequate space on the server.  The problem with this is that if your data or log file runs out of space you will get an error message such as the following and your transactions will fail.


Msg 1105, Level 17, State 2, Line 1 Could not allocate space for object 'dbo.table1' in database 'test' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

The general solution I'm suggesting here will use a stored procedure called dbo.usp_get_db_files_near_maxsize. The procedure takes a parameter for space percentage or it will use the default of 10%.  This will then check each file for all databases on the server, including the system databases.

If the SP is run without passing in a parameter it will find all database files, both data and log, that are within 10% of being filled only for files where you have set a maximum size.

Here is the stored procedure and this can be created in master or in your admin database.

CREATE PROCEDURE dbo.usp_get_db_files_near_maxsize (@nearMaxSizePct DECIMAL (5,1) = 10.0)AS
BEGIN
    SET NOCOUNT ON

    CREATE TABLE ##ALL_DB_Files (
                    dbname SYSNAME,
                    fileid smallint,
                    groupid smallint,
                    [size] INT NOT NULL,
                    [maxsize] INT NOT NULL,
                    growth INT NOT NULL,
                    status INT,
                    perf INT,
                    [name] SYSNAME NOT NULL,
                    [filename] NVARCHAR(260) NOT NULL)

    -- loop over all databases and collect the information from sysfiles
    -- to the ALL_DB_Files tables using the sp_MsForEachDB system procedure
    EXEC sp_MsForEachDB
        @command1='use [$];Insert into ##ALL_DB_Files select db_name(), * from sysfiles',
        @replacechar = '$'

    -- output the results
    SELECT
        [dbname] AS DatabaseName,
        [name] AS dbFileLogicalName,
        [filename] AS dbFilePhysicalFilePath,
        ROUND(size * CONVERT(FLOAT,8) / 1024,0) AS ActualSizeMB,
        ROUND(maxsize * CONVERT(FLOAT,8) / 1024,0) AS MaxRestrictedSizeMB,
        ROUND(maxsize * CONVERT(FLOAT,8) / 1024,0) - ROUND(size * CONVERT(FLOAT,8) / 1024,0) AS SpaceLeftMB
    FROM ##ALL_DB_Files
    WHERE maxsize > -1 AND -- skip db files that have no max size
        ([maxsize] - [size]) * 1.0 < 0.01 * @nearMaxSizePct * [maxsize] -- find db files within percentage
    ORDER BY 6

    DROP TABLE ##ALL_DB_Files

    SET NOCOUNT OFF
ENDGO

As a DBA you should run this weekly or even daily to find all database files that are approaching the maximum size limit.  Then it is up to you to fix the problem by adding more space to the file. 

Ideally disk space would not be an issue and you woudl not have to worry about a maximum file size, but even with disk space not costing much there are still some systems that are limited and as a DBA you have to make do with what you have.

Ref: 

http://www.mssqltips.com