Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Monitor Storage Constraints for SQL Server Data Files


By:   |   Last Updated: 2015-03-19   |   Comments   |   Related Tips: More > Monitoring

Problem

In a previous tip, we covered one way to monitor free disk space on SQL Server.  However, this is not the only storage constraint for SQL Server Database Administrators.  Each file within a database can have limits imposed by its respective growth and max size parameters.  In other words, a physical disk can have an abundance of free space, but the database files residing on that disk may not be configured to automatically consume that free space as needed.  To that end, we (as DBAs) need a way to monitor storage constraints at the database level in addition to the disk level.  The code included in this tip provides one way to monitor storage constraints for data files within a database.

Solution

Prerequisite Considerations

  • Be sure to configure database mail and SQL Agent notifications as a prerequisite to ensure proper functionality of this monitoring job.
  • This solution assumes that custom error message 64002 is not currently in use by the target instance.  If this is not the case, code changes will be necessary.
  • The code has been tested against SQL Server 2012, but should function properly on version 2005 and greater. 

SQL Server Custom Error Message

Listing 1 creates the custom error message used by the used by the data file monitoring procedure for notifications.  If error message 64002 is already in use on the instance, it will be overwritten.

-- Listing 1
USE [master]
GO

-- Create the custom messages
EXEC dbo.sp_addmessage @msgnum = 64002,
    @msgtext = 'Data File Monitor Alert - %s',
    @severity = 8, @with_log = 'FALSE', @replace = 'REPLACE';
GO

SQL Server Data File Monitoring Stored Procedure

The code in listing 2 creates the stored procedure used to check databases on the local instance for data file storage constraints.  It is recommended that this procedure be created in a dedicated administration database as opposed to a user or system database.

--Listing 2
USE [MSSQLTips]    /*    If possible, use dedicated administrative database    */
GO

-- Create data file monitoring procedure
IF OBJECT_ID('[dbo].[MonitorDataFiles]', 'P') IS NULL
    EXEC('CREATE PROCEDURE [dbo].[MonitorDataFiles] AS SELECT 0');
GO

/****************************************************************************************
*    Author:        Mike Eastland                                                       *
*                                                                                       *
*    Purpose:    This procedure will check for databases on the local instance with data*
*                file space constraints.                                                *
****************************************************************************************/
ALTER PROCEDURE [dbo].[MonitorDataFiles] (
    @db_name        VARCHAR(128) = NULL,
        /*    Name of specific database to analyze    */
    @debug_mode        BIT = 0,
        /*    Determines if alerts are sent or just printed    */
    @fg_pct            FLOAT = 90.00,
        /*    Filegroup percent full threshold     */
    @growth_cnt        INT = 2,
        /*    Minimum number of file growth operations    */
    @growth_pct        FLOAT = NULL,
        /*    Minimum growth percentage related to current file size    */
    @max_size        FLOAT = NULL
        /*    Max desired size of data files (GB)    */
)
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE @cmd    VARCHAR(MAX),
        @db        VARCHAR(128),
        @msg    VARCHAR(MAX),
        @sev    TINYINT;

DECLARE    @db_file_usage    TABLE (
    FileID            SMALLINT,
    FileGroupNbr    SMALLINT,
    TotalExtents    FLOAT,
    UsedExtents        FLOAT,
    LogicalName        VARCHAR(64),
    PhysicalName    VARCHAR(256)
);

DECLARE    @db_file_detail    TABLE (
    LogicalName        VARCHAR(64),
    FileID            SMALLINT,
    PhysicalName    VARCHAR(256),
    FileGroupName    VARCHAR(64),
    FileSize        VARCHAR(32),
    MaxFileSize        VARCHAR(32),
    GrowthValue        VARCHAR(32),
    FileUsage        VARCHAR(16)
);

DECLARE    @db_files    TABLE (
    DatabaseName    VARCHAR(128),
    LogicalName        VARCHAR(64),
    PhysicalName    VARCHAR(256),
    FileGroupName    VARCHAR(64),
    FileSize        FLOAT,
    MaxFileSize        FLOAT,
    GrowthValue        FLOAT,
    SpaceUsed        FLOAT
);

DECLARE @svr_drives    TABLE  (
    DriveLetter        CHAR(1),
    FreeSpace        BIGINT
);

-- Parameter validation
IF @db_name IS NOT NULL AND DB_ID(@db_name) IS NULL
BEGIN
    SELECT @msg = 'Database [' + @db_name + '] not found on ' + @@SERVERNAME +
                    '. All databases will be analyzed.', @db_name = NULL;
        RAISERROR(@msg, 0, 0) WITH NOWAIT;
