Find All Databases with Less Than 20 Percent Free Space and Grow Them

This is kind of a continuation of one of my previous posts.  (https://www.sqlrx.com/find-and-modify-file-growth-settings-for-all-databases/ ) Thousands of databases in a SQL instance can make a DBA’s life a bit challenging.  Automation suddenly becomes very important. 

My new client had been shrinking their databases frequently, mistakenly thinking that doing so was a good thing.  I needed to check all of the data files for all databases to see how much free space they had and how much space needed to be added to each file to grow each database to have 20% free space available.  “Why should you have at least 20% free space available in your database?”, you might ask.  In this case less is not more.  According to Microsoft:

Found at: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15   Your data files need free space to perform index maintenance efficiently.  I have gone with a rule of thumb to try to have about 20% free space in files so that SQL and ultimately the users stay happy.

Again….my client has over a thousand databases of varying sizes.  The sizes are not huge but they do vary.  My challenge is to figure out the current size of each file, how much free space each file has, and how much space do I need to add to each file to have 20% free space.

The info I need is found in sys.database_files which can be used along with the FILEPROPERTY function to get size, space used and free space.   https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-files-transact-sql?view=sql-server-ver15 , https://docs.microsoft.com/en-us/sql/t-sql/functions/fileproperty-transact-sql?view=sql-server-ver15  Since sys.database_files is a per-database view, I will have to query each database using dynamic SQL and put that info into a temporary table so that I can calculate how much space needs to be added.  I also wanted to have an ALTER DATABASE statement created for me so that I did not have to try to type out all the changes.

Here is my final code:

/* Finds all db files with less than 20% free space and create statements to grow
to an appropriate size.
*/

SET NOCOUNT ON
DECLARE @dbname NVARCHAR(200)
DECLARE @SQLStr NVARCHAR(4000)
DECLARE @filetype VARCHAR(10)
DECLARE @filesizeMB INT
DECLARE @logicalname NVARCHAR(200)
DECLARE @queryonly BIT

SET @filetype = 'DATA'  -- Use DATA for data files, Use LOG for transaction log files
SET @queryonly = 0      -- 0 = generate ALTER DATABASE statements, 1 = just run query to show free space data 

IF OBJECT_ID('tempdb..#DBName') IS NOT NULL
		DROP TABLE #DBName;
CREATE TABLE #DBName (Name NVARCHAR(200))

IF OBJECT_ID('tempdb..##FileStats') IS NOT NULL
		DROP TABLE ##FileStats;
CREATE TABLE ##FileStats (DBName NVARCHAR(200), 
FileType NVARCHAR(100), [FileName] NVARCHAR(100), 
CurrentSizeMB FLOAT, FreeSpaceMB FLOAT, 
PercentMBFree FLOAT, FileLocation NVARCHAR(2000))

IF OBJECT_ID('tempdb..#FilesToGrow') IS NOT NULL
		DROP TABLE #FilesToGrow;
CREATE TABLE #FilesToGrow (FileShouldBeThisSizeMB INT, DBName NVARCHAR(200), [FileName] NVARCHAR(200))

INSERT INTO #DBName 
SELECT sd.name 
FROM sys.databases sd
WHERE sd.name NOT IN ('master','model','msdb','tempdb')
AND sd.name NOT IN (SELECT d.name
						FROM sys.databases d
						INNER JOIN sys.availability_databases_cluster adc ON d.group_database_id = adc.group_database_id
						INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
						INNER JOIN sys.dm_hadr_availability_replica_states rs ON ag.group_id = rs.group_id AND d.replica_id = rs.replica_id
						WHERE rs.role_desc = 'SECONDARY') -- exclude dbs in AG secondary
AND sd.is_read_only = 0 AND sd.state = 0 -- exclude dbs that are read only or offline

DECLARE DBCur CURSOR FOR 
SELECT Name FROM #DBName

OPEN DBCur
		
