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!