END

IF @fg_pct > 99.99
BEGIN
    SELECT @msg = 'The filegroup pct full threshold must be less than 100. ' +
                    'Resetting to default value of 90.00.', @fg_pct = 90.00;
        RAISERROR(@msg, 0, 0) WITH NOWAIT;
END

IF @growth_cnt > 20
BEGIN
    SELECT @msg = 'The filegrowth count threshold should be less than 20. ' +
                    'Resetting to default value of 2.', @growth_cnt = 2;
        RAISERROR(@msg, 0, 0) WITH NOWAIT;
END

-- Set conditional default variable values
SELECT    @growth_cnt = CASE @growth_cnt WHEN 0 THEN 1 ELSE @growth_cnt END,
        @sev = CASE @debug_mode WHEN 1 THEN 0 ELSE 8 END;

IF @growth_pct IS NOT NULL
BEGIN
    IF @growth_pct >= 1
        SET @growth_pct = @growth_pct / 100;

    IF @growth_pct > .50
    BEGIN
        SELECT @msg = 'The filegrowth percent threshold should be less than .5. ' +
                        'Resetting to default value of .05', @growth_pct = .05;
            RAISERROR(@msg, 0, 0) WITH NOWAIT;
    END
END

-- Populate temp table with free space of all local drives
INSERT INTO @svr_drives
    EXEC [master].dbo.xp_fixeddrives;

-- Convert FreeSpace value from MB to KB
UPDATE @svr_drives SET FreeSpace = FreeSpace * 1024;

DECLARE df_csr CURSOR FAST_FORWARD READ_ONLY FOR
SELECT [name]
FROM [master].sys.databases
WHERE DATABASEPROPERTYEX([name], 'Status') = 'ONLINE'
AND DATABASEPROPERTYEX([name], 'Updateability') <> 'READ_ONLY'
AND LOWER([name]) = LOWER(ISNULL(@db_name, [name]))
AND LOWER([name]) NOT IN ('model', 'northwind', 'pubs')
AND    LOWER([name]) NOT LIKE 'adventurework%'
AND [source_database_id] IS NULL
ORDER BY [name];

OPEN df_csr;

WHILE 0 = 0
BEGIN
    FETCH NEXT FROM df_csr INTO @db;
   
    IF @@FETCH_STATUS <> 0
        BREAK;

    -- Clear work table variables
    DELETE FROM @db_file_detail;
    DELETE FROM @db_file_usage;

    -- Populate temp table with file usage stats
    SET @cmd = 'USE [' + @db + ']; DBCC SHOWFILESTATS WITH NO_INFOMSGS';
        INSERT INTO @db_file_usage
            EXEC(@cmd);

    -- Get space details from files in current databases
    SET @cmd = 'USE [' + @db + ']; EXEC sp_helpfile';
        INSERT INTO @db_file_detail
            EXEC(@cmd);

    -- Populate table main temp table with parameters for all files in the current database
    INSERT INTO @db_files
    SELECT  @db [DatabaseName],
            d.LogicalName,
            d.PhysicalName,
            d.FileGroupName,
            CONVERT(BIGINT, REPLACE(d.FileSize, ' KB', '')) [FileSize],
            CASE UPPER(d.MaxFileSize)
                WHEN 'UNLIMITED' THEN -1
                ELSE CONVERT(BIGINT, REPLACE(d.MaxFileSize, ' KB', ''))
            END [MaxFileSize],
            CASE CHARINDEX('%', d.GrowthValue)
                WHEN 0 THEN CONVERT(FLOAT, CONVERT(BIGINT, REPLACE(d.GrowthValue, ' KB', '')))
                ELSE ROUND((CONVERT(FLOAT, CONVERT(BIGINT, REPLACE(d.FileSize, ' KB', ''))) *
                    (CONVERT(FLOAT, CONVERT(BIGINT, REPLACE(d.GrowthValue, '%', ''))) / 100)), 0)
            END [GrowthValue],
            u.[UsedExtents] * 64 [SpaceUsed]
    FROM @db_file_detail d
        INNER JOIN @db_file_usage u ON d.[FileID] = u.[FileID]
    WHERE LOWER(d.FileUsage) = 'data only';
END

CLOSE df_csr;
DEALLOCATE df_csr;

-- Display information in debug mode
IF @debug_mode = 1
BEGIN
    SELECT * FROM @svr_drives;

    SELECT *, [SpaceUsed]/[FileSize] * 100 [PctSpaceUsed] FROM @db_files;
