Script: Can My Databases Grow?

Most of us try to proactively manage our available disk space as much as we can, but sometimes you’re not able to predict when a file will grow. We usually leave auto-grow on as a safeguard for this. One of the problems I’ve run into in the past is. having a very large growth setting or lack of disk space create a situation where there isn’t enough space on disk for a file to grow. There may be enough “free space” on the disk for whatever your monitoring threshold is, but maybe not enough for a growth or two.

Here’s a stored procedure you can use to check to see if any of the databases on your server would fail to auto-grow. It requires dbmail be set up. Also note that for this, and most scripts I will usually post, I have a separate administrative database that I put this in. Let me know if you have any feedback on this, or if you get any use out of it.

Thanks for reading.

-David.
USE [SQLAdmin];
GO

CREATE PROCEDURE AutogrowWarning
/*******************************************************************************
AUTHOR:
David M Maxwell

DATE:
Apr 24 2012

DESCRIPTION:
Checks to see if sufficient disk space is available for autogrow. If not, sends a warning.

DEPENDENCIES:
None

TEST EXEC:

EXEC dbo.AutogrowWarning
@dbaemail = 'david.maxwell@companyname.com'
GO

TO DO LIST:
1. Nothing at this time.

NOTES:
Please send any comments or suggestions to david.maxwell@companyname.com.

CHANGE HISTORY:
Date - Author - Change
Apr 24 2012 - David M Maxwell - Initial Version

*******************************************************************************/
@dbaemail varchar(500) = NULL -- DBA email address to receive warnings.
AS

SET NOCOUNT ON;

/* Test Parameters.  Run everything from here down to test. */
--DECLARE @dbaemail varchar(500), @msgquery nvarchar(4000)
--SELECT
--  @dbaemail = 'david.maxwell@companyname.com'

/* Parameter check */
IF (SELECT @dbaemail) IS NULL
BEGIN
RAISERROR
('ERROR: Email recipient address must be provided.',16,1)
--GOTO EXITPROC
END

/* Create temp tables for comparison. */
IF (SELECT object_id('tempdb.dbo.#DiskFree')) IS NOT NULL
DROP TABLE #DiskFree

CREATE TABLE #DiskFree (
DriveLetter char(1) NOT NULL,
FreePages int NOT NULL
);

IF (SELECT object_id('tempdb.dbo.#NextAutoGrow')) IS NOT NULL
DROP TABLE #NextAutoGrow

CREATE TABLE #NextAutoGrow (
DBName varchar(100) NOT NULL,
LogicalName varchar(100) NOT NULL,
FileType CHAR(10) NOT NULL,
DriveLetter char(1) NOT NULL,
Growth int NOT NULL
);

IF (SELECT object_id('tempdb.dbo.#AutoGrowFail')) IS NOT NULL
DROP TABLE #AutoGrowFail

CREATE TABLE #AutoGrowFail (
DBName varchar(100) NOT NULL,
LogicalName varchar(100) NOT NULL,
FileType CHAR(10) NOT NULL,
DriveLetter char(1) NOT NULL,
Growth int NOT NULL,
FreePages int NOT NULL
);

/* Create perm table, if it does not exist. */

IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'AutoGrowWarnings')
BEGIN
CREATE TABLE
AutoGrowWarnings (
WarningMessage nvarchar(max)
)
END

TRUNCATE TABLE dbo.AutoGrowWarnings;

/* Get the amount of disk space free on each volume. */
INSERT INTO #DiskFree
EXEC xp_fixeddrives;

/* Convert to pages for easier comparison, since master_files.size
is in pages. */
UPDATE #DiskFree
SET FreePages = FreePages * 128;

/* Get the next autogrow for each DB per drive letter. */
INSERT INTO #NextAutoGrow
SELECT
db_name(database_id),
name,
type_desc,
substring(physical_name,1,1),
CASE is_percent_growth
WHEN 1 THEN (size / growth)
ELSE growth
END AS Growth
FROM master.sys.master_files
WHERE Growth > 0

/* Compare the next autogrow to the disk free and create
a list of files that autogrow would fail on, due to
insufficient disk space. */
INSERT INTO #AutoGrowFail
SELECT
NAG.DBName,
NAG.LogicalName,
NAG.FileType,
NAG.DriveLetter,
NAG.Growth,
DF.FreePages
FROM #NextAutoGrow NAG
INNER JOIN #DiskFree DF
ON NAG.DriveLetter = DF.DriveLetter
WHERE NAG.Growth > DF.FreePages;

/* If the list is not empty, email it to the DBA. */
IF (SELECT COUNT(*) FROM #AutoGrowFail) > 0
BEGIN

INSERT INTO dbo.AutoGrowWarnings
SELECT 'WARNING: As of ' + CONVERT(varchar(24),GETDATE(),120) + ',' + DBName + '.'
+ LogicalName + ' requires ' + CONVERT(nvarchar(24),growth)
+
' pages of free space for autogrow and only ' + CONVERT(nvarchar(24),FreePages)
+
' are available on drive ' + DriveLetter + '.'
FROM #AutoGrowFail

EXEC msdb.dbo.sp_send_dbmail
@recipients = @dbaemail,
@subject = 'WARNING: Possible Auto-Grow Failure',
@query = 'SELECT * FROM SQLAdmin.dbo.AutoGrowWarnings'
END

EXITPROC:

Advertisements

Please Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s