Everyone likes taking some time to relax. You’re sitting out in the sunshine or on a beach and the waitress is bringing you your favorite beverage. The company and conversation is great and it seems like the perfect day. And then… suddenly without warning… your drink is gone.
You were having a great time and the waiter, who should be monitoring for this situation, failed to do so. As the conversation is interrupted by this horror of this situation you look around the table and start sipping on what’s left of the water you had with dinner.
What if there wasn’t water to switch to? What would have happened?
In much the same way, DBAs are the waiters and waitresses for their SQL Servers. Instead of serving beverages, we provide space to the database files for our environments. In most cases the conversations (transactions) in the database will be safe from harm because there will be extra water (space) laying around.
What if there isn’t and you haven’t planned ahead for this scenario? It would be worse to have you actual drinks and evening with friends interrupted because the SQL Servers are offline because they ran out of the space necessary to grow.
I’m Growing and Taking More Beverages With Me
In a previous post I talked about knowing when a file growth has occurred. This is the after-the-fact alert to inform that things may not be going as planned. Before you get to that point, it would be nice to have something in place that informs that unplanned growth may occur.
To that end…
How Many Kegs Are Left In the Cooler?
To help alleviate evening interruptions, I put together a file space monitoring alert for the DBADiagnostics database a while back. This alert is usually scheduled through a SQL Server Agent job and is executed in the morning before the day starts. This allows me to see which databases may need some action through the day and I get all day minus meetings, emergencies, meetings, and other work to get this taken care of.
To cut to the chase, here is the procedure:
USE [DBADiagnostics] GO IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Alert') EXEC('CREATE SCHEMA [Alert] AUTHORIZATION [dbo]') GO IF OBJECT_ID('Alert.FilegroupSpaceAvailable') IS NOT NULL DROP PROCEDURE [Alert].[FilegroupSpaceAvailable] GO /*================================================================================ Procedure: Alert.FilegroupSpaceAvailable Author: Jason Strate Date: 2005-04-05 Synopsis: Alert to monitor amount of available space within database datafiles. This process does not take into account space of transaction logs. Database recovery strategy should account for those files. Parameters: @PercentFull: Threshold percenteage for space used in data file @MBEmpty: Threshold MB value for space remaining in each data file. Prevents false-positive alerts for large databases @ToAddress: E-mail address to send the alert @SubjectWarning: Prefix to e-mail message to code or rate the alert ================================================================================ Revision History: Date: By Description ---------------------------------------------------------------------------------- ================================================================================*/ CREATE PROCEDURE [Alert].[FilegroupSpaceAvailable] ( @PercentFull decimal(5,2) = 90, @MBEmpty int = 2500, @ToAddress varchar(255), @SubjectWarning varchar(255) = '' ) AS SET NOCOUNT ON --Declare variables section DECLARE @db_full_pages float, @db_full_percentage float, @SQL nvarchar(4000), @db_size float IF @PercentFull NOT BETWEEN 1 AND 99 OR @PercentFull IS NULL BEGIN RAISERROR('@PercentFull must be a value between 1 and 99.', 16, 1) RETURN -1 END IF @MBEmpty IS NULL BEGIN RAISERROR('@MBEmpty can not be null.', 16, 1) RETURN -1 END IF OBJECT_ID('tempdb..#FileStats') IS NOT NULL DROP TABLE #FileStats CREATE TABLE #FileStats ( FileID int, FileGroup int, TotalExtents bigint, UsedExtents bigint, Name varchar(255), [FileName] varchar(255) ) IF OBJECT_ID('tempdb..#Output') IS NOT NULL DROP TABLE #Output CREATE TABLE #Output ( DatabaseName sysname, ID int IDENTITY(1,1), FileGroup varchar(255), FileName varchar(255), FilePath varchar(255), SpaceAvailable int, SpaceUsed int, PercentUsed decimal(12,6) ) DECLARE @DBName nvarchar(128) DECLARE CURS_DATABASE CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.databases WHERE source_database_id IS NULL AND state_desc = 'ONLINE' AND is_read_only = 0 AND is_in_standby = 0 AND name NOT IN ('model', 'master', 'msdb') OPEN CURS_DATABASE FETCH NEXT From CURS_DATABASE INTO @DBName WHILE @@Fetch_Status = 0 BEGIN Set @SQL = 'Use [' + @DBName + ']; Exec(''DBCC showfilestats'')' DELETE FROM #FileStats INSERT INTO #FileStats EXEC sp_ExecuteSQL @SQL Set @SQL = 'Use [' + @DBName + ']; SELECT ''' + @DBName + ''' ,RTRIM(fg.name) ,RTRIM(f.name) ,RTRIM(f.physical_name) ,(TotalExtents * 64) / 1024. ,(UsedExtents * 64) / 1024. ,100 * CAST(((UsedExtents * 64) / 1024.)/((TotalExtents * 64) / 1024.) as decimal(12,6)) FROM #FileStats fs INNER JOIN sys.database_files f On fs.Name = f.Name COLLATE SQL_Latin1_General_CP1_CI_AS INNER JOIN sys.filegroups fg On f.data_space_id = fg.data_space_id WHERE CAST(((UsedExtents * 64) / 1024.)/((TotalExtents * 64) / 1024.) as decimal(12,6)) > ' + Convert(varchar, @PercentFull/100) + ' AND ((TotalExtents * 64)) / 1024. - ((UsedExtents * 64) / 1024.) <= ' + Convert(varchar, @MBEmpty) + ' ORDER BY fg.name, f.name' INSERT INTO #Output EXEC sp_ExecuteSQL @SQL FETCH NEXT From CURS_DATABASE INTO @DBNAME END CLOSE CURS_DATABASE DEALLOCATE CURS_DATABASE IF EXISTS (SELECT * FROM #Output) BEGIN DECLARE NOTIFICATION_CURS CURSOR LOCAL FAST_FORWARD FOR SELECT DatabaseName, FileGroup, FileName, FilePath, SpaceAvailable, SpaceUsed, PercentUsed FROM #Output DECLARE @DatabaseName nvarchar(255), @FileGroup nvarchar(255), @FileName nvarchar(255), @FilePath nvarchar(255), @SpaceAvailable nvarchar(255), @SpaceUsed nvarchar(255), @PercentUsed nvarchar(255), @Subject nvarchar(255), @Body nvarchar(max) SET @Subject = LTRIM(@SubjectWarning + SPACE(1) + @@SERVERNAME) + ': FileGroup Space Available Notification' SET @Body = 'The following database files belong to filegroups that nearly full. Please increase the size of the files to prevent unscheduled file growth. ' + ' ' + ' ' OPEN NOTIFICATION_CURS FETCH NEXT FROM NOTIFICATION_CURS INTO @DatabaseName, @FileGroup, @FileName, @FilePath, @SpaceAvailable, @SpaceUsed, @PercentUsed While @@Fetch_Status = 0 BEGIN Set @Body = @Body + ' ' FETCH NEXT FROM NOTIFICATION_CURS INTO @DatabaseName, @FileGroup, @FileName, @FilePath, @SpaceAvailable, @SpaceUsed, @PercentUsed END CLOSE NOTIFICATION_CURS DEALLOCATE NOTIFICATION_CURS Exec msdb.dbo.sp_send_dbmail @recipients = @ToAddress, @subject = @Subject, @body = @Body, @body_format = 'HTML' END GO
The parameters for the procedure are as follows:
- @PercentFull: Threshold percenteage for space used in data file
- @MBEmpty: Threshold MB value for space remaining in each data file. Prevents false-positive alerts for large databases
- @ToAddress: E-mail address to send the alert
- @SubjectWarning: Prefix to e-mail message to code or rate the alert
Getting It Taken Care Of
This alert should not be used as a replacement to planning for the future growth of your databases. Instead, the current and historical database sizes should be used to accommodate for future growth and should be reviewed at regular intervals.
Notifications of this sort are designed to let you know that unplanned growth may be possible and the historical growth rates ay no longer be valid based on the receipt of the alert. Always know where you expect the database size to be and use this notification to determine when pre-determined safety threshold have been exceeded.
Nobody wants to run out of space or beer.
Disclaimer: The procedure uses the undocumented command DBCC SHOWFILESTATS. Use this with caution. It is the same statement that SQL Server Management Studio uses to get this information. But the command can change at any time without notice.