END

SET @msg = 'Begin space constraint checks.';
    RAISERROR(@msg, 0, 0) WITH NOWAIT;

-- Check file size based on @max_size input parameter
IF @max_size IS NOT NULL
BEGIN
    SET @cmd = NULL;

    SELECT @cmd = COALESCE(@cmd + '; ', '') + d.DatabaseName + '-' +
                    d.FileGroupName + '-' + d.[PhysicalName]
    FROM @db_files d
    WHERE d.[SpaceUsed] > (@max_size * 1024 * 1024)
    ORDER BY d.DatabaseName, d.FileGroupName;

    SET @msg = 'The following databases on ' + @@SERVERNAME + ' contain files using more than ' +
                CAST(@max_size AS VARCHAR) + ' GB of space: ' + @cmd;

    IF @msg IS NOT NULL
        RAISERROR(64002, @sev, 1, @msg) WITH LOG, NOWAIT;
END

-- Check for available datafile growth space on physical drives
SET @cmd = NULL;

SELECT @cmd = COALESCE(@cmd + ', ', '') + d.DriveLetter
FROM @db_files f
    INNER JOIN @svr_drives d ON UPPER(LEFT(f.PhysicalName, 1)) = d.DriveLetter
GROUP BY d.DriveLetter, d.FreeSpace
HAVING d.FreeSpace <= SUM(f.GrowthValue)
ORDER BY d.DriveLetter;

SET @msg = 'The following drives on ' + @@SERVERNAME + ' do not have enough free space for all files ' +
            'to grow by the current value of their respective AutoGrowth parameter: ' + @cmd;

IF @msg IS NOT NULL
    RAISERROR(64002, @sev, 2, @msg) WITH LOG, NOWAIT;

-- Check for files with growth parameter set to less than @growth_pct
IF @growth_pct IS NOT NULL
BEGIN
    SET @cmd = NULL;

    SELECT @cmd = COALESCE(@cmd + '; ', '') + d.DatabaseName + '/' + d.LogicalName
    FROM @db_files d
    WHERE d.GrowthValue <> 0
    AND d.GrowthValue < (d.FileSize * @growth_pct)
    ORDER BY d.DatabaseName, d.LogicalName;

    SET @msg = 'The following database/file combinations on ' + @@SERVERNAME + ' have an AutoGrowth setting of less than ' +
                CONVERT(VARCHAR(4), (100 * @growth_pct)) + ' percent of the current file size: ' + @cmd;

    IF @msg IS NOT NULL
        RAISERROR(64002, @sev, 3, @msg) WITH LOG, NOWAIT;
END

-- Remove database/filegroup combinations from alert consideration if they have sufficient free space currently available.
DELETE d
FROM @db_files d
WHERE EXISTS (    SELECT *
                FROM @db_files d2
                WHERE d2.[DatabaseName] = d.[DatabaseName]
                AND d2.[FileGroupName] = d.[FileGroupName]
                GROUP BY [DatabaseName], [FileGroupName]
                HAVING ((SUM([SpaceUsed]) / SUM([FileSize])) * 100) < @fg_pct    )

-- Check for filegroups with no files set to autogrow
SET @cmd = NULL;

SELECT @cmd = COALESCE(@cmd + '; ', '') + d.DatabaseName + '/' + d.FileGroupName
FROM @db_files d
GROUP BY d.DatabaseName, d.FileGroupName
HAVING SUM(d.GrowthValue) = 0
ORDER BY d.DatabaseName, d.FileGroupName;

SET @msg = 'The following database/filegroup combinations on ' + @@SERVERNAME + ' are at least ' +
            CONVERT(VARCHAR(4), @fg_pct) + ' percent full and contain no files able to AutoGrow: ' + @cmd;

IF @msg IS NOT NULL
    RAISERROR(64002, @sev, 4, @msg) WITH LOG, NOWAIT;

-- Check for filegroups with no files having room to autogrow
SET @cmd = NULL;

SELECT @cmd = COALESCE(@cmd + '; ', '') + t.DatabaseName + '/' + t.FileGroupName
FROM @db_files t
WHERE t.MaxFileSize <> -1
AND t.MaxFileSize < t.FileSize + (@growth_cnt * t.GrowthValue)
AND NOT EXISTS (    SELECT *
                    FROM @db_files f
                    WHERE f.MaxFileSize >= f.FileSize + (@growth_cnt * f.GrowthValue)
                    AND f.DatabaseName = t.DatabaseName
                    AND f.FileGroupName = t.FileGroupName    )
