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.
Strate SQL 