WHILE (1=1)
BEGIN
	FETCH NEXT FROM DBCur INTO @dbname
	IF @@FETCH_STATUS < 0 BREAK
 
	SET @SQLStr = 'USE [' + @dbname + ']
	INSERT INTO ##FileStats
	SELECT DB_NAME() AS DbName, 
	CASE WHEN type = 0 THEN ''DATA'' ELSE ''LOG'' END AS FileType,
	name AS FileName, 
	size/128.0 AS CurrentSizeMB,  
	size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB,
	100*(1 - ((CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0)/(size/128.0))) AS PercentMBFree,
	physical_name AS FileLocation
	FROM sys.database_files'
 
	EXEC sp_executesql @SQLStr
END
-- Close and deallocate the cursor
CLOSE DBCur
DEALLOCATE DBCur

IF @queryonly = 1
	SELECT DBName,
	FileType,
	FileName,
	ROUND(CurrentSizeMB, 2) AS CurrentSizeMB,
	ROUND(FreeSpaceMB, 2) AS FreeSpaceMB,
	ROUND((FreeSpaceMB*100/CurrentSizeMB),2) AS PercentFree, 
	ROUND(((CurrentSizeMB/.8)-(CurrentSizeMB)),2) AS MBNeededToMakeFileHave20PctFreeSpace,
	ROUND(((CurrentSizeMB/.8)-(CurrentSizeMB))+(CurrentSizeMB)+5,0) AS FileShouldBeThisSizeMB,  -- pad it by 5MB and round to a whole number
	FileLocation
	FROM ##FileStats
	WHERE ROUND((FreeSpaceMB*100/CurrentSizeMB), 2) < 20
	AND FileType = @filetype
	ORDER BY DBName

IF @queryonly = 0
BEGIN
	INSERT INTO #FilesToGrow (FileShouldBeThisSizeMB, DBName, [FileName])
	SELECT 
	ROUND(((CurrentSizeMB/.8)-(CurrentSizeMB))+(CurrentSizeMB)+5,0) AS FileShouldBeThisSizeMB,  -- pad it by 5MB and round to a whole number
	DBName, [FileName]
	FROM ##FileStats
	WHERE ROUND((FreeSpaceMB*100/CurrentSizeMB), 2) < 20  -- file has less than 20% free space
	AND FileType = @filetype
	ORDER BY DBName

	DECLARE DBFileGrow CURSOR FOR 
	SELECT FileShouldBeThisSizeMB, DBName, [FileName] FROM #FilesToGrow
	ORDER BY DBName

	OPEN DBFileGrow
		
	WHILE (1=1)
	BEGIN
		FETCH NEXT FROM DBFileGrow INTO @filesizeMB, @dbname, @logicalname
		IF @@FETCH_STATUS < 0 BREAK

		 SET @SQLStr = 'ALTER DATABASE [' + @dbname + '] MODIFY FILE (NAME = N'''+@logicalname+''', SIZE = '+CAST(@filesizeMB AS NVARCHAR(10))+'MB)' 
		 --EXEC (@SQLStr)
		 PRINT @SQLStr

	END
	-- Close and deallocate the cursor
	CLOSE DBFileGrow
	DEALLOCATE DBFileGrow
END
-- Clean up
DROP TABLE #DBName
DROP TABLE ##FileStats
DROP TABLE #FilesToGrow

There are 2 parameters at the top of the script.  Set @filetype to query either DATA or LOG files and use @queryonly to have the file space information displayed instead of generating the ALTER DATABASE statements. 

I chose to look at data and log files separately because I felt that log files may not need to be grown if there is less than 20% space free in it.  If there is not much free space in the log file, then it really might be best to back it or the database up to free space in it.  That is why I felt that log files need to be handled a bit differently than the data file so I queried them separately.

I also thought that I needed to check the space needed for all files so that I would be able to take that information to Excel so that I can sort by drive and SUM up the total space needed and could then make sure that my drives had enough space free for the files to grow into.  When you set @queryonly = 1, this is the result.

You will also notice that in my calculation for the final file size, I added 5MB.  I did that just to give each file a tiny bit more space than exactly 20% free.  You can modify or remove that if you want.  From experience, it seems like the right thing to do to me.

Just like my previous post, I have the statements being printed for now but I put in an EXEC command that can be uncommented to allow the ALTER DATABASE statements to run.

Hope this helps someone else.  I know it helped me a lot!

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!