GROUP BY t.DatabaseName, t.FileGroupName
ORDER BY t.DatabaseName, t.FileGroupName;

SET @msg = 'The following database/filegroup combinations on ' + @@SERVERNAME +
            ' contain no files that can grow at least ' + CONVERT(VARCHAR(4), @growth_cnt) + ' times: ' + @cmd;

IF @msg IS NOT NULL
    RAISERROR(64002, @sev, 5, @msg) WITH LOG, NOWAIT;

GO


The procedure checks for five different storage constraint scenarios.  The first (and probably least relevant) is to check for any data files that exceed the number of gigabytes specified by the @max_size parameter.  This may be useful to check certain databases for rapid growth in a relatively short amount of time.  The second scenario will check for any local disk on the server that cannot accommodate at least one file growth iteration for each data file that resides on the disk.  Third, the procedure checks for any data files with an auto growth setting smaller than a certain percentage of the current data file size.  This percentage threshold is set using the @growth_pct parameter.  The purpose of this check is to ensure that each data file is set to grow at an acceptable rate relative to its size.  The fourth scenario (and probably most relevant) is to check for databases containing a filegroup that is at least @fg_pct full, but has no files that can autogrow.  The key here is that this check is at the filegroup level.  As long as a given filegroup contains at least one file with the ability and room to autogrow, the procedure will not generate an alert.  For the fifth and final scenario, the procedure checks for any databases containing a filegroup with no files that can grow at least @growth_cnt number of times.

As with many things in the DBA world, your mileage may vary.  Be sure to test the procedure in your environment using different parameter combinations.  This can be done with the @debug_mode parameter set to 1, which should suppress the alerts.

SQL Server Agent Operator, Alert, and Job

The code in listing 3 targets the SQL Server Agent subsystem verify or create the remaining components required to automate data file monitoring.  Specifically, the code performs the following steps:

1. Check for the existence of a SQL Server Agent Operator
2. Create a custom SQL Server Agent Alert for the custom Error Message created by Listing 1.
3. Create a SQL Server Agent Job to execute the data file monitoring procedure created by Listing 2.

-- Listing 3
USE [msdb]
GO

-- Declare variables
DECLARE @AlertName    NVARCHAR(128),
        @DatabaseName NVARCHAR(12),
        @DebugMessage VARCHAR(MAX),
        @JobCategory NVARCHAR(128),
        @JobCmd NVARCHAR(MAX),
        @JobDescr NVARCHAR(128),
        @JobID BINARY(16),
        @JobName NVARCHAR(64),
        @JobOper NVARCHAR(32),
        @JobStep NVARCHAR(128),
        @JobSubSystem NVARCHAR(16),
        @ReturnCode BIGINT;

-- Initialize variables
SET @AlertName = N'Data File Monitor';
SET @DatabaseName = 'MSSQLTips';    /*    Set to name of dedicated administrative database on the local instance    */
SET @JobCategory = N'Instance Monitor';
SET @JobCmd = 'EXEC dbo.MonitorDataFiles';
SET @JobDescr = N'Check for database file groups nearing capacity.';
SET @JobName = N'Monitor - Data Files';
SET @JobOper = N'sqlDBA';    /*    Set to name of preferred SQL Server Agent Operator on the local instance    */
SET @JobStep = @JobDescr
SET @JobSubSystem ='TSQL';
SET @ReturnCode = 0;
-- End variable initialization

-- Check for existence of specified operator; use failsafe operator if it doesn't
IF NOT EXISTS (SELECT * FROM dbo.[sysoperators] WHERE [name] = @JobOper)
BEGIN
    SET @DebugMessage = 'Operator [' + @JobOper + '] not found; checking for failsafe operator.';
        RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;

    EXEC [master].dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
        N'Software\Microsoft\MSSQLServer\SQLServerAgent',
        N'AlertFailSafeOperator', @JobOper OUTPUT;
END

IF @JobOper IS NULL
BEGIN
    SET @DebugMessage = 'No failsafe operator found; Job [' + @JobName +
    '] will not be created without notification functionality.';
        RAISERROR(@DebugMessage, 8, 0) WITH LOG, NOWAIT;
            GOTO QuitWithRollback;
END

-- Create alert associated with custom alert message
IF EXISTS (SELECT * FROM [dbo].[sysalerts] WHERE [name] = @AlertName)
    EXEC dbo.sp_delete_alert @name = @AlertName;

EXEC dbo.sp_add_alert @name = @AlertName, @message_id = 64002, @severity = 0, @enabled = 1,
    @delay_between_responses = 0, @include_event_description_in = 3;

