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. And while addressing it, I’ll put it into the DBADiagnosticsdatabase that I’ve blogged about a few times before.
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.
This procedure for the DBADiagnostics database differs slightly from previous alerts. 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.
USE [DBADiagnostics] GO 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.