I haven’t blogged much since getting back from vacation.  It seems like a good restart after the two week hiatus would be to address an issue that has arisen countless times.

Sudden File Growth

In a number of SQL Server environments that I’ve worked in there either isn’t a method for monitoring file size or the process is more of a rubber stamp morning check.  The DBA gets in and looks at the size of the files and if there isn’t a log of changes to update nothing is really done and changes aren’t investigated.

In most cases this won’t be a problem.  For instance, if you’ve gone out and pre-grown your data and log files to appropriate sizes then nothing will grow and there is nothing to see here.

In cases were the unexpected happens, though, database files can and will grow.  And in the worst of these cases, which only occur at night or on vacations, the files will grow to a point where there is no longer any disk space available.  And if your annual review is next week, this will happen to the log file and force your database offline.

Knowing Is Half the Battle

In these types of situations, I like to recall the last couple minutes of the GI Joe epos ides that I watched as a kid.  They almost always ended with the quote, “Knowing is half the battle!  Yo, Joe!”.  If I know that a file growth has happened then I can do something about it.

Now the best case is to know, monitor, and plan for upcoming file growths.  This is what might be called a best practice and if you’re not doing it I’d really recommend putting a process in place.  But we need to be prepared for the unexpected.  And even in the most best, rock solid environment, I’d recommend a file growth monitoring process.

The Solution

This procedure differs slightly from previous alerts that I’ve blogged about.  Instead of sending out an e-mail that aggregates all of the changes an error is raised for each file change in question.  This method was selected because this allows the process to be tool agnostic.

If the client has a log file monitoring process, then the error can be picked up that way.  Or if all of the alerts are generated from the SQL Server instances, then a SQL Agent Alert can be created to notify people about the issue.

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Monitor')
    EXEC('CREATE SCHEMA [Monitor] AUTHORIZATION [dbo]')
GO

IF OBJECT_ID('Monitor.FileGrowth') IS NULL
BEGIN
    CREATE TABLE [Monitor].[FileGrowth]
        (
        DatabaseName sysname NOT NULL,
        DatabaseFileName nvarchar(260) NOT NULL,
        FileSizeMB decimal(18,3) NOT NULL
        CONSTRAINT PK_MonitorFileGrowth PRIMARY KEY(DatabaseName, DatabaseFileName)
        )
END

IF OBJECT_ID('Alert.FileGrowth') IS NOT NULL
    DROP PROCEDURE [Alert].[FileGrowth]
GO

/*================================================================================
Procedure:    [Alert].[FileGrowth]
Author:        Jason Strate
Date:        2007-11-14

Synopsis:
    Procedures monitors the size of each file for all user databases and tempdb.  In
    the event of file growrh for any of the monitored databases, a error is raised
    that can be captured through either tools monitoring SQL Servers log files or
    through SQL Agent Alerts.

================================================================================
Revision History:
Date:        By            Description
----------------------------------------------------------------------------------
================================================================================*/
CREATE PROCEDURE [Alert].[FileGrowth] 

As

SET NOCOUNT ON

-- Validate that necessary error message exists
IF NOT EXISTS (SELECT * FROM master..sysmessages WHERE error = 70000)
    EXEC master..sp_addmessage  @msgnum = 70000, @severity = 12, @with_log = 'true',
        @msgtext = 'File growth has occured in the database %s on the file %s.  The size has increased from %d to %d.  If the file growth was unplanned, please review for unexpected issues.', @replace = 'REPLACE'

-- Insert files for each database that are not currently being watched
INSERT INTO [Monitor].[FileGrowth] (DatabaseName, DatabaseFileName, FileSizeM
SELECT d.name, mf.name, CAST(mf.size as float)*8/1024
FROM sys.databases d
    INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
    LEFT OUTER JOIN Monitor.FileGrowth fg ON d.name = fg.DatabaseName AND mf.name = fg.DatabaseFileName
WHERE d.state_desc = 'ONLINE'
AND source_database_id IS NULL
AND d.name NOT IN ('model', 'master', 'msdb')
AND fg.FileSizeMB IS NULL

-- Delete databases that no longer exist
DELETE FROM Monitor.FileGrowth
FROM Monitor.FileGrowth fg
    LEFT OUTER JOIN sys.databases d ON fg.DatabaseName = d.name
WHERE d.name IS NULL

DECLARE @FileGrowthDelta table
    (
    DatabaseName sysname
    ,DatabaseFileName nvarchar(260)
    ,OldFileSizeMB decimal(18,3)
    ,NewFileSizeMB decimal(18,3)
    )

-- Update files that changed size and output delta rows
UPDATE fg
SET FileSizeMB = CAST(CAST(mf.size as float)*8/1024 as decimal(18,3))
OUTPUT INSERTED.DatabaseName, INSERTED.DatabaseFileName, INSERTED.FileSizeMB, DELETED.FileSizeMB
INTO @FileGrowthDelta
FROM Monitor.FileGrowth fg
    INNER JOIN sys.databases d ON d.name = fg.DatabaseName
    INNER JOIN sys.master_files mf ON d.database_id = mf.database_id AND mf.name = fg.DatabaseFileName
WHERE d.state_desc = 'ONLINE'
AND source_database_id IS NULL
AND d.name NOT IN ('model', 'master', 'msdb')
AND FileSizeMB  CAST(CAST(mf.size as float)*8/1024 as decimal(18,3))

--Declare variables section for triggering error event
DECLARE @DatabaseName sysname
    ,@DatabaseFileName nvarchar(260)
    ,@OldFileSizeMB int
    ,@NewFileSizeMB int

--For each database name in sysdatabases
DECLARE ALTER_FILE_GROWTH_CURSOR CURSOR LOCAL FAST_FORWARD FOR
    SELECT DatabaseName
        ,DatabaseFileName
        ,OldFileSizeMB
        ,NewFileSizeMB
    FROM @FileGrowthDelta

OPEN ALTER_FILE_GROWTH_CURSOR
FETCH NEXT FROM ALTER_FILE_GROWTH_CURSOR INTO @DatabaseName, @DatabaseFileName, @OldFileSizeMB, @NewFileSizeMB

WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR(70000, 0, 1, @DatabaseName, @DatabaseFileName, @OldFileSizeMB, @NewFileSizeM WITH LOG
    FETCH NEXT FROM ALTER_FILE_GROWTH_CURSOR INTO @DatabaseName, @DatabaseFileName, @OldFileSizeMB, @NewFileSizeMB
END

CLOSE ALTER_FILE_GROWTH_CURSOR
DEALLOCATE ALTER_FILE_GROWTH_CURSOR
GO

EXEC [Alert].[FileGrowth]

In the next post, I’ll outline the process that I use to check to see if there are any files that may need be running out of available space.