EXEC dbo.sp_add_notification @alert_name = @AlertName, @operator_name = @JobOper,
    @notification_method = 1;

-- Create SQL Server Agent job
BEGIN TRANSACTION

-- Create job category if it doesn't already exist
IF NOT EXISTS (SELECT name FROM dbo.syscategories WHERE name = @JobCategory AND category_class = 1)
BEGIN
    EXEC @ReturnCode = dbo.sp_add_category @class = N'JOB', @type = N'LOCAL', @name = @JobCategory;

    IF (@@ERROR <> 0 OR @ReturnCode <> 0)
    BEGIN
        SET @DebugMessage = 'Error creating job category [' + @JobCategory + ']';
            RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;
                GOTO QuitWithRollback;
    END
END

-- Delete job if it already exists
IF EXISTS (SELECT job_id FROM dbo.sysjobs_view WHERE [name] = @JobName)
    EXEC dbo.sp_delete_job @job_name = @JobName, @delete_unused_schedule = 1;

-- Create data file monitoring job
EXEC @ReturnCode = dbo.sp_add_job @job_name = @JobName, @enabled = 1, @notify_level_eventlog = 2,
    @notify_level_email = 2, @notify_level_netsend = 0, @notify_level_page = 0,
    @notify_email_operator_name = @JobOper, @delete_level = 0, @description = @JobDescr,
    @category_name = @JobCategory, @owner_login_name = N'sa', @job_id = @JobID OUTPUT;

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
    SET @DebugMessage = 'Error creating job [' + @JobName + ']';
        RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;
            GOTO QuitWithRollback;
END

-- Add step to job
EXEC @ReturnCode = dbo.sp_add_jobstep @job_id = @JobID, @step_name = @JobStep, @step_id = 1,
    @cmdexec_success_code = 0, @on_success_action = 1, @on_success_step_id = 0,
    @on_fail_action = 2, @on_fail_step_id = 0, @retry_attempts = 0, @retry_interval = 0,
    @os_run_priority = 0, @subsystem = @JobSubSystem, @command = @JobCmd,
    @database_name = @DatabaseName, @flags = 0;

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
    SET @DebugMessage = 'Error creating job step [' + @JobStep + ']';
        RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;
            GOTO QuitWithRollback;
END

-- Explicitly set step id on which job will start
EXEC @ReturnCode = dbo.sp_update_job @job_id = @JobID, @start_step_id = 1;

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
    SET @DebugMessage = 'Error setting start step for job [' + @JobName + ']';
        RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;
            GOTO QuitWithRollback;
END

-- Create job schedule
EXEC @ReturnCode = dbo.sp_add_jobschedule @job_id = @JobID, @name = @JobName, @enabled = 1, @freq_type = 4,
    @freq_interval = 1, @freq_subday_type = 8, @freq_subday_interval = 1, @freq_relative_interval = 0,
    @freq_recurrence_factor = 0, @active_start_date = 19900101, @active_end_date = 99991231,
    @active_start_time = 1100, @active_end_time = 235959;

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
    SET @DebugMessage = 'Error creating job schedule [' + @JobName + ']';
        RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;
            GOTO QuitWithRollback;
END

-- Designate server for job execution
EXEC @ReturnCode = dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)';

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
    SET @DebugMessage = 'Error setting job server for job [' + @JobName + ']';
        RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;
            GOTO QuitWithRollback;
END

-- Commit changes if no errors occur
IF @@TRANCOUNT <> 0
    COMMIT TRANSACTION;

SET @DebugMessage = @JobName + ' job has been (re)created.';
    RAISERROR(@DebugMessage, 0, 0) WITH NOWAIT;

-- Rollback changes if error occurs at any point in script
QuitWithRollback:
IF @@TRANCOUNT <> 0
    ROLLBACK TRANSACTION;

GO

Before executing the script in listing 3, be sure to update the values for the @DatabaseName and @JobOper variables to reflect the dedicated administrative database and desired local SQL Server Agent Operator, respectively.

Conclusion

The code in this tip provides a way to monitor storage constraints for data files within one or more databases using tools and functionality native to SQL Server.

Next Steps
  • Update the job schedule based on the desired monitoring interval.  The job is scheduled to run hourly by default.
  • Check out other MSSQLTips related to monitoring SQL Server.


Last Updated: 2015-03-19


get scripts

next tip button



About the author
MSSQLTips author Mike Eastland Mike Eastland has been a SQL Server DBA since 1999. His main areas of interest are monitoring and the automation of administrative tasks.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools