Script to Analyze Mount Point Space & Generate Shrink File Statements
In my organization, we often receive far too many incidents for low free disk space. As part of resolution we have to check for unexpected files/folders on the mount point drive and delete/move to other location. There are also times when we are left with no other option but to Shrink the database files present on the mount point drive. Below script serves the same purpose. Script requires only 2 inputs namely @Path and @FreeSpaceThresholdInPercent. Then it analyzes all the database files of instance falling under same mount point as of @path, and presents the general information like Total Size, Available space, free space, and more space to add to meet the threshold requirement. Also, it then generates DBCC SHRINKFILE commands for all the affected files in decreasing order of space available. It also displays running total of approx., space freed with each shrink operation.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 |
-- Created By: Ajay Dwivedi -- Purpose: Script to Find [percent_free] space and Shrink Files using loop. -- Also, not considering [sp_msForEachDB] since its is not compatible with databases with compatability of 80. -- Inputs: 2 (Mount Point & Threshold) SET NOCOUNT ON; DECLARE @Path VARCHAR(50) = 'C:\' -- Input 01: Mount Point with \ in last ,@FreeSpaceThresholdInPercent FLOAT = 20.0 -- Input 02: Threshold in % -- Declare variables for Cursor DECLARE @db_name NVARCHAR(100) ,@SQLString NVARCHAR(max); DECLARE @volume_mount_point VARCHAR(256), @MountPoint_SizeInGB FLOAT, @MountPoint_FreeSpaceInGB FLOAT, @MountPoint_PercentFreeSpace FLOAT, @MountPoint_SizeInMB FLOAT, @MountPoint_FreeSpaceInMB FLOAT -- Create table for storing free space IF OBJECT_ID('tempdb..#FileSpace') IS NOT NULL DROP TABLE #FileSpace; CREATE TABLE #FileSpace ( databaseName sysname, name sysname, physical_name varchar(max), isLogFile tinyint, File_SizeInMB float, File_FreeSpaceInMB float, volume_mount_point varchar(256), MountPoint_SizeInMB float, MountPoint_FreeSpaceInMB float, MountPoint_SizeInGB float, MountPoint_FreeSpaceInGB float, [MountPoint_PercentFreeSpace] as ((MountPoint_FreeSpaceInMB/MountPoint_SizeInMB)*100) ); DECLARE database_cursor CURSOR FOR SELECT db.name FROM sys.databases AS db WHERE db.state_desc = 'ONLINE' --AND db.name NOT IN ('master','tempdb','model','msdb') AND db.compatibility_level > 80 OPEN database_cursor FETCH NEXT FROM database_cursor INTO @DB_Name; WHILE @@FETCH_STATUS = 0 BEGIN SET @SQLString = ' USE ['+@DB_Name+']; select DB_NAME() as databaseName, f.name, f.physical_name, FILEPROPERTY(name,''IsLogFile'') as isLogFile, f.size/128.0 as File_SizeInMB, f.size/128.0 - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS int)/128.0 AS File_FreeSpaceInMB ,s.volume_mount_point, s.total_bytes/1024.0/1024.0 as MountPoint_SizeInMB, s.available_bytes/1024.0/1024.0 AS MountPoint_FreeSpaceInMB ,s.total_bytes/1024.0/1024.0/1024.0 as MountPoint_SizeInGB, s.available_bytes/1024.0/1024.0/1024.0 AS MountPoint_FreeSpaceInGB from sys.database_files f cross apply sys.dm_os_volume_stats(DB_ID(), f.file_id) s'; -- Find free space for files INSERT INTO #FileSpace (databaseName, name, physical_name, isLogFile, File_SizeInMB, File_FreeSpaceInMB, volume_mount_point, MountPoint_SizeInMB, MountPoint_FreeSpaceInMB, MountPoint_SizeInGB, MountPoint_FreeSpaceInGB) EXEC (@SQLString); FETCH NEXT FROM database_cursor INTO @DB_Name; END CLOSE database_cursor DEALLOCATE database_cursor SELECT @volume_mount_point=s.volume_mount_point, @MountPoint_SizeInGB=s.MountPoint_SizeInGB, @MountPoint_FreeSpaceInGB=s.MountPoint_FreeSpaceInGB, @MountPoint_PercentFreeSpace=s.MountPoint_PercentFreeSpace, @MountPoint_SizeInMB=S.MountPoint_SizeInMB, @MountPoint_FreeSpaceInMB=S.MountPoint_FreeSpaceInMB FROM #FileSpace s WHERE s.volume_mount_point LIKE @Path+'%' GROUP BY s.volume_mount_point, s.MountPoint_SizeInGB, s.MountPoint_FreeSpaceInGB, s.MountPoint_PercentFreeSpace ,S.MountPoint_SizeInMB, S.MountPoint_FreeSpaceInMB PRINT '/* **************** Analyzing Mount Point for path '''+@Path+''' ************************** Total Size = '+cast(@MountPoint_SizeInMB as varchar(20))+ ' MB = '+cast(@MountPoint_SizeInGB as varchar(20))+ ' GB Available Space = '+cast(@MountPoint_FreeSpaceInMB as varchar(20))+ ' MB = '+cast(@MountPoint_FreeSpaceInGB as varchar(20))+ ' GB % Free Space = '+cast(@MountPoint_PercentFreeSpace as varchar(20))+ ' Space to Add ('+cast(@FreeSpaceThresholdInPercent as varchar(10))+'% threshold) = '+ (case when ((@FreeSpaceThresholdInPercent*@MountPoint_SizeInMB)/100)>@MountPoint_FreeSpaceInMB then cast(((@FreeSpaceThresholdInPercent*@MountPoint_SizeInMB)/100)-@MountPoint_FreeSpaceInMB as varchar(20))+' MB = '+cast( (((@FreeSpaceThresholdInPercent*@MountPoint_SizeInMB)/100)-@MountPoint_FreeSpaceInMB)/1024 as varchar(20))+' GB' else '_____Mount Point has sufficient space_____' end)+' NOTE: Files would not shrink below initial size. So below values are only estimation. Please re-run the script to refresh this result each time. Also, databases of compatibility of 80 are not supported by this script. */ '; -- Create intermediate table for Running Total feature (as UNBOUND PRECEEDING is not supported below SQL 2012) IF OBJECT_ID('tempdb..#FileSpace_Final') IS NOT NULL DROP TABLE #FileSpace_Final SELECT * ,RowID = ROW_NUMBER()OVER(ORDER BY File_FreeSpaceInMB DESC) INTO #FileSpace_Final FROM #FileSpace as s WHERE s.volume_mount_point LIKE @Path+'%' AND File_FreeSpaceInMB > 1 ORDER BY File_FreeSpaceInMB DESC; -- Display Shrink command SELECT ' USE ['+databaseName+'] GO DBCC SHRINKFILE (N'''+name+''' , '+cast(convert(numeric,(File_SizeInMB-File_FreeSpaceInMB+1) ) as varchar(50))+') GO -- Space freed on ['+databaseName+'] = '+cast(File_FreeSpaceInMB-1 as varchar(50))+' MB -- Total Space freed = '+CAST( (SELECT SUM(s1.File_FreeSpaceInMB-1) FROM #FileSpace_Final as s1 WHERE s1.RowID <= s.RowID) AS VARCHAR(20))+' MB ' --,[Total Space freed] = CAST( (SELECT SUM(s1.File_FreeSpaceInMB-1) FROM #FileSpace_Final as s1 WHERE s1.RowID <= s.RowID) AS VARCHAR(20)) FROM #FileSpace_Final as s ORDER BY File_FreeSpaceInMB DESC; GO |
Friends, please feel free to correct me by comments. Also, comment if you find the script useful. Kindly suggest any suggestion that you feel could make the script better and useful. If you like the article, do Like & Share. Happy Coding 🙂
Related Posts
-
Error/Fix: Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation
-
SQL Server – Get Database Backup History for Crash Recovery
-
SQL Server – Doing Index Maintenance when HADR solutions are in Place
-
Non – Trusted Foreign Keys in SQL Server
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.