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!
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
If I need to change to 30%, where do i have to changes? Can you plz tell me by line no. above?
You make that change on lines 81 & 92. Change to this: WHERE ROUND((FreeSpaceMB*100/CurrentSizeMB), 2) < 30
If there is not enough space in the file it will grow automatically and if instant file initialization is enabled it will be almost instant. So I don’t see what is the point of keeping extra 20% for each file in every database. Seems like a waste of space for me. Especially if you have 10Tb database. Also it would be fun to restore such databases with a lot of empty space to multiple test environments.
I think this is a very nice start Lori. I have a script similar to this but I take it a step further. You Want to know how much disk space is available so I also show side by side the disk information. Total space on disk. Then allowing for disk to only go to 80% use, I find what 80% of the disk is. That is the maximum space you can use. Then that number minus space used so far tells me how much i can grow. I Get existing growth increments and divide that into the available disk space left and then just adjust the autogrowth amount to acomodate that.
I like it! We have a table that gathers daily disk space from a WMI query so that I don’t miss any drives that don’t have databases. I’ll have to blog that sometime if I haven’t already. Feel free to share code if you can. 🙂