Script to Analyze Mount Point Space & Generate Shrink File Statements

Spread the love

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.

--	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 ['[email protected]_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 '''[email protected]+''' **************************
	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)[email protected]_FreeSpaceInMB as varchar(20))+' MB = '+cast( (((@FreeSpaceThresholdInPercent*@MountPoint_SizeInMB)/100)[email protected]_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

percent-free-space

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 🙂

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.