Monitor Storage Constraints for SQL Server Data Files

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

-- 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