SQL Server Performance Tuning Script – Can My Databases Grow?
Originally published on DMMaxwell.WordPress.com.
SQL Server Performance Tuning 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 = '[email protected]' GO TO DO LIST: 1. Nothing at this time. NOTES: Please send any comments or suggestions to [email protected] 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 = '[email protected]' /* 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:
About the Author
David Maxwell
UpSearch Alum David Maxwell is a database strategist, community evangelist and public speaker. He is passionate about helping community and business leaders get the most out of SQL Server.
Since 2000, David has served to protect, unlock and optimize data's value within such diverse environments as healthcare providers, insurance companies, manufacturers and financial institutions. David has the unique ability to zero in on a complex challenge quickly, and provide a long-term solution that fits both the business needs and budget.
Since 2012, David has maintained a SQL Server focused blog at https://dmmaxwell.wordpress.com/. He is a frequent presenter at local and regional events for the Professional Association for SQL Server (PASS) and and works with the Columbus, OH based PASS chapter.
In his free time, David is an avid musician who plays several instruments, as well as a lover of puzzle-based games.
Learn more about David Maxwell at https://upsearch.com/david-maxwell/.
About UpSearch
UpSearch is a company of data management and analytics experts who enable digital maturity with Microsoft’s technologies. Its mission is to enable every leader to unlock data’s full potential. UpSearch provides full lifecycle support for SQL Server, SQL Server in Azure (IaaS), Azure SQL DB (PaaS), Azure SQL DW (PaaS), Analytics Platform System (APS), and Power BI.