Create/Restrict Data Files – Automation

Spread the love

In my organization, as part of our strategy we keep Data files in 2 TB sized Mount Point Volumes named like ‘Data’,’Data01′,’Data02′ etc. on E:\ drive. Similary, for Log files also, we have 2 TB sized Mount Point Volumes named like ‘Logs’,’Logs01′,’Logs02′ etc. on E:\ drive. Now, it is quite often that mount point volumes for data or log files get full, and we need to add new mount point volume. Let’s just say that our server already has ‘E:\Data’, ‘E:\Data1’, ‘E:\Data02’ data volumes. Now, ‘E:\Data02’ volume is filled above threshold value of 80%. So, we need to add another mount point volume ‘E:\Data03’ for data files. Also, need to add data files in @newVolume (E:\Data03) for each combination of Db and FileGroup +nt on @oldVolume (E:\Data2). Once files added on @newVolume, we need to restrict growth of all data files on @oldVolume. Then, we have to shrink the size of files in @oldVolume in such a way so that they do not take more than threshold value of space(upto 79%). This kind of activity becomes very irritating when the no of databases gets considerably large, say more than 50.

I have created below script for such kind of activities only.

USE tempdb
GO
IF OBJECT_ID('dbo.usp_AnalyzeSpaceCapacity') IS NULL
  EXEC ('CREATE PROCEDURE dbo.usp_AnalyzeSpaceCapacity AS RETURN 0;')
GO

ALTER PROCEDURE [dbo].[usp_AnalyzeSpaceCapacity]
	@getInfo TINYINT = 0, @getLogInfo TINYINT = 0, @help TINYINT = 0, @addDataFiles TINYINT = 0, @addLogFiles TINYINT = 0, @restrictDataFileGrowth TINYINT = 0, @restrictLogFileGrowth TINYINT = 0, @generateCapacityException TINYINT = 0, @unrestrictFileGrowth TINYINT = 0, @removeCapacityException TINYINT = 0, @UpdateMountPointSecurity TINYINT = 0, @restrictMountPointGrowth TINYINT = 0,
	@newVolume VARCHAR(50) = NULL, @oldVolume VARCHAR(50) = NULL, @smallDB_InititalSize_MB BIGINT = '256', @smallDB_InititalLogSize_MB BIGINT = '257', @smallDB_InititalSize_Percent TINYINT = 10, @smallDBSize DECIMAL(20,2) = 2.0,
	@largeDB_InititalSize_MB BIGINT = '1024', @largeDB_InititalLogSize_MB BIGINT = '256', @largeDB_AutoGrowth_MB BIGINT = '1024', @smallDB_LogAutoGrowth_MB BIGINT = '256', @largeDB_LogAutoGrowth_MB BIGINT = '256', @mountPointGrowthRestrictionPercent TINYINT = 79
AS
BEGIN
/*
	Created By:		Ajay Dwivedi
	Updated on:		16-Apr-2017
	Purpose:		This procedure can be used to generate automatic TSQL code for working with incidents like 'DBServer2339 - Data- Create and Restrict Database File Names' type.
*/
SET NOCOUNT ON;

DECLARE @_powershellCMD VARCHAR(400);
DECLARE	@_addFileSQLText VARCHAR(MAX) = ''
		,@_isServerPartOfMirroring TINYINT = 1
		,@_mirroringPartner VARCHAR(50)
		,@_principalDatabaseCounts_Mirroring SMALLINT = 0
		,@_mirrorDatabaseCounts_Mirroring SMALLINT = 0
		,@_nonAccessibleDatabasesCounts SMALLINT
		,@_nonAccessibleDatabases VARCHAR(MAX)
		,@_mirrorDatabases VARCHAR(MAX) = NULL
		,@_principalDatabases VARCHAR(MAX) = NULL
		,@_nonAddedDataFilesDatabases VARCHAR(MAX) = NULL
		,@_nonAddedDataFilesDatabasesCounts SMALLINT = 0
		,@_nonAddedLogFilesDatabases VARCHAR(MAX) = NULL
		,@_nonAddedLogFilesDatabasesCounts SMALLINT = 0
		,@_databasesWithMultipleDataFiles VARCHAR(MAX) = NULL
		,@_databasesWithMultipleDataFilesCounts SMALLINT = 0
		,@_totalSpace_OldVolume_GB DECIMAL(20,2)
		,@_freeSpace_OldVolume_Percent TINYINT
		,@_freeSpace_OldVolume_GB DECIMAL(20,2)
		,@_errorMSG VARCHAR(200)
		,@_loopCounter SMALLINT = 0
		,@_loopCounts SMALLINT = 0
		,@_loopSQLText VARCHAR(MAX)
		,@_capacityExceptionSQLText VARCHAR(MAX)
		,@_svrName VARCHAR(255) = @@SERVERNAME
		,@_sqlGetMountPointVolumes VARCHAR(400)
		,@_sqlGetInfo VARCHAR(2000)
		,@_commaSeparatedMountPointVolumes VARCHAR(1000)
		,@_DBName VARCHAR(200)
		,@_LogOrData VARCHAR(5)
		,@_Total_Files_Size_MB DECIMAL(20,2)
		,@_Space_That_Can_Be_Freed_MB DECIMAL(20,2)
		,@_SpaceToBeFreed_MB DECIMAL(20,2);

--creating a temporary table
DECLARE @output TABLE (line varchar(255));
DECLARE @mountPointVolumes TABLE ( Volume VARCHAR(200), [capacity(MB)] DECIMAL(20,2), [freespace(MB)] DECIMAL(20,2) ,VolumeName VARCHAR(50), [capacity(GB)]  DECIMAL(20,2), [freespace(GB)]  DECIMAL(20,2), [freespace(%)]  DECIMAL(20,2) );
DECLARE @T_Files_Final_Restrict TABLE (ID INT IDENTITY(1,1), TSQL_RestrictFileGrowth VARCHAR(2000));
DECLARE @DBFiles TABLE
(
	[DbName] [varchar](500),
	[FileName] [varchar](500),
	[physical_name] varchar(1000),
	[CurrentSizeMB] [numeric](17, 6),
	[FreeSpaceMB] [numeric](18, 6),
	[SpaceUsed] [numeric] (18,6),
	[type_desc] [varchar](60),
	[growth] [int],
	[is_percent_growth] [bit]
);

IF OBJECT_ID('tempdb..#T_Files_Derived') IS NOT NULL
	DROP TABLE #T_Files_Derived;
CREATE TABLE #T_Files_Derived
(
	[dbName] [nvarchar](128) NULL,
	[database_id] [int] NULL,
	[file_id] [int] NULL,
	[type_desc] [nvarchar](60) NULL,
	[data_space_id] [int] NULL,
	[name] [sysname] NULL,
	[physical_name] [nvarchar](260) NULL,
	[size] [int] NULL,
	[max_size] [int] NULL,
	[growth] [int] NULL,
	[is_percent_growth] [bit] NULL,
	[fileGroup] [sysname] NULL,
	[FileIDRankPerFileGroup] [bigint] NULL,
	[isExistingOn_NewVolume] [int] NULL,
	[isExisting_UnrestrictedGrowth_on_OtherVolume] [int] NULL,
	[Category] [varchar](10) NULL,
	[Size (GB)] [decimal](20, 2) NULL,
	[_name] [nvarchar](4000) NULL,
	[_physical_name] [nvarchar](4000) NULL,
	[TSQL_AddFile] [varchar](2000) NULL,
	[TSQL_RestrictFileGrowth] [varchar](2000) NULL,
	[TSQL_UnRestrictFileGrowth] [varchar](2000) NULL
);

--	============================================================================
	--	Begin:	Validations 
--	============================================================================
	IF (@addDataFiles=1 OR @restrictDataFileGrowth=1 OR @getInfo=1)
		SET @_LogOrData = 'Data';
	ELSE
		SET @_LogOrData = 'Log';

	IF	(@help=1 OR @addDataFiles=1 OR @addLogFiles=1 OR @restrictDataFileGrowth=1 OR @restrictLogFileGrowth=1 OR @generateCapacityException=1 OR @unrestrictFileGrowth=1 OR @removeCapacityException=1 OR @UpdateMountPointSecurity=1 OR @restrictMountPointGrowth=1)
	BEGIN	
		SET	@getInfo = 0;
		SET @getLogInfo = 0;
	END
	ELSE 
	BEGIN
		IF (@getLogInfo=0)
			SET	@getInfo = 1;
	END

	IF (COALESCE(@getInfo,@help,@addDataFiles,@addLogFiles,@restrictDataFileGrowth,@restrictLogFileGrowth,@generateCapacityException,@unrestrictFileGrowth,@smallDB_InititalSize_MB,@largeDB_InititalSize_MB,@largeDB_AutoGrowth_MB,@smallDBSize,@restrictMountPointGrowth,-999) = -999)
	BEGIN
		SET @_errorMSG = 'Procedure does not accept NULL for parameter values.';
		IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12
			EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;
		ELSE
			EXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;
	END

	IF (@help = 1 AND (@addDataFiles=1 OR @addLogFiles=1 OR @restrictDataFileGrowth=1 OR @restrictLogFileGrowth=1 OR @generateCapacityException=1 OR @unrestrictFileGrowth=1 OR @removeCapacityException=1))
	BEGIN
		SET @_errorMSG = '@help=1 is incompatible with any other parameters.';
		IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12
			EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;
		ELSE
			EXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;
	END

	IF (@generateCapacityException = 1 AND (@addDataFiles=1 OR @restrictDataFileGrowth=1 OR @unrestrictFileGrowth=1 OR @help=1 OR @removeCapacityException=1))
	BEGIN
		SET @_errorMSG = '@generateCapacityException=1 is incompatible with any other parameters.';
		IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12
			EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;
		ELSE
			EXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;
	END

	IF (@unrestrictFileGrowth = 1 AND (@addDataFiles=1 OR @restrictDataFileGrowth=1 OR @generateCapacityException=1 OR @help=1 OR @removeCapacityException=1))
	BEGIN
		SET @_errorMSG = '@unrestrictFileGrowth=1 is incompatible with any other parameters.';
		IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12
			EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;
		ELSE
			EXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;
	END

	IF (@removeCapacityException = 1 AND (@addDataFiles=1 OR @restrictDataFileGrowth=1 OR @generateCapacityException=1 OR @help=1 OR @unrestrictFileGrowth=1))
	BEGIN
		SET @_errorMSG = '@removeCapacityException=1 is incompatible with any other parameters.';
		IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12
			EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;
		ELSE
			EXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;
	END

	IF ( (@addDataFiles=1 OR @addLogFiles=1) AND (@newVolume IS NULL OR @oldVolume IS NULL))
	BEGIN
		SET @_errorMSG = '@oldVolume & @newVolume parameters must be specified with '+(CASE WHEN @addDataFiles=1 THEN '@addDataFiles' ELSE '@addLogFiles' END)+' = 1 parameter.';
		IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12
			EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;
		ELSE
			EXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;
	END

	IF ( (@restrictDataFileGrowth=1 OR @restrictLogFileGrowth=1 OR @restrictMountPointGrowth=1) AND (@oldVolume IS NULL))
	BEGIN
		SET @_errorMSG = '@oldVolume parameters must be specified with '+(CASE WHEN @restrictDataFileGrowth=1 THEN '@restrictDataFileGrowth' WHEN @restrictLogFileGrowth=1 THEN '@restrictLogFileGrowth' ELSE '@restrictMountPointGrowth' END)+' = 1 parameter.';
		IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12
			EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;
		ELSE
			EXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;
	END

	--	Validation on @oldVolume & @newVolume for size is in Common Code
--	============================================================================
	--	End:	Validations 
--	============================================================================

--	============================================================================
	--	Begin:	Common Code 
--	----------------------------------------------------------------------------
	--	Begin: Get Data & Log Mount Point Volumes
	SET @_powershellCMD =  'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@@servername,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"';

	-- Clear previous output
	DELETE @output;

	--inserting disk name, total space and free space value in to temporary table
	INSERT @output
	EXEC xp_cmdshell @_powershellCMD;

	;WITH T_Volumes AS
	(
		SELECT	RTRIM(LTRIM(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as Volume
				,ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('|',line)+1,
				(CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'
				,ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('%',line)+1,
				(CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'
		FROM	@output
		WHERE line like '[A-Z][:]%'
	)
	INSERT INTO @mountPointVolumes
	(Volume, [capacity(MB)], [freespace(MB)] ,VolumeName, [capacity(GB)], [freespace(GB)], [freespace(%)])
	SELECT	Volume
			,[capacity(MB)]
			,[freespace(MB)]
			,REVERSE(SUBSTRING(REVERSE(v.Volume),2,CHARINDEX('\',REVERSE(v.Volume),2)-2)) as VolumeName
			,CAST(([capacity(MB)]/1024.0) AS DECIMAL(20,2)) AS [capacity(GB)]
			,CAST(([freespace(MB)]/1024.0) AS DECIMAL(20,2)) AS [freespace(GB)]
			,CAST(([freespace(MB)]*100.0)/[capacity(MB)] AS DECIMAL(20,2)) AS [freespace(%)]
	FROM	T_Volumes v
	WHERE	v.Volume LIKE '[A-Z]:\Data\'
		OR	v.Volume LIKE '[A-Z]:\Data[0-9]\'
		OR	v.Volume LIKE '[A-Z]:\Data[0-9][0-9]\'
		OR	v.Volume LIKE '[A-Z]:\Logs\'
		OR	v.Volume LIKE '[A-Z]:\Logs[0-9]\'
		OR	v.Volume LIKE '[A-Z]:\Logs[0-9][0-9]\';

	--	End: Get Data & Log Mount Point Volumes
	------------------------------------------------------------------------------------------------

	--	Add '\' Backslash at the end for @oldVolume & @newVolume
	SELECT	@oldVolume = CASE WHEN RIGHT(RTRIM(LTRIM(@oldVolume)),1) <> '\' THEN @oldVolume+'\' ELSE @oldVolume END,
			@newVolume = CASE WHEN RIGHT(RTRIM(LTRIM(@newVolume)),1) <> '\' THEN @newVolume+'\' ELSE @newVolume END;

	IF NOT EXISTS (SELECT * FROM @mountPointVolumes v WHERE v.Volume = @newVolume AND v.[freespace(%)] >= 20) AND (@addDataFiles=1 OR @addLogFiles=1) 
	BEGIN
		SET @_errorMSG = 'Available free space on @newVolume='+QUOTENAME(@newVolume,'''')+' is less than 20 percent.';
		IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12
			EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;
		ELSE
			EXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;
	END

	------------------------------------------------------------------------------------------------
	--	Begin: Get All Databases with size information
	DECLARE	@DatabasesBySize TABLE 
	(DBName SYSNAME, database_id SMALLINT, [Size (GB)] DECIMAL(20,2), Category VARCHAR(10));

	SET @_mirroringPartner = (SELECT TOP 1 mirroring_partner_instance FROM sys.database_mirroring WHERE mirroring_state IS NOT NULL);

	INSERT @DatabasesBySize
		SELECT	DBName, database_id, [Size (GB)], (CASE WHEN [Size (GB)] <= @smallDBSize THEN 'Small' ELSE 'Large' END) as Category
		FROM (	
				SELECT	db_name(database_id) as DBName, database_id, CONVERT(DECIMAL(20,2),((SUM(size)*8.0)/1024/1024)) AS [Size (GB)]
				FROM	master.sys.master_files as f
				GROUP BY db_name(database_id), database_id
			 ) AS d;
	--	End: Get All Databases with size information
	------------------------------------------------------------------------------------------------

	--	============================================================================================
	--	Begin: Find Data/Log files on @oldVolume
	--	------------------------------------------------------------------------
	IF (@_LogOrData = 'Log')
	BEGIN
		--	Find Log files on @oldVolume. [isLogExistingOn_NewVolume] column indicates if the same files exists on @newVolume.
		;WITH T_Files AS 
		(		
			--	Find Log files on @oldVolume
			SELECT	DB_NAME(database_id) as dbName, mf1.*, NULL as [fileGroup]
						-- Consider adding single file per filegroup for each database
					,[FileIDRankPerFileGroup] = row_number()over(partition by mf1.database_id order by mf1.file_id)
						-- Check if corresponding Data file for same FileGroup exists on @newVolume
					,[isExistingOn_NewVolume] = CASE WHEN NOT EXISTS (
																		SELECT	mf2.*, NULL as [fileGroup]
																		FROM	sys.master_files mf2
																		WHERE	mf2.type_desc = mf1.type_desc
																			AND	mf2.database_id = mf1.database_id
																			AND mf2.physical_name like (@newVolume+'%')
																	)
													THEN 0
													ELSE 1
													END
					,[isExisting_UnrestrictedGrowth_on_OtherVolume] = CASE WHEN NOT EXISTS (
																		SELECT	mf2.*, NULL as [fileGroup]
																		FROM	sys.master_files mf2
																		WHERE	mf2.type_desc = mf1.type_desc
																			AND	mf2.database_id = mf1.database_id
																			AND mf2.growth <> 0
																			AND LEFT(mf2.physical_name, CHARINDEX('\',mf2.physical_name,4)) IN (select V.Volume from @mountPointVolumes V WHERE V.Volume <> @oldVolume AND [freespace(%)] >= 20.0)
																	)
													THEN 0
													ELSE 1
													END
			FROM	sys.master_files mf1
			WHERE	mf1.type_desc = 'LOG'
				AND	mf1.physical_name LIKE (@oldVolume+'%')
		)
			INSERT #T_Files_Derived
			(	dbName, database_id, file_id, type_desc, data_space_id, name, physical_name, size, max_size, growth, is_percent_growth, fileGroup, 
				FileIDRankPerFileGroup, isExistingOn_NewVolume, isExisting_UnrestrictedGrowth_on_OtherVolume, Category, [Size (GB)], _name, _physical_name, TSQL_AddFile, TSQL_RestrictFileGrowth, TSQL_UnRestrictFileGrowth
			)
			SELECT	f.dbName, f.database_id, f.file_id, f.type_desc, f.data_space_id, f.name, f.physical_name, f.size, f.max_size, f.growth, f.is_percent_growth, f.fileGroup, 
					f.FileIDRankPerFileGroup, f.isExistingOn_NewVolume, f.isExisting_UnrestrictedGrowth_on_OtherVolume ,d.Category, d.[Size (GB)]
					,mf.[_name]
					,[_physical_name] = @newVolume+[_name]+'.ldf'
					,[TSQL_AddFile] = CAST(NULL AS VARCHAR(2000))
					,[TSQL_RestrictFileGrowth] = CAST(NULL AS VARCHAR(2000))
					,[TSQL_UnRestrictFileGrowth] = CAST(NULL AS VARCHAR(2000))
			FROM	T_Files as f
			LEFT JOIN
					@DatabasesBySize	AS d
				ON	d.database_id = f.database_id
			LEFT JOIN
				(	SELECT	database_id, DBName, type_desc, name, _name = (CASE WHEN CHARINDEX(DBName,name) <> 0 THEN DBName ELSE '' END)+_Name_Without_DBName
					FROM	(
								SELECT	database_id, DBName, type_desc, name, FileNO_String, FileNO_Int, Name_Without_DBName, FileOrder, 
										[_Name_Without_DBName] = (CASE WHEN LEN( [_Name_Without_DBName]) > 0 THEN [_Name_Without_DBName] ELSE (CASE WHEN type_desc = 'ROWS' THEN '_Data01' ELSE '_Log01' END) END)
								FROM	(
											SELECT	*
													,ROW_NUMBER()OVER(PARTITION BY DBName, type_desc ORDER BY FileNO_Int DESC) as FileOrder
													,[_Name_Without_DBName] = CASE WHEN LEN(FileNO_String)<>0 THEN REPLACE(Name_Without_DBName,FileNO_String,(CASE WHEN LEN(FileNO_Int+1) = 1 THEN ('0'+CAST((FileNO_Int+1) AS VARCHAR(20))) ELSE CAST((FileNO_Int+1) AS VARCHAR(20)) END )) ELSE Name_Without_DBName + (CASE WHEN type_desc = 'LOG' THEN '01' ELSE '_data01' END) END
											FROM	(
														SELECT mf.database_id, db_name(database_id) AS DBName, type_desc, name 
																,FileNO_String = RIGHT(name,PATINDEX('%[a-zA-Z_ ]%',REVERSE(name))-1)
																,FileNO_Int = CAST(RIGHT(name,PATINDEX('%[a-zA-Z_ ]%',REVERSE(name))-1) AS INT)
																,Name_Without_DBName = REPLACE ( name, db_name(database_id), '')
														FROM sys.master_files as mf
													) AS T_Files_01
										) AS T_Files_02
							) AS T_Files_03
					WHERE	FileOrder = 1
				)  AS mf
				ON	mf.database_id = f.database_id
				AND	mf.type_desc = f.type_desc;
	END
	ELSE
	BEGIN
		--	Find Data files on @oldVolume. [isExistingOn_NewVolume] column indicates if the same files exists on @newVolume.
		;WITH T_Files AS 
		(		
			--	Find Data files on @oldVolume
			SELECT	DB_NAME(database_id) as dbName, mf1.*, fg1.name as [fileGroup]
						-- Consider adding single file per filegroup for each database
					,[FileIDRankPerFileGroup] = row_number()over(partition by mf1.database_id, fg1.name order by mf1.file_id)
						-- Check if corresponding Data file for same FileGroup exists on @newVolume
					,[isExistingOn_NewVolume] = CASE WHEN NOT EXISTS (
																		SELECT	mf2.*, NULL as [fileGroup]
																		FROM	sys.master_files mf2
																		WHERE	mf2.type_desc = mf1.type_desc 
																			AND	mf2.database_id = mf1.database_id
																			AND mf2.data_space_id = mf1.data_space_id -- same filegroup
																			AND mf2.physical_name like (@newVolume+'%')
																	)
													THEN 0
													ELSE 1
													END
					,[isExisting_UnrestrictedGrowth_on_OtherVolume] = CASE WHEN EXISTS (
																		SELECT	mf2.*, NULL as [fileGroup]
																		FROM	sys.master_files mf2
																		WHERE	mf2.type_desc = mf1.type_desc
																			AND	mf2.database_id = mf1.database_id
																			AND mf2.data_space_id = mf1.data_space_id -- same filegroup
																			AND mf2.growth <> 0
																			AND LEFT(mf2.physical_name, CHARINDEX('\',mf2.physical_name,4)) IN (select Volume from @mountPointVolumes V WHERE V.Volume <> @oldVolume AND [freespace(%)] >= 20.0)
																	)
													THEN 1
													ELSE 0
													END
			FROM	sys.master_files mf1 inner join sys.filegroups fg1 on fg1.data_space_id = mf1.data_space_id
			WHERE	mf1.type_desc = 'rows'
				AND	mf1.physical_name LIKE (@oldVolume+'%')
		)	
			INSERT #T_Files_Derived
			(	dbName, database_id, file_id, type_desc, data_space_id, name, physical_name, size, max_size, growth, is_percent_growth, fileGroup, 
				FileIDRankPerFileGroup, isExistingOn_NewVolume, isExisting_UnrestrictedGrowth_on_OtherVolume, Category, [Size (GB)], _name, _physical_name, TSQL_AddFile, TSQL_RestrictFileGrowth, TSQL_UnRestrictFileGrowth
			)
			SELECT	f.dbName, f.database_id, f.file_id, f.type_desc, f.data_space_id, f.name, f.physical_name, f.size, f.max_size, f.growth, f.is_percent_growth, f.fileGroup, 
					f.FileIDRankPerFileGroup, f.isExistingOn_NewVolume, f.isExisting_UnrestrictedGrowth_on_OtherVolume ,d.Category, d.[Size (GB)]
					,[_name]
					,[_physical_name] = @newVolume+[_name]+'.ndf'
					,[TSQL_AddFile] = CAST(NULL AS VARCHAR(2000))
					,[TSQL_RestrictFileGrowth] = CAST(NULL AS VARCHAR(2000))
					,[TSQL_UnRestrictFileGrowth] = CAST(NULL AS VARCHAR(2000))
			FROM	T_Files as f -- all data files on @oldVolume
			LEFT JOIN
					@DatabasesBySize	AS d
				ON	d.database_id = f.database_id
			LEFT JOIN -- get new names per filegroup
				(	SELECT	database_id, DBName, type_desc, name, _name = (CASE WHEN CHARINDEX(DBName,name) <> 0 THEN DBName ELSE '' END)+_Name_Without_DBName
							,FileOrder ,data_space_id
					FROM	(
								SELECT	database_id, DBName, type_desc, name, FileNO_String, FileNO_Int, Name_Without_DBName, FileOrder, data_space_id,
										[_Name_Without_DBName] = (CASE WHEN LEN( [_Name_Without_DBName]) > 0 THEN [_Name_Without_DBName] ELSE (CASE WHEN type_desc = 'ROWS' THEN '_Data01' ELSE '_Log01' END) END)
								FROM	(
											SELECT	T_Files_01.*
													,FileOrder = ROW_NUMBER()OVER(PARTITION BY DBName, type_desc, data_space_id ORDER BY FileNO_Int DESC)
													,MaxFileNO = MAX(FileNO_Int)OVER(PARTITION BY DBName, type_desc)
													,[_Name_Without_DBName] = CASE	WHEN LEN(FileNO_String)<>0 -- if more than 1 files already exist, then just increment no by 1
																					THEN REPLACE(Name_Without_DBName,FileNO_String,(CASE WHEN LEN(FileNO_Int+data_space_id) = 1 THEN ('0'+CAST((FileNO_Int+data_space_id) AS VARCHAR(20))) ELSE CAST((FileNO_Int+data_space_id) AS VARCHAR(20)) END )) 
																					ELSE Name_Without_DBName + (CASE WHEN type_desc = 'LOG' THEN '01' ELSE '_data01' END) END
											FROM	(
														SELECT mf.database_id, db_name(database_id) AS DBName, type_desc, name, data_space_id, growth
																,FileNO_String = RIGHT(name,PATINDEX('%[a-zA-Z_ ]%',REVERSE(name))-1)
																,FileNO_Int = CAST(RIGHT(name,PATINDEX('%[a-zA-Z_ ]%',REVERSE(name))-1) AS INT)
																,Name_Without_DBName = REPLACE ( name, db_name(database_id), '')
														FROM sys.master_files as mf
														WHERE mf.type_desc = 'ROWS'
													) AS T_Files_01
										) AS T_Files_02
							) AS T_Files_03
					WHERE	FileOrder = 1
				)  AS mf
				ON	mf.database_id = f.database_id
				AND	mf.type_desc = f.type_desc
				AND mf.data_space_id = f.data_space_id;
	END
	--	------------------------------------------------------------------------
	--	End: Find Data/Log files on @oldVolume
	--	============================================================================================

	IF	@_mirroringPartner IS NOT NULL
	BEGIN
		--	Find all databases that are part of Mirroring plan, their data files are +nt on @oldVolume and playing 'MIRROR' role.
		SELECT	@_mirrorDatabases = COALESCE(@_mirrorDatabases+', '+DB_NAME(database_id),DB_NAME(database_id))
		FROM	sys.database_mirroring m
		WHERE	m.mirroring_state IS NOT NULL
			AND	m.mirroring_role_desc = 'MIRROR'
			AND	m.database_id IN (select f.database_id from #T_Files_Derived as f);
		SET @_mirrorDatabaseCounts_Mirroring = (LEN(@_mirrorDatabases)-LEN(REPLACE(@_mirrorDatabases,',',''))+1);
		
		--	Find all databases that are part of Mirroring plan, their data files are +nt on @oldVolume and playing 'MIRROR' role.
		SELECT	@_principalDatabases = COALESCE(@_principalDatabases+', '+DB_NAME(database_id),DB_NAME(database_id))
		FROM	sys.database_mirroring m
		WHERE	m.mirroring_state IS NOT NULL
			AND	m.mirroring_role_desc = 'PRINCIPAL'
			AND	m.database_id IN (select f.database_id from #T_Files_Derived as f where (@addDataFiles = 0 OR (@addDataFiles = 1 AND f.isExistingOn_NewVolume = 0)) OR (@restrictDataFileGrowth = 0 OR (@restrictDataFileGrowth = 1 AND growth <> 0 AND f.isExistingOn_NewVolume = 1)) OR  (@unrestrictFileGrowth = 0 OR (@unrestrictFileGrowth = 1 AND growth = 0)));	
		SET @_principalDatabaseCounts_Mirroring = (LEN(@_principalDatabases)-LEN(REPLACE(@_principalDatabases,',',''))+1);

		--	Find all databases that are in Restoring mode, and cannot be accessed at the moment.
		SELECT	@_nonAccessibleDatabases = COALESCE(@_nonAccessibleDatabases+', '+DB_NAME(database_id),DB_NAME(database_id))
		FROM	sys.databases as d
		WHERE	d.state_desc = 'Restoring'
			AND	d.database_id NOT IN (SELECT m.database_id FROM sys.database_mirroring as m WHERE m.mirroring_role_desc IS NOT NULL)
			AND	d.database_id IN (select f.database_id from #T_Files_Derived as f);
		SET @_nonAccessibleDatabasesCounts = (LEN(@_nonAccessibleDatabases)-LEN(REPLACE(@_nonAccessibleDatabases,',',''))+1);

		--	Find all databases having multiple files per filegroup on @oldVolume.
		SELECT	@_databasesWithMultipleDataFiles = COALESCE(@_databasesWithMultipleDataFiles+', '+DB_NAME(database_id),DB_NAME(database_id))
		FROM  (	SELECT DISTINCT database_id FROM #T_Files_Derived AS m WHERE FileIDRankPerFileGroup <> 1 ) as f;
		SET @_databasesWithMultipleDataFilesCounts = (LEN(@_databasesWithMultipleDataFilesCounts)-LEN(REPLACE(@_databasesWithMultipleDataFilesCounts,',',''))+1);
	END

	--	Create temp table #T_Files_Final with Data files of @oldVolume that can be successfully processed for @addDataFiles & @restrictDataFileGrowth operations.
	IF OBJECT_ID('tempdb..#T_Files_Final') IS NOT NULL
		DROP TABLE #T_Files_Final;
	SELECT	*, ROW_NUMBER()OVER(PARTITION BY isExistingOn_NewVolume ORDER BY f.Category, f.[Size (GB)]) as ID
	INTO	#T_Files_Final
	FROM	#T_Files_Derived AS f
	WHERE	f.database_id NOT IN (SELECT m.database_id FROM	sys.database_mirroring m WHERE m.mirroring_state IS NOT NULL AND m.mirroring_role_desc = 'MIRROR')
		AND	f.database_id NOT IN (	SELECT d.database_id FROM sys.databases as d WHERE d.state_desc = 'Restoring'
										AND	d.database_id NOT IN (SELECT m.database_id FROM sys.database_mirroring as m WHERE m.mirroring_role_desc IS NOT NULL)
								);

IF (@_LogOrData='Log')
BEGIN
		UPDATE	#T_Files_Final
			SET	TSQL_AddFile = (CASE WHEN Category = 'Small' THEN '
	ALTER DATABASE ['+dbName+'] ADD LOG FILE ( NAME = N'+QUOTENAME(_name,'''')+', FILENAME = N'+QUOTENAME(_physical_name,'''')+' , SIZE = '+CAST(@smallDB_InititalLogSize_MB AS VARCHAR(10))+'MB , FILEGROWTH = '+CAST(@smallDB_LogAutoGrowth_MB AS VARCHAR(10))+'MB)
	GO
	PRINT	''Adding new file '+QUOTENAME(_name)+' for database ['+dbName+']''' 
															ELSE '
	ALTER DATABASE ['+dbName+'] ADD LOG FILE ( NAME = N'+QUOTENAME(_name,'''')+', FILENAME = N'+QUOTENAME(_physical_name,'''')+' , SIZE = '+CAST(@largeDB_InititalLogSize_MB AS VARCHAR(10))+'MB , FILEGROWTH = '+CAST(@largeDB_LogAutoGrowth_MB AS VARCHAR(10))+'MB)
	GO
	PRINT	''Adding new file '+QUOTENAME(_name)+' for database ['+dbName+']''' END)
					,TSQL_RestrictFileGrowth = '
	ALTER DATABASE ['+dbName+'] MODIFY FILE ( NAME = N'+QUOTENAME(name,'''')+', FILEGROWTH = 0)
	GO
	PRINT	''Restricting growth for file '+QUOTENAME(name)+' of database ['+dbName+']'''

					,TSQL_UnRestrictFileGrowth = (CASE WHEN Category = 'Small' THEN '
	ALTER DATABASE ['+dbName+'] MODIFY FILE ( NAME = N'+QUOTENAME(name,'''')+', FILEGROWTH = 10%)
	GO
	PRINT	''Removing restriction for file '+QUOTENAME(name)+' of database ['+dbName+']''' 
																				ELSE '
	ALTER DATABASE ['+dbName+'] MODIFY FILE ( NAME = N'+QUOTENAME(name,'''')+', FILEGROWTH = '+CAST(@largeDB_LogAutoGrowth_MB AS VARCHAR(10))+'MB)
	GO
	PRINT	''Removing restriction for file '+QUOTENAME(name)+' of database ['+dbName+']''' END)
END
ELSE
BEGIN
		UPDATE	#T_Files_Final
				SET	TSQL_AddFile = (CASE WHEN Category = 'Small' THEN '
	ALTER DATABASE ['+dbName+'] ADD FILE ( NAME = N'+QUOTENAME(_name,'''')+', FILENAME = N'+QUOTENAME(_physical_name,'''')+' , SIZE = '+CAST(@smallDB_InititalSize_MB AS VARCHAR(10))+'MB , FILEGROWTH = 10%) TO FILEGROUP [PRIMARY]
	GO
	PRINT	''Adding new file '+QUOTENAME(_name)+' for database ['+dbName+']'''		
																ELSE '
	ALTER DATABASE ['+dbName+'] ADD FILE ( NAME = N'+QUOTENAME(_name,'''')+', FILENAME = N'+QUOTENAME(_physical_name,'''')+' , SIZE = '+CAST(@largeDB_InititalSize_MB AS VARCHAR(10))+'MB , FILEGROWTH = '+CAST(@largeDB_AutoGrowth_MB AS VARCHAR(10))+'MB) TO FILEGROUP [PRIMARY]
	GO
	PRINT	''Adding new file '+QUOTENAME(_name)+' for database ['+dbName+']''' END)
					,TSQL_RestrictFileGrowth = '	
	ALTER DATABASE ['+dbName+'] MODIFY FILE ( NAME = N'+QUOTENAME(name,'''')+', FILEGROWTH = 0)
	GO
	PRINT	''Restricting growth for file '+QUOTENAME(name)+' of database ['+dbName+']'''

					,TSQL_UnRestrictFileGrowth = (CASE WHEN Category = 'Small' THEN '
	--	Remove Growth Restriction of File '+CAST(ID AS VARCHAR(5))+'			
	ALTER DATABASE ['+dbName+'] MODIFY FILE ( NAME = N'+QUOTENAME(name,'''')+', FILEGROWTH = 10%)
	GO
	PRINT	''Removing restriction for file '+QUOTENAME(name)+' of database ['+dbName+']''' 
															ELSE '
	--	Remove Growth Restriction of File '+CAST(ID AS VARCHAR(5))+'	
	ALTER DATABASE ['+dbName+'] MODIFY FILE ( NAME = N'+QUOTENAME(name,'''')+', FILEGROWTH = '+CAST(@largeDB_AutoGrowth_MB AS VARCHAR(10))+'MB)
	GO
	PRINT	''Removing restriction for file '+QUOTENAME(name)+' of database ['+dbName+']''' END)
END

	--	Find the free space % on @oldVolume
	SELECT	--s.available_bytes, s.logical_volume_name, s.total_bytes, s.volume_mount_point,
			@_freeSpace_OldVolume_GB = CAST(s.available_bytes/1024.0/1024.0/1024.0 AS DECIMAL(12,2)),
			@_totalSpace_OldVolume_GB = CAST(s.total_bytes/1024.0/1024.0/1024.0 AS DECIMAL(12,2)),
			@_freeSpace_OldVolume_Percent = CAST((s.available_bytes*100.0)/s.total_bytes AS DECIMAL(12,2))
			--38gb(40%) of 1024gb is available on 'E:\Data' volume
	FROM  (	SELECT TOP 1 * FROM #T_Files_Derived ) as f 
	CROSS APPLY
			sys.dm_os_volume_stats(f.database_id, f.file_id) as s;
--	----------------------------------------------------------------------------
	--	End:	Common Code 
--	============================================================================

--	============================================================================
	--	Begin:	@getInfo = 1
--	----------------------------------------------------------------------------
IF	@getInfo = 1
BEGIN
	SET @_sqlGetMountPointVolumes = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@_svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"';

	--creating a temporary table
	DELETE @output;

	--inserting disk name, total space and free space value in to temporary table
	INSERT @output
	EXEC xp_cmdshell @_sqlGetMountPointVolumes;

	IF OBJECT_ID('tempdb..#FilesByFileGroup') IS NOT NULL
		DROP TABLE #FilesByFileGroup;
	WITH T_FileGroup AS
	(	SELECT mf1.database_id, mf1.data_space_id, fg1.Name as [FileGroup], CONVERT(DECIMAL(20,2),((SUM(size)*8.0)/1024/1024)) AS [TotalFilesSize(GB)]
		FROM sys.master_files AS mf1 INNER JOIN sys.filegroups AS fg1 ON fg1.data_space_id = mf1.data_space_id
		GROUP BY mf1.database_id, mf1.data_space_id, fg1.Name
	)
	,T_Volumes AS
	(
		SELECT	RTRIM(LTRIM(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as Volume
			   ,ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('|',line)+1,
			   (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'
			   ,ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('%',line)+1,
			   (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'
		FROM	@output
		WHERE line like '[A-Z][:]%'
	)
	,T_Files_Filegroups AS
	(
		SELECT	mf.File_ID, mf.database_id as [DB_ID], DB_NAME(mf.database_id) AS [DB_Name], fg.[TotalFilesSize(GB)], fg.[FileGroup]
				,growth 
				,(CASE WHEN growth = 0 THEN '0' WHEN is_percent_growth = 1 THEN CAST(growth AS VARCHAR(5))+'%' 
				ELSE CAST(CONVERT( DECIMAL(20,2),((65536*8.0)/1024.0)) AS VARCHAR(20))+'(MB)'
				END) AS [growth(GB)]
				,name as [FileName] ,LEFT(physical_name, CHARINDEX('\',physical_name,4))  as [Volume] 
		FROM	sys.master_files AS mf
		INNER JOIN
				T_FileGroup AS fg
			ON	mf.database_id = fg.database_id AND mf.data_space_id = fg.data_space_id
		WHERE	mf.type_desc = 'ROWS'
	)
	,T_Volumes_Derived AS
	(
		SELECT	Volume
			   ,[capacity(MB)]
			   ,[freespace(MB)]
			   ,REVERSE(SUBSTRING(REVERSE(v.Volume),2,CHARINDEX('\',REVERSE(v.Volume),2)-2)) as VolumeName
			   ,CAST(([capacity(MB)]/1024.0) AS DECIMAL(20,2)) AS [capacity(GB)]
			   ,CAST(([freespace(MB)]/1024.0) AS DECIMAL(20,2)) AS [freespace(GB)]
			   ,CAST(([freespace(MB)]*100.0)/[capacity(MB)] AS DECIMAL(20,2)) AS [freespace(%)]
		FROM	T_Volumes v
		WHERE	v.Volume IN (SELECT DISTINCT [Volume] FROM T_Files_Filegroups)
			OR	v.Volume LIKE '[A-Z]:\Data[0-9]\'
			OR	v.Volume LIKE '[A-Z]:\Data[0-9][0-9]\'
	)
	,T_Files AS
	(
		SELECT	DB_ID, DB_Name, [TotalFilesSize(GB)], [FileGroup], f.FileName+' (Growth by '+[growth(GB)]+')' AS FileSettings, v.VolumeName+' = '+CAST([freespace(GB)] AS VARCHAR(20))+'GB('+CAST([freespace(%)] AS VARCHAR(20))+'%) Free of '+CAST([capacity(GB)] AS VARCHAR(20))+' GB' as FileDrive
				,growth, [growth(GB)], [FileName], v.Volume, [capacity(MB)], [freespace(MB)], VolumeName, [capacity(GB)], [freespace(GB)], [freespace(%)]
				,ROW_NUMBER()OVER(PARTITION BY v.Volume, f.DB_Name, f.[FileGroup] ORDER BY f.[File_ID]) AS FileID
		FROM	T_Files_Filegroups AS f
		RIGHT OUTER JOIN
				T_Volumes_Derived AS v
			ON	v.Volume = f.[Volume]
	),T_Files_Derived AS
	(
		SELECT	DB_ID, DB_Name, [TotalFilesSize(GB)], FileGroup, STUFF(
						(SELECT ', ' + f2.FileSettings
						 FROM T_Files as f2
						 WHERE f2.Volume = f.Volume AND f2.DB_Name = f.DB_Name AND f2.FileGroup = f.FileGroup
						 FOR XML PATH (''))
						  , 1, 1, ''
					) AS Files, FileDrive, growth, [growth(GB)], FileName, Volume, [capacity(MB)], [freespace(MB)], VolumeName, [capacity(GB)], [freespace(GB)], [freespace(%)], FileID
		FROM	T_Files as f
		WHERE	f.FileID = 1
	)
	SELECT	*
	INTO	#FilesByFileGroup
	FROM	T_Files_Derived;

	SELECT	@_commaSeparatedMountPointVolumes = COALESCE(@_commaSeparatedMountPointVolumes+', '+QUOTENAME(FileDrive), QUOTENAME(FileDrive)) --DISTINCT FileDrive
	FROM	(SELECT DISTINCT FileDrive FROM #FilesByFileGroup) AS FD;

	SET @_sqlGetInfo = '
	SELECT	DB_ID, DB_Name, [TotalFilesSize(GB)], FileGroup, '[email protected]_commaSeparatedMountPointVolumes+'
	FROM  (
			SELECT	DB_ID, DB_Name, [TotalFilesSize(GB)], FileGroup, Files, FileDrive
			FROM	#FilesByFileGroup
		  ) up
	PIVOT	(MAX(Files) FOR FileDrive IN ('[email protected]_commaSeparatedMountPointVolumes+')) AS pvt
	ORDER BY [DB_Name];
	';

	EXEC (@_sqlGetInfo);

END
--	----------------------------------------------------------------------------
	--	End:	@getInfo = 1
--	============================================================================


--	============================================================================
	--	Begin:	@getLogInfo = 1
--	----------------------------------------------------------------------------
IF	@getLogInfo = 1
BEGIN

	CREATE TABLE #stage([RecoveryUnitId] INT, [file_id] INT,[file_size] BIGINT,[start_offset] BIGINT,[f_seq_no] BIGINT,[status] BIGINT,[parity] BIGINT,[create_lsn] NUMERIC(38));
	DECLARE @Databases TABLE (ID INT IDENTITY(1,1), DBName VARCHAR(200));
	CREATE TABLE #LogInfoByFile (DBName VARCHAR(200), FileId INT, VLFCount INT);

	SET @_sqlGetMountPointVolumes = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@_svrName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"';

	DELETE @output;

	--inserting disk name, total space and free space value in to temporary table
	INSERT @output
	EXEC xp_cmdshell @_sqlGetMountPointVolumes;

	INSERT @Databases
	SELECT name FROM sys.databases;
	
	SET	@_loopCounter = 1;
	SET	@_loopCounts = (SELECT COUNT(*) FROM @Databases);

	WHILE (@_loopCounter <= @_loopCounts)
	BEGIN
		SELECT @_DBName = DBName FROM @Databases WHERE ID = @_loopCounter;
		SET @_loopSQLText = 'DBCC LOGINFO ('+QUOTENAME(@_DBName)+')
WITH  NO_INFOMSGS;';

		INSERT #stage
		EXEC (@_loopSQLText);

		INSERT #LogInfoByFile
		SELECT	@_DBName AS DBName,
				file_id as FileId,
				COUNT(*) AS VLFCount
		FROM	#stage
		GROUP BY [file_id];

		SET @_loopCounter += 1;
	END

	--SELECT * FROM #LogInfoByFile;

	;WITH T_Volumes AS
	(
		SELECT	RTRIM(LTRIM(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as Volume
			   ,ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('|',line)+1,
			   (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float),0) as 'capacity(MB)'
			   ,ROUND(CAST(RTRIM(LTRIM(SUBSTRING(line,CHARINDEX('%',line)+1,
			   (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float),0) as 'freespace(MB)'
		FROM	@output
		WHERE line like '[A-Z][:]%'
	)
	,T_Files_Size AS
	(
		SELECT mf.database_id, CONVERT(DECIMAL(20,2),((SUM(size)*8.0)/1024/1024)) AS [TotalFilesSize(GB)] FROM sys.master_files AS mf WHERE mf.type_desc = 'LOG' GROUP BY mf.database_id
	)
	,T_Files_Filegroups AS
	(
		SELECT	mf.database_id as [DB_ID], DB_NAME(mf.database_id) AS [DB_Name], [TotalFilesSize(GB)]
				,(CASE WHEN growth = 0 THEN '0' WHEN is_percent_growth = 1 THEN CAST(growth AS VARCHAR(5))+'%' 
				ELSE CAST(CONVERT( DECIMAL(20,2),((65536*8.0)/1024.0)) AS VARCHAR(20))+' mb'
				END) AS [growth(GB)]
				,mf.name as [FileName] ,LEFT(physical_name, CHARINDEX('\',physical_name,4))  as [Volume]
				,mf.* 
				,d.recovery_model_desc
		FROM	sys.master_files AS mf
		INNER JOIN
				sys.databases as d
		ON		d.database_id = mf.database_id
		LEFT JOIN
				T_Files_Size AS l
			ON	l.database_id = mf.database_id
		WHERE	mf.type_desc = 'LOG'
	)
	,T_Volumes_Derived AS
	(
		SELECT	Volume
			   ,[capacity(MB)]
			   ,[freespace(MB)]
			   ,REVERSE(SUBSTRING(REVERSE(v.Volume),2,CHARINDEX('\',REVERSE(v.Volume),2)-2)) as VolumeName
			   ,CAST(([capacity(MB)]/1024.0) AS DECIMAL(20,2)) AS [capacity(GB)]
			   ,CAST(([freespace(MB)]/1024.0) AS DECIMAL(20,2)) AS [freespace(GB)]
			   ,CAST(([freespace(MB)]*100.0)/[capacity(MB)] AS DECIMAL(20,2)) AS [freespace(%)]
		FROM	T_Volumes v
		WHERE	v.Volume IN (SELECT DISTINCT [Volume] FROM T_Files_Filegroups)
			OR	v.Volume LIKE '[A-Z]:\LOG[S][0-9]\'
			OR	v.Volume LIKE '[A-Z]:\LOG[S][0-9][0-9]\'
	)
	,T_Files AS
	(
		SELECT	DB_ID, DB_Name, [TotalFilesSize(GB)],
				f.FileName+' (VLF_Count|Size|AutoGrowth :: '+CAST(l.VLFCount AS VARCHAR(20))+'|'+CAST(CONVERT(DECIMAL(20,2),((size*8.0)/1024/1024)) AS VARCHAR(20))+' gb|'+[growth(GB)]+')' AS FileSettings, 
				v.VolumeName+' = '+CAST([freespace(GB)] AS VARCHAR(20))+'GB('+CAST([freespace(%)] AS VARCHAR(20))+'%) Free of '+CAST([capacity(GB)] AS VARCHAR(20))+' GB' as FileDrive
				,growth, [growth(GB)], [FileName], l.VLFCount
				,v.Volume, [capacity(MB)], [freespace(MB)], VolumeName, [capacity(GB)], [freespace(GB)], [freespace(%)]
				,ROW_NUMBER()OVER(PARTITION BY v.Volume, f.DB_Name ORDER BY f.[File_ID]) AS FileID
		FROM	T_Files_Filegroups AS f
		LEFT JOIN
				#LogInfoByFile AS l
			ON	l.DBName = DB_Name AND l.FileId = f.File_Id
		RIGHT OUTER JOIN
				T_Volumes_Derived AS v
			ON	v.Volume = f.[Volume]
	)
	,T_Files_Derived AS
	(
		SELECT	DB_ID, DB_Name, [TotalFilesSize(GB)], STUFF(
						(SELECT ', ' + f2.FileSettings
						 FROM T_Files as f2
						 WHERE f2.Volume = f.Volume AND f2.DB_Name = f.DB_Name
						 FOR XML PATH (''))
						  , 1, 1, ''
					) AS Files, FileDrive, growth, [growth(GB)], FileName, Volume, [capacity(MB)], [freespace(MB)], VolumeName, [capacity(GB)], [freespace(GB)], [freespace(%)], FileID
		FROM	T_Files as f
		WHERE	f.FileID = 1
	)
	SELECT	*
	INTO	#LogFiles
	FROM	T_Files_Derived;

	SELECT	@_commaSeparatedMountPointVolumes = COALESCE(@_commaSeparatedMountPointVolumes+', '+QUOTENAME(FileDrive), QUOTENAME(FileDrive)) --DISTINCT FileDrive
	FROM	(SELECT DISTINCT FileDrive FROM #LogFiles) AS FD;

	SET @_sqlGetInfo = '
	SELECT	DB_ID, DB_Name, [TotalFilesSize(GB)] as [TotalLogFilesSize(GB)], '[email protected]_commaSeparatedMountPointVolumes+'
	FROM  (
			SELECT	DB_ID, DB_Name, [TotalFilesSize(GB)], Files, FileDrive
			FROM	#LogFiles
		  ) up
	PIVOT	(MAX(Files) FOR FileDrive IN ('[email protected]_commaSeparatedMountPointVolumes+')) AS pvt
	ORDER BY [DB_Name];
	';

	EXEC (@_sqlGetInfo);
	
END
--	----------------------------------------------------------------------------
	--	End:	@getLogInfo = 1
--	============================================================================

--	============================================================================
	--	Begin:	@help = 1
--	----------------------------------------------------------------------------
IF	@help = 1
BEGIN
	PRINT	'
NAME
	[dbo].[usp_AnalyzeSpaceCapacity]

SYNOPSIS
	Analyze the Data Volume mount points for free space, database files, growth restriction and capacity exception.

SYNTAX
	EXEC [dbo].[usp_AnalyzeSpaceCapacity]	[ [@getInfo =] { 1 | 0 } ]
											|
											@getLogInfo = { 1 | 0 }
											|
											@help = { 1 | 0 }
											|
											@addDataFiles = { 1 | 0 } ,@newVolume = <drive_name>, @oldVolume = <drive_name> [ [,@smallDB_InititalSize_MB = size] [,@smallDB_InititalSize_Percent = value] [ ,@smallDBSize = size_in_GB ] [,@largeDB_InititalSize_MB = size] [,@largeDB_AutoGrowth_MB = size]
											|
											@addLogFiles = { 1 | 0 } ,@newVolume = <drive_name>, @oldVolume = <drive_name> [ [,@smallDB_InititalSize_MB = size] [,@smallDB_InititalSize_Percent = value] [ ,@smallDBSize = size_in_GB ] [,@largeDB_InititalSize_MB = size] [,@largeDB_AutoGrowth_MB = size]
											|
											@restrictDataFileGrowth = { 1 | 0 } ,@newVolume = <drive_name>, @oldVolume = <drive_name>
											|
											@restrictLogFileGrowth = { 1 | 0 } ,@newVolume = <drive_name>, @oldVolume = <drive_name>
											|
											@generateCapacityException = { 1 | 0 }, @oldVolume = <drive_name>
											|
											@unrestrictFileGrowth = { 1 | 0 }, @oldVolume = <drive_name>
											|
											@removeCapacityException = { 1 | 0 }, @oldVolume = <drive_name>
											|
											@UpdateMountPointSecurity = { 1 | 0 }
											|
											@restrictMountPointGrowth = { 1 | 0 }, @oldVolume = <drive_name>
										  } [;]

	<drive_name> :: { ''E:\Data\'' | ''E:\Data01'' | ''E:\Data2'' | ... }

	--------------------------------------- EXAMPLE 1 ----------------------------------------------
	EXEC [dbo].[usp_AnalyzeSpaceCapacity]

	This procedure returns general information like Data volumes, data files on those data volumes, Free space on data volumes, Growth settings of dbs etc.

	--------------------------------------- EXAMPLE 2 ----------------------------------------------
	EXEC [dbo].[usp_AnalyzeSpaceCapacity] @getLogInfo = 1

	This procedure returns general information like Log volumes, Log files on those log volumes, Free space on log volumes, Growth settings of dbs etc.
	
	--------------------------------------- EXAMPLE 3 ----------------------------------------------
	EXEC [dbo].[usp_AnalyzeSpaceCapacity] @help = 1

	This returns help for procedure usp_AnalyzeSpaceCapacity.

	--------------------------------------- EXAMPLE 4 ----------------------------------------------
	EXEC [dbo].[usp_AnalyzeSpaceCapacity] @addDataFiles = 1 ,@newVolume = ''E:\Data1\'' ,@oldVolume = ''E:\Data\''

	This generates TSQL Code for add secondary data files on @newVolume for each file of @oldVolume per FileGroup.

	--------------------------------------- EXAMPLE 5 ----------------------------------------------
	EXEC [dbo].[usp_AnalyzeSpaceCapacity] @restrictDataFileGrowth = 1 ,@oldVolume = ''E:\Data\''

	This generates TSQL Code to restrict growth of secondary data files on @oldVolume if corresponding Data files exists on @newVolume.

		--------------------------------------- EXAMPLE 6 ----------------------------------------------
	EXEC [dbo].[usp_AnalyzeSpaceCapacity] @addLogFiles = 1 ,@newVolume = ''E:\Logs1\'' ,@oldVolume = ''E:\Logs\''

	This generates TSQL Code for add log files on @newVolume for each database on @oldVolume.

	--------------------------------------- EXAMPLE 7 ----------------------------------------------
	EXEC [dbo].[usp_AnalyzeSpaceCapacity] @restrictLogFileGrowth = 1 ,@oldVolume = ''E:\Logs\''

	This generates TSQL Code to restrict growth of log files on @oldVolume if corresponding log files exists on @newVolume.
	
	--------------------------------------- EXAMPLE 8 ----------------------------------------------
	EXEC [dbo].[usp_AnalyzeSpaceCapacity] @unrestrictFileGrowth = 1, @oldVolume = ''E:\Data\''

	This generates TSQL Code for remove Data File growth Restriction for files on @oldVolume.

	--------------------------------------- EXAMPLE 9 ----------------------------------------------
	EXEC [dbo].[usp_AnalyzeSpaceCapacity] @generateCapacityException = 1, @oldVolume = ''E:\Data\''

	This generates TSQL Code for adding Space Capacity Exception for @oldVolume.

	--------------------------------------- EXAMPLE 10 ----------------------------------------------
	EXEC [dbo].[usp_AnalyzeSpaceCapacity] @UpdateMountPointSecurity = 1

	This will generate Powershell command to provide Full Access on @newVolume for SQL Server service accounts.

	--------------------------------------- EXAMPLE 11 ----------------------------------------------
	EXEC [dbo].[usp_AnalyzeSpaceCapacity] @restrictMountPointGrowth = 1, @oldVolume = ''E:\Data\''
	EXEC [dbo].[usp_AnalyzeSpaceCapacity] @restrictMountPointGrowth = 1, @oldVolume = ''E:\Logs2\''

	This will generate TSQL Code to restrict all the files on @oldVolume such that total files size consumes upto 79% of the mount point volume.
';
END
--	----------------------------------------------------------------------------
	--	End:	@help = 1
--	============================================================================

--	============================================================================
	--	Begin:	@addDataFiles = 1
--	----------------------------------------------------------------------------
IF	@addDataFiles = 1
BEGIN
	IF (SELECT COUNT(*) FROM @mountPointVolumes as V WHERE V.Volume IN (@newVolume,@oldVolume))<>2
	BEGIN -- Begin block for Validation of Data volumes
		SET @_errorMSG = '@newVolume and @oldVolume parameter values mandatory with @addDataFiles = 1 parameter. Verify if valid values are supplied.';
		IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12
			EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;
		ELSE
			EXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;
	END -- End block for Validation of Data volumes
	ELSE
	BEGIN -- Begin Else portion for Validation of Data volumes
		IF	@_mirrorDatabases IS NOT NULL
			PRINT	'/*	NOTE: Following '+CAST(@_mirrorDatabaseCounts_Mirroring AS VARCHAR(5))+' database(s) '+(case when @_mirrorDatabaseCounts_Mirroring > 1 then 'are' else 'is' end) +' in role of ''Mirroring Partner''. So add secondary files on Partner server '''[email protected]_mirroringPartner+''' for these dbs.
			'[email protected]_mirrorDatabases+'
*/';
	
		IF	@_nonAccessibleDatabases IS NOT NULL
		PRINT	'/*	NOTE: Following '+CAST(@_nonAccessibleDatabasesCounts AS VARCHAR(5))+' database(s) '+(case when @_nonAccessibleDatabasesCounts > 1 then 'are' else 'is' end) +' in Restoring mode. Either wait, or resolve the issue, and then create/restrict Data files.
			'[email protected]_nonAccessibleDatabases+'
*/';

		IF	@_principalDatabases IS NOT NULL
			PRINT	'/*	NOTE: Following '+CAST(@_principalDatabaseCounts_Mirroring AS VARCHAR(5))+' database(s) '+(case when @_principalDatabaseCounts_Mirroring > 1 then 'are' else 'is' end) +' in role of ''Mirroring Principal''. So generating code to add secondary files for these dbs. Kindly make sure that Same Data Volumes exists on DR server '''[email protected]_mirroringPartner+''' as well. Otherwise this shall fail.
			'[email protected]_principalDatabases+'
*/';

		IF	@_databasesWithMultipleDataFiles IS NOT NULL
			PRINT	'/*	NOTE: Following '+CAST(@_databasesWithMultipleDataFilesCounts AS VARCHAR(5))+' database(s) exists that have multiple files per filegroup on @oldVolume '+QUOTENAME(@oldVolume,'''') + '. But, this script will add only single file per filegroup per database on @newVolume '+QUOTENAME(@newVolume,'''') + '.
			'[email protected]_databasesWithMultipleDataFiles+'
*/';

		--	Generate TSQL Code for adding data files when it does not exist
		IF EXISTS (SELECT * FROM #T_Files_Final WHERE isExistingOn_NewVolume = 0 AND isExisting_UnrestrictedGrowth_on_OtherVolume = 0 )
		BEGIN	-- Begin block for tsql code generation

			DECLARE @T_Files_Final_Add TABLE (ID INT IDENTITY(1,1), TSQL_AddFile VARCHAR(2000));
			DELETE @T_Files_Final_Add;
			INSERT @T_Files_Final_Add
			SELECT TSQL_AddFile FROM #T_Files_Final as f WHERE isExistingOn_NewVolume = 0 AND isExisting_UnrestrictedGrowth_on_OtherVolume = 0 AND [FileIDRankPerFileGroup] = 1 ORDER BY f.DBName;

			SELECT @_loopCounter=MIN(ID), @_loopCounts=MAX(ID) FROM	@T_Files_Final_Add;
			
			WHILE @_loopCounter <= @_loopCounts
			BEGIN	-- Begin Block of Loop

				SELECT @_loopSQLText = '
--	Add File: '+CAST(@_loopCounter AS VARCHAR(5))+TSQL_AddFile FROM @T_Files_Final_Add as f WHERE f.ID = @_loopCounter;
				IF @_loopCounter = 1
					SET @_loopSQLText =	'USE [master]
GO
--	=====================================================================================================
	--	TSQL Code to Add Secondary Data Files on @newVolume '+QUOTENAME(@newVolume,'''') + ' that exists on @oldVolume '+QUOTENAME(@oldVolume,'''') + ' per FileGroup.
' + @_loopSQLText;

				PRINT @_loopSQLText;

				SET @_loopSQLText = '';
				SET @_loopCounter += 1;
			END		-- End Block of Loop
		END -- End block for tsql code generation
		
		--	Un-Restrict File Growth if file already exists on @newVolume
		IF EXISTS (SELECT * FROM #T_Files_Final WHERE isExistingOn_NewVolume = 1 AND isExisting_UnrestrictedGrowth_on_OtherVolume = 0)
		BEGIN	-- Begin block for Un-Restrict File Growth if file already exists on @newVolume

			DECLARE @T_Files_Final_AddUnrestrict TABLE (ID INT IDENTITY(1,1), TSQL_AddFile VARCHAR(2000));
			INSERT @T_Files_Final_AddUnrestrict
			SELECT	(CASE WHEN Category = 'Small' THEN '
ALTER DATABASE ['+dbName+'] MODIFY FILE ( NAME = N'+QUOTENAME(name,'''')+', FILEGROWTH = 10%)
GO'		ELSE '
ALTER DATABASE ['+dbName+'] MODIFY FILE ( NAME = N'+QUOTENAME(name,'''')+', FILEGROWTH = '+CAST(@largeDB_AutoGrowth_MB AS VARCHAR(20))+'MB)
GO' END)
			FROM	sys.master_files AS mf 
			INNER JOIN
					@DatabasesBySize AS s
			ON		s.database_id = mf.database_id
			INNER JOIN
				(
					SELECT mf1.database_id, mf1.data_space_id, MAX(mf1.file_id) AS MAX_file_id 
					FROM sys.master_files AS mf1
					WHERE mf1.type_desc = 'ROWS' AND mf1.physical_name LIKE (@newVolume+'%')
					AND EXISTS (SELECT * FROM #T_Files_Final AS t -- Find files on @newVolume with restrict growth
									WHERE isExistingOn_NewVolume = 1 AND isExisting_UnrestrictedGrowth_on_OtherVolume = 0 AND t.database_id = mf1.database_id AND t.data_space_id = mf1.data_space_id) 
					GROUP BY mf1.database_id, mf1.data_space_id
				) as rf
			ON		rf.database_id = mf.database_id
				AND	rf.data_space_id = mf.data_space_id
				AND	rf.MAX_file_id = mf.file_id
			ORDER BY DB_NAME(mf.database_id); --pick the latest file in case multiple log files exists

			SELECT @_loopCounter=MIN(ID), @_loopCounts=MAX(ID) FROM	@T_Files_Final_AddUnrestrict;
			
			WHILE @_loopCounter <= @_loopCounts
			BEGIN	-- Begin Block of Loop

				SELECT @_loopSQLText = '
--	Un-restrict Data File: '+CAST(@_loopCounter AS VARCHAR(5))+TSQL_AddFile FROM @T_Files_Final_AddUnrestrict as f WHERE f.ID = @_loopCounter;
				IF @_loopCounter = 1
					SET @_loopSQLText =	'

USE [master]
GO
--	=====================================================================================================
	--	TSQL Code to Remove Data file Growth restriction on @newVolume '+QUOTENAME(@newVolume,'''') + ' that exists on @oldVolume '+QUOTENAME(@oldVolume,'''')+'
	'+ @_loopSQLText;

				PRINT @_loopSQLText;

				SET @_loopSQLText = '';
				SET @_loopCounter += 1;
			END		-- End Block of Loop

		END -- End block for Un-Restrict File Growth if file already exists on @newVolume

		IF NOT EXISTS (SELECT * FROM #T_Files_Final WHERE NOT (isExistingOn_NewVolume = 1 OR IsExisting_UnrestrictedGrowth_on_OtherVolume = 1))
			PRINT	'/*	------------------------------------------------------------------------------------------------
	No secondary Data files to add on @newVolume '+QUOTENAME(@newVolume,'''')+' with respect to @oldVolume '+QUOTENAME(@oldVolume,'''') + '.
------------------------------------------------------------------------------------------------
*/'; 
	END	-- End Else portion for Validation of Data volumes

END -- End block of @addDataFiles = 1
--	----------------------------------------------------------------------------
	--	End:	@addDataFiles = 1
--	============================================================================

--	============================================================================
	--	Begin:	@addLogFiles = 1
--	----------------------------------------------------------------------------
IF	@addLogFiles = 1
BEGIN
	IF (SELECT COUNT(*) FROM @mountPointVolumes as V WHERE V.Volume IN (@newVolume,@oldVolume))<>2
	BEGIN -- Begin block for Validation of Data volumes
		SET @_errorMSG = '@newVolume and @oldVolume parameter values mandatory with @addLogFiles = 1 parameter. Verify if valid values are supplied.';
		IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12
			EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;
		ELSE
			EXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;
	END -- End block for Validation of Data volumes
	ELSE
	BEGIN -- Begin Else portion for Validation of Data volumes
		IF	@_mirrorDatabases IS NOT NULL
			PRINT	'/*	NOTE: Following '+CAST(@_mirrorDatabaseCounts_Mirroring AS VARCHAR(5))+' database(s) '+(case when @_mirrorDatabaseCounts_Mirroring > 1 then 'are' else 'is' end) +' in role of ''Mirroring Partner''. So add log files on Partner server '''[email protected]_mirroringPartner+''' for these dbs.
			'[email protected]_mirrorDatabases+'
*/';
	
		IF	@_nonAccessibleDatabases IS NOT NULL
		PRINT	'/*	NOTE: Following '+CAST(@_nonAccessibleDatabasesCounts AS VARCHAR(5))+' database(s) '+(case when @_nonAccessibleDatabasesCounts > 1 then 'are' else 'is' end) +' in Restoring mode. Either wait, or resolve the issue, and then create/restrict log files.
			'[email protected]_nonAccessibleDatabases+'
*/';

		IF	@_principalDatabases IS NOT NULL
			PRINT	'/*	NOTE: Following '+CAST(@_principalDatabaseCounts_Mirroring AS VARCHAR(5))+' database(s) '+(case when @_principalDatabaseCounts_Mirroring > 1 then 'are' else 'is' end) +' in role of ''Mirroring Principal''. So generating code to add files for these dbs. Kindly make sure that Same Mount Point Volumes exists on DR server '''[email protected]_mirroringPartner+''' as well. Otherwise this shall fail.
			'[email protected]_principalDatabases+'
*/';

		IF	EXISTS (SELECT * FROM #T_Files_Final WHERE isExistingOn_NewVolume = 0 AND [FileIDRankPerFileGroup] = 1)
			PRINT	'/*	NOTE: Few database(s) exists that have multiple files per filegroup on @oldVolume '+QUOTENAME(@oldVolume,'''') + '. But, this script will add only single file per filegroup per database on @newVolume '+QUOTENAME(@newVolume,'''') + '.
*/';

		--	Generate TSQL Code for adding log files when it does not exist
		IF EXISTS (SELECT * FROM #T_Files_Final WHERE isExistingOn_NewVolume = 0 AND isExisting_UnrestrictedGrowth_on_OtherVolume = 0)
		BEGIN	-- Begin block for tsql code generation
			DECLARE @T_LogFiles_Final_Add TABLE (ID INT IDENTITY(1,1), TSQL_AddFile VARCHAR(2000));
			INSERT @T_LogFiles_Final_Add
			SELECT TSQL_AddFile FROM #T_Files_Final as f WHERE isExistingOn_NewVolume = 0 AND [FileIDRankPerFileGroup] = 1 AND isExisting_UnrestrictedGrowth_on_OtherVolume = 0 ORDER BY f.DBName;

			SELECT @_loopCounter=MIN(ID), @_loopCounts=MAX(ID) FROM	@T_LogFiles_Final_Add;
			
			WHILE @_loopCounter <= @_loopCounts
			BEGIN	-- Begin Block of Loop

				SELECT @_loopSQLText = '
--	Add File: '+CAST(@_loopCounter AS VARCHAR(5))+TSQL_AddFile FROM @T_LogFiles_Final_Add as f WHERE f.ID = @_loopCounter;
				IF @_loopCounter = 1
					SET @_loopSQLText =	'USE [master]
GO
--	=====================================================================================================
	--	TSQL Code to Add Log Files on @newVolume '+QUOTENAME(@newVolume,'''') + ' that exists on @oldVolume '+QUOTENAME(@oldVolume,'''') + '.
' + @_loopSQLText;

				PRINT @_loopSQLText;

				SET @_loopSQLText = '';
				SET @_loopCounter += 1;
			END		-- End Block of Loop
		END -- End block for tsql code generation
		
		--	Un-Restrict File Growth if file already exists on @newVolume
		IF EXISTS (SELECT * FROM #T_Files_Final WHERE isExistingOn_NewVolume = 1 AND isExisting_UnrestrictedGrowth_on_OtherVolume = 0)
		BEGIN	-- Begin block for Un-Restrict File Growth if file already exists on @newVolume

			DECLARE @T_Files_Final_AddUnrestrictLogFiles TABLE (ID INT IDENTITY(1,1), TSQL_AddFile VARCHAR(2000));
			INSERT @T_Files_Final_AddUnrestrictLogFiles
			SELECT	(CASE WHEN Category = 'Small' THEN '
ALTER DATABASE ['+dbName+'] MODIFY FILE ( NAME = N'+QUOTENAME(name,'''')+', FILEGROWTH = '+CAST(@smallDB_LogAutoGrowth_MB AS VARCHAR(20))+'MB)
GO'		ELSE '
ALTER DATABASE ['+dbName+'] MODIFY FILE ( NAME = N'+QUOTENAME(name,'''')+', FILEGROWTH = '+CAST(@largeDB_LogAutoGrowth_MB AS VARCHAR(20))+'MB)
GO' END)
			FROM	sys.master_files AS mf 
			INNER JOIN
					@DatabasesBySize AS s
			ON		s.database_id = mf.database_id
			WHERE	mf.type_desc = 'LOG'
			AND		mf.physical_name LIKE (@newVolume+'%')
			AND		EXISTS (SELECT * FROM #T_Files_Final AS t -- Find files on @newVolume with restrict growth
							WHERE isExistingOn_NewVolume = 1 AND isExisting_UnrestrictedGrowth_on_OtherVolume = 0 AND t.database_id = mf.database_id)
			AND		mf.file_id IN (SELECT MAX(file_id) FROM sys.master_files AS mf1 WHERE mf1.type_desc = 'LOG' AND mf1.physical_name LIKE (@newVolume+'%') GROUP BY mf1.database_id)
			ORDER BY DB_NAME(mf.database_id); --pick the latest file in case multiple log files exists

			SELECT @_loopCounter=MIN(ID), @_loopCounts=MAX(ID) FROM	@T_Files_Final_AddUnrestrictLogFiles;
			
			WHILE @_loopCounter <= @_loopCounts
			BEGIN	-- Begin Block of Loop

				SELECT @_loopSQLText = '
--	Un-restrict Log File: '+CAST(@_loopCounter AS VARCHAR(5))+TSQL_AddFile FROM @T_Files_Final_AddUnrestrictLogFiles as f WHERE f.ID = @_loopCounter;
				IF @_loopCounter = 1
					SET @_loopSQLText =	'

USE [master]
GO
--	=====================================================================================================
	--	TSQL Code to Remove Log file Growth restriction on @newVolume '+QUOTENAME(@newVolume,'''') + ' that exists on @oldVolume '+QUOTENAME(@oldVolume,'''')+'
	'+ @_loopSQLText;

				PRINT @_loopSQLText;

				SET @_loopSQLText = '';
				SET @_loopCounter += 1;
			END		-- End Block of Loop

		END -- End block for Un-Restrict File Growth if file already exists on @newVolume
		
		IF NOT EXISTS (SELECT * FROM #T_Files_Final WHERE NOT (isExistingOn_NewVolume = 1 OR IsExisting_UnrestrictedGrowth_on_OtherVolume = 1))
			PRINT	'/*	------------------------------------------------------------------------------------------------
	No new Log files to add on @newVolume '+QUOTENAME(@newVolume,'''')+' with respect to @oldVolume '+QUOTENAME(@oldVolume,'''') + '.
------------------------------------------------------------------------------------------------
*/';
	END	-- End Else portion for Validation of Data volumes
END -- End block of @addLogFiles = 1
--	----------------------------------------------------------------------------
	--	End:	@addLogFiles = 1
--	============================================================================

--	============================================================================
	--	Begin:	@restrictDataFileGrowth = 1
--	----------------------------------------------------------------------------
IF	@restrictDataFileGrowth = 1
BEGIN
	IF (SELECT COUNT(*) FROM @mountPointVolumes as V WHERE V.Volume IN (@oldVolume))<>1
	BEGIN -- Begin block for Validation of Data volumes
		SET @_errorMSG = '@oldVolume parameter value is must with @restrictDataFileGrowth = 1 parameter. Verify if valid values are supplied.';
		IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12
			EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;
		ELSE
			EXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;
	END
	ELSE
	BEGIN -- Begin Else portion for Validation of Data volumes
		IF	@_mirrorDatabases IS NOT NULL
			PRINT	'/*	NOTE: Following '+CAST(@_mirrorDatabaseCounts_Mirroring AS VARCHAR(5))+' database(s) '+(case when @_mirrorDatabaseCounts_Mirroring > 1 then 'are' else 'is' end) +' in role of ''Mirroring Partner''. So restrict growth on Partner server '''[email protected]_mirroringPartner+''' for these dbs.
			'[email protected]_mirrorDatabases+'
*/';
	
		IF	@_nonAccessibleDatabases IS NOT NULL
		PRINT	'/*	NOTE: Following '+CAST(@_nonAccessibleDatabasesCounts AS VARCHAR(5))+' database(s) '+(case when @_nonAccessibleDatabasesCounts > 1 then 'are' else 'is' end) +' in Restoring mode. Either wait, or resolve the issue, and then create/restrict Data files.
			'[email protected]_nonAccessibleDatabases+'
*/';

		IF	@_principalDatabases IS NOT NULL
			PRINT	'/*	NOTE: Following '+CAST(@_principalDatabaseCounts_Mirroring AS VARCHAR(5))+' database(s) '+(case when @_principalDatabaseCounts_Mirroring > 1 then 'are' else 'is' end) +' in role of ''Mirroring Principal''. So generating code to restrict growth of secondary files for these dbs.
			'[email protected]_principalDatabases+'
*/';
		
		--	Find all databases for which Secondary Data files are yet to be added on @newVolume.
		SELECT	@_nonAddedDataFilesDatabases = COALESCE(@_nonAddedDataFilesDatabases+', '+DB_NAME(database_id),DB_NAME(database_id))
		FROM	(SELECT DISTINCT database_id FROM #T_Files_Final WHERE [isExisting_UnrestrictedGrowth_on_OtherVolume] = 0) as d;
		SET @_nonAddedDataFilesDatabasesCounts = (LEN(@_nonAddedDataFilesDatabases)-LEN(REPLACE(@_nonAddedDataFilesDatabases,',',''))+1);
		IF	@_nonAddedDataFilesDatabases IS NOT NULL
			PRINT	'/*	NOTE: New Data files for following '+CAST(@_nonAddedDataFilesDatabasesCounts AS VARCHAR(5))+' database(s) are yet to be added. So skipping these database for growth restriction.
			'[email protected]_principalDatabases+'
*/';

		--	Generate TSQL Code for restricting data files growth
		IF EXISTS (SELECT * FROM #T_Files_Final WHERE [isExisting_UnrestrictedGrowth_on_OtherVolume] = 1 AND growth <> 0)
		BEGIN	-- Begin block for tsql code generation
			
			DELETE @T_Files_Final_Restrict;
			INSERT @T_Files_Final_Restrict
			SELECT [TSQL_RestrictFileGrowth] FROM #T_Files_Final as f WHERE [isExisting_UnrestrictedGrowth_on_OtherVolume] = 1 AND growth <> 0 ORDER BY f.DBName;

			SELECT @_loopCounter=MIN(ID), @_loopCounts=MAX(ID) FROM	@T_Files_Final_Restrict;
			
			WHILE @_loopCounter <= @_loopCounts
			BEGIN
				SELECT @_loopSQLText = '
--	Restrict Growth of File: '+CAST(ID AS VARCHAR(5))+[TSQL_RestrictFileGrowth] FROM @T_Files_Final_Restrict as f WHERE f.ID = @_loopCounter;
				IF @_loopCounter = 1
					SET @_loopSQLText =	'USE [master]
GO
--	=====================================================================================================
	--	TSQL Code to Restrict Data Files growth on @oldVolume '+QUOTENAME(@oldVolume,'''') + ' for which Data file already exists on other Data volumes.
' + @_loopSQLText;

				PRINT @_loopSQLText;

				SET @_loopSQLText = '';
				SET @_loopCounter += 1;
			END
		END -- End block for tsql code generation
		ELSE
			PRINT	'/*	------------------------------------------------------------------------------------------------
	No Data files to restrict growth for @oldVolume '+QUOTENAME(@oldVolume,'''')+'.
------------------------------------------------------------------------------------------------
*/';
	END	-- End Else portion for Validation of Data volumes
END -- End block of @restrictDataFileGrowth = 1
--	============================================================================

--	============================================================================
	--	Begin:	@restrictLogFileGrowth = 1
--	----------------------------------------------------------------------------
IF	@restrictLogFileGrowth = 1
BEGIN
	IF (SELECT COUNT(*) FROM @mountPointVolumes as V WHERE V.Volume IN (@oldVolume))<>1
	BEGIN -- Begin block for Validation of Log volumes
		SET @_errorMSG = '@oldVolume parameter value is must with @restrictLogFileGrowth = 1 parameter. Verify if valid values are supplied.';
		IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12
			EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;
		ELSE
			EXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;
	END
	ELSE
	BEGIN -- Begin Else portion for Validation of Log volumes
		IF	@_mirrorDatabases IS NOT NULL
			PRINT	'/*	NOTE: Following '+CAST(@_mirrorDatabaseCounts_Mirroring AS VARCHAR(5))+' database(s) '+(case when @_mirrorDatabaseCounts_Mirroring > 1 then 'are' else 'is' end) +' in role of ''Mirroring Partner''. So restrict growth on Partner server '''[email protected]_mirroringPartner+''' for these dbs.
			'[email protected]_mirrorDatabases+'
*/';
	
		IF	@_nonAccessibleDatabases IS NOT NULL
		PRINT	'/*	NOTE: Following '+CAST(@_nonAccessibleDatabasesCounts AS VARCHAR(5))+' database(s) '+(case when @_nonAccessibleDatabasesCounts > 1 then 'are' else 'is' end) +' in Restoring mode. Either wait, or resolve the issue, and then create/restrict Data files.
			'[email protected]_nonAccessibleDatabases+'
*/';

		IF	@_principalDatabases IS NOT NULL
			PRINT	'/*	NOTE: Following '+CAST(@_principalDatabaseCounts_Mirroring AS VARCHAR(5))+' database(s) '+(case when @_principalDatabaseCounts_Mirroring > 1 then 'are' else 'is' end) +' in role of ''Mirroring Principal''. So generating code to restrict growth of secondary files for these dbs.
			'[email protected]_principalDatabases+'
*/';
		
		--	Find all databases for which log files are yet to be added on @newVolume.
		SELECT	@_nonAddedLogFilesDatabases = COALESCE(@_nonAddedLogFilesDatabases+', '+DB_NAME(database_id),DB_NAME(database_id))
		FROM	(SELECT DISTINCT database_id FROM #T_Files_Final WHERE [isExisting_UnrestrictedGrowth_on_OtherVolume] = 0) as d;
		SET @_nonAddedLogFilesDatabasesCounts = (LEN(@_nonAddedLogFilesDatabases)-LEN(REPLACE(@_nonAddedLogFilesDatabases,',',''))+1);
		IF	@_nonAddedLogFilesDatabases IS NOT NULL
			PRINT	'/*	NOTE: New Log files for following '+CAST(@_nonAddedLogFilesDatabasesCounts AS VARCHAR(5))+' database(s) are yet to be added. So skipping these database for growth restriction.
			'[email protected]_nonAddedLogFilesDatabases+'
*/';

		--	Generate TSQL Code for restricting data files growth
		IF EXISTS (SELECT * FROM #T_Files_Final WHERE [isExisting_UnrestrictedGrowth_on_OtherVolume] = 1 AND growth <> 0)
		BEGIN	-- Begin block for tsql code generation
			
			DELETE @T_Files_Final_Restrict;
			INSERT @T_Files_Final_Restrict
			SELECT [TSQL_RestrictFileGrowth] FROM #T_Files_Final as f WHERE [isExisting_UnrestrictedGrowth_on_OtherVolume] = 1 AND growth <> 0 ORDER BY f.DBName;

			SELECT @_loopCounter=MIN(ID), @_loopCounts=MAX(ID) FROM	@T_Files_Final_Restrict;
			
			WHILE @_loopCounter <= @_loopCounts
			BEGIN
				SELECT @_loopSQLText = '
--	Restrict Growth of File: '+CAST(ID AS VARCHAR(5))+[TSQL_RestrictFileGrowth] FROM @T_Files_Final_Restrict as f WHERE f.ID = @_loopCounter;
				IF @_loopCounter = 1
					SET @_loopSQLText =	'USE [master]
GO
--	=====================================================================================================
	--	TSQL Code to Restrict Log Files growth on @oldVolume '+QUOTENAME(@oldVolume,'''') + ' for which Log file already exists on other Log volumes.
' + @_loopSQLText;

				PRINT @_loopSQLText;

				SET @_loopSQLText = '';
				SET @_loopCounter += 1;
			END
		END -- End block for tsql code generation
		ELSE
			PRINT	'/*	------------------------------------------------------------------------------------------------
	No Log files to restrict growth for @oldVolume '+QUOTENAME(@oldVolume,'''')+'.
------------------------------------------------------------------------------------------------
*/';
	END	-- End Else portion for Validation of Log volumes
END -- End block of @restrictLogFileGrowth = 1
--	============================================================================

--	============================================================================
	--	Begin:	@unrestrictFileGrowth = 1
--	----------------------------------------------------------------------------
IF	@unrestrictFileGrowth = 1
BEGIN
	IF (SELECT COUNT(*) FROM @mountPointVolumes as V WHERE V.Volume IN (@oldVolume))<>1
	BEGIN -- Begin block for Validation of Data volumes
		SET @_errorMSG = '@oldVolume parameter value is mandatory with @unrestrictFileGrowth = 1 parameter. Verify if valid values are supplied.';
		IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12
			EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;
		ELSE
			EXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;
	END -- End block for Validation of Data volumes
	ELSE
	BEGIN -- Begin Else portion for Validation of Data volumes
		IF	@_mirrorDatabases IS NOT NULL
			PRINT	'/*	NOTE: Following '+CAST(@_mirrorDatabaseCounts_Mirroring AS VARCHAR(5))+' database(s) '+(case when @_mirrorDatabaseCounts_Mirroring > 1 then 'are' else 'is' end) +' in role of ''Mirroring Partner''. So unrestrict data files growth on Partner server '''[email protected]_mirroringPartner+''' for these dbs.
			'[email protected]_mirrorDatabases+'
*/';
	
		IF	@_nonAccessibleDatabases IS NOT NULL
		PRINT	'/*	NOTE: Following '+CAST(@_nonAccessibleDatabasesCounts AS VARCHAR(5))+' database(s) '+(case when @_nonAccessibleDatabasesCounts > 1 then 'are' else 'is' end) +' in Restoring mode. Either wait, or resolve the issue, and then unrestrict Data files.
			'[email protected]_nonAccessibleDatabases+'
*/';

		IF	@_principalDatabases IS NOT NULL
			PRINT	'/*	NOTE: Following '+CAST(@_principalDatabaseCounts_Mirroring AS VARCHAR(5))+' database(s) '+(case when @_principalDatabaseCounts_Mirroring > 1 then 'are' else 'is' end) +' in role of ''Mirroring Principal''. So generating code to un-restrict growth of secondary files for these dbs.
			'[email protected]_principalDatabases+'
*/';
		
		--	Generate TSQL Code for un-restricting data file growth
		IF EXISTS (SELECT * FROM #T_Files_Final WHERE growth = 0)
		BEGIN	-- Begin block for tsql code generation

			DECLARE @T_Files_Final_UnRestrictFiles TABLE (ID INT IDENTITY(1,1), TSQL_UnRestrictFileGrowth VARCHAR(2000));
			INSERT @T_Files_Final_UnRestrictFiles
			SELECT TSQL_UnRestrictFileGrowth FROM #T_Files_Final as f WHERE growth = 0;

			SELECT @_loopCounter=MIN(ID), @_loopCounts=MAX(ID) FROM	@T_Files_Final_UnRestrictFiles;
			
			WHILE @_loopCounter <= @_loopCounts
			BEGIN	-- Begin Block of Loop
				SELECT @_loopSQLText = '
--	Un-restrict Growth of File: '+CAST(ID AS VARCHAR(5))+TSQL_UnRestrictFileGrowth FROM @T_Files_Final_UnRestrictFiles as f WHERE f.ID = @_loopCounter;
				IF @_loopCounter = 1
					SET @_loopSQLText =	'USE [master]
GO
--	=====================================================================================================
	--	TSQL Code to Remove Restriction of Auto Growth for files on @oldVolume '+QUOTENAME(@oldVolume,'''') + '.
' + @_loopSQLText;

				PRINT @_loopSQLText;

				SET @_loopSQLText = '';
				SET @_loopCounter += 1;
			END		-- End Block of Loop
		END -- End block for tsql code generation
		ELSE
			PRINT	'/*	------------------------------------------------------------------------------------------------
	No files exists on @oldVolume '+QUOTENAME(@oldVolume,'''') + ' with Auto growth restriction.
------------------------------------------------------------------------------------------------
*/';
	END	-- End Else portion for Validation of Data volumes
END -- End block of @unrestrictFileGrowth = 1
--	----------------------------------------------------------------------------
	--	End:	@unrestrictFileGrowth = 1
--	============================================================================

--	============================================================================
	--	Begin:	@generateCapacityException = 1
--	----------------------------------------------------------------------------
IF	@generateCapacityException = 1
BEGIN
	IF (SELECT COUNT(*) FROM @mountPointVolumes as V WHERE V.Volume IN (@oldVolume))<>1
	BEGIN -- Begin block for Validation of Data volumes
		SET @_errorMSG = '@oldVolume parameter value is mandatory with @generateCapacityException = 1 parameter. Verify if valid values are supplied.';
		IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12
			EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;
		ELSE
			EXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;
	END -- End block for Validation of Data volumes
	ELSE
	BEGIN -- Begin Else portion for Validation of Data volumes
		PRINT	'/*	NOTE:	'+CAST(@_freeSpace_OldVolume_GB AS VARCHAR(20))+'gb('+CAST(@_freeSpace_OldVolume_Percent AS VARCHAR(20))+'%) of '+CAST(@_totalSpace_OldVolume_GB AS VARCHAR(20))+'gb is available on @oldVolume '+QUOTENAME(@oldVolume,'''')+'.
*/';
		PRINT	'
--	Add Space Capacity Exception for '+QUOTENAME(@oldVolume,'''')+'
	--	Execute Below code on Inventory server <MyDBInventoryServer>
';
		--	Find FQN
		DECLARE @Domain varchar(100), @key varchar(100);
		SET @key = 'SYSTEM\ControlSet001\Services\Tcpip\Parameters\';
		EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE', @[email protected],@value_name='Domain',@[email protected] OUTPUT;

		WITH T_Thresholds AS
		(
			SELECT	@oldVolume AS Volume, 
					[capacity (gb)] = ((((total_bytes/1024)/1024)/1024)+1),
					[pWarningThreshold%] = CEILING(((total_bytes-available_bytes)*100)/CAST(total_bytes AS FLOAT)),
					[pWarningThreshold (gb)] = FLOOR(((100-(CEILING(((total_bytes-available_bytes)*100)/CAST(total_bytes AS FLOAT))))*((((total_bytes/1024)/1024)/1024)+1))/100),

					[pCriticalThreshold%] = CEILING(((total_bytes-available_bytes)*100)/CAST(total_bytes AS FLOAT))+2,
					[pCriticalThreshold (gb)] = FLOOR(((100-(CEILING(((total_bytes-available_bytes)*100)/CAST(total_bytes AS FLOAT))+2))*((((total_bytes/1024)/1024)/1024)+1))/100),
					s.*--,f.ID
			FROM  (	SELECT TOP 1 * FROM sys.master_files as mf WHERE mf.physical_name LIKE (@oldVolume+'%') ) as f 
			CROSS APPLY
					sys.dm_os_volume_stats(f.database_id, f.file_id) as s
		)
		,T_Exception AS
		(
			SELECT	*
					,[pReason] = 'Data '+LEFT(f.Volume,LEN(f.Volume)-1)+' Unrestricted Cap:'+CAST([capacity (gb)] AS VARCHAR(20))+'gbs  Warn:'+CAST([pWarningThreshold%] AS VARCHAR(20))+'% '+CAST([pWarningThreshold (gb)] AS VARCHAR(20))+'gbs  Crit:'+CAST([pCriticalThreshold%] AS VARCHAR(20))+'% '+CAST([pCriticalThreshold (gb)] AS VARCHAR(20))+'gbs'
			FROM T_Thresholds as f
		)
			SELECT	@_capacityExceptionSQLText = '
IF NOT EXISTS (SELECT * FROM dbo.ExceptionTable e WHERE e.Type = ''Capacity Constraint'' AND e.serverName LIKE '''[email protected]@SERVERNAME+'%'' AND volumeName = '''+LEFT(e.Volume,LEN(e.Volume)-1)+''')
BEGIN
		DECLARE	@DateOfException SMALLDATETIME = GETDATE();

		--	Space Capacity Exception
		EXEC dbo.AddException_SpaceCapacity
				@pServerName	= '''[email protected]@servername+'.'[email protected]+''',
				@pVolumeName		= '''+LEFT(e.Volume,LEN(e.Volume)-1)+''',
				@pWarningThreshold	= '+CAST([pWarningThreshold%] AS VARCHAR(20))+',
				@pCriticalThreshold = '+CAST([pCriticalThreshold%] AS VARCHAR(20))+',
				@pStartDTS			= @DateOfException,
				@pEndDTS			= NULL,
				@pReason			= '''+[pReason]+''';
	END
END
GO
'
			FROM	T_Exception AS e;

			PRINT	@_capacityExceptionSQLText;
	END	-- End Else portion for Validation of Data volumes
END -- End block of @generateCapacityException = 1
--	----------------------------------------------------------------------------
	--	End:	@generateCapacityException = 1
--	============================================================================

--	============================================================================
	--	Begin:	@UpdateMountPointSecurity = 1
--	----------------------------------------------------------------------------
IF	@UpdateMountPointSecurity = 1
BEGIN
		PRINT	'/*	Import <<SQLDBATools>> powershell module, and then use <<Update-MountPointSecurity>> command after that.

Copy "\\SharedServer\DBATools\SQLDBATools.ps1" "E:\"
Import-Module "E:\SQLDBATools.psm1"
Update-MountPointSecurity -ServerName '+QUOTENAME(@@SERVERNAME,'"')+ '
*/';

END -- End block of @UpdateMountPointSecurity = 1
--	----------------------------------------------------------------------------
	--	End:	@UpdateMountPointSecurity = 1
--	============================================================================

--	============================================================================
	--	Begin:	@restrictMountPointGrowth = 1
--	----------------------------------------------------------------------------
IF	@restrictMountPointGrowth = 1
BEGIN
	IF EXISTS (SELECT * FROM sys.master_files as mf WHERE mf.physical_name LIKE (@oldVolume + '%') AND mf.growth <> 0)
	BEGIN
		PRINT	'Kindly restrict the growth of files +nt in @oldVolume = '+QUOTENAME(@oldVolume,'''')+'. Then, proceed for this step.';
	END
	ELSE
	BEGIN	-- Begin block : Real Logic for restricting mount point volume
		INSERT @DBFiles
		EXEC sp_msForEachDB '
		USE [?];
		SELECT	DB_NAME() AS DbName,
				name AS FileName,
				physical_name,
				size/128.0 AS CurrentSizeMB,
				size/128.0 -CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB,
				CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT)/128.0 AS [SpaceUsed],
				type_desc,
				growth,
				is_percent_growth
		FROM sys.database_files;
		';

		SELECT	@_Total_Files_Size_MB = SUM(f.CurrentSizeMB)
				,@_Space_That_Can_Be_Freed_MB = SUM(f.FreeSpaceMB)
		FROM	@DBFiles AS f
		WHERE	f.physical_name LIKE (@oldVolume + '%');

		SELECT	@_SpaceToBeFreed_MB = ((([email protected])*v.[capacity(MB)])/100) - (v.[capacity(MB)] - @_Total_Files_Size_MB)
		FROM	@mountPointVolumes v
		WHERE	v.Volume = @oldVolume;

		IF @_SpaceToBeFreed_MB <= 0
			PRINT	'Nothing to do here. Files on volume '+QUOTENAME(@oldVolume,'''')+ ' are already restricted to '+CAST(@mountPointGrowthRestrictionPercent AS VARCHAR(10))+'% of mount point capacity.';
		ELSE
		BEGIN
			PRINT	'Generate Code for shrinking files on volume '+QUOTENAME(@oldVolume,'''')+ ' to '+CAST(@mountPointGrowthRestrictionPercent AS VARCHAR(10))+'% of mount point capacity.';

			;WITH T_FileSpace_01 AS
			(
				SELECT	*
						,RowID = ROW_NUMBER()OVER(ORDER BY FreeSpaceMB DESC)
				FROM	@DBFiles AS f
				WHERE	f.physical_name LIKE (@oldVolume + '%')
			)
			,T_FileSpace_Final AS
			(
				SELECT	*
						,SpaceFreedOnFile = (s.FreeSpaceMB-512)
						,Total_SpaceFreedTillNow = (SELECT SUM(s1.FreeSpaceMB-512) FROM T_FileSpace_01 as s1 WHERE s1.RowID <= s.RowID)
				FROM	T_FileSpace_01 AS s
			)
				SELECT	--*,
						TSQL_ShrinkFile = '
	USE ['+DbName+']
	GO
	DBCC SHRINKFILE (N'''+[FileName]+''' , '+cast(convert(numeric,(SpaceUsed+512) ) as varchar(50))+')
	GO
	--	Space freed on file '+QUOTENAME([FileName])+ ' for database '+QUOTENAME(DbName)+' = '+cast(SpaceFreedOnFile as varchar(50))+' MB
	--	Total Space freed = '+CAST( Total_SpaceFreedTillNow AS VARCHAR(20))+' MB
	'
				FROM	T_FileSpace_Final AS s
				WHERE	s.Total_SpaceFreedTillNow <= @_SpaceToBeFreed_MB
					OR	(s.Total_SpaceFreedTillNow - @_SpaceToBeFreed_MB < SpaceFreedOnFile) ;
		END

	END	-- End block : Real Logic for restricting mount point volume
END -- End block of @restrictMountPointGrowth = 1
--	----------------------------------------------------------------------------
	--	End:	@restrictMountPointGrowth = 1
--	============================================================================
END -- End Procedure

One can use this script to add new data/log files on @newVolume, restrict old data/log files on @oldVolume, un-restrict growth @oldVolume in case if required, get general data files distribution info, get general log files distribution info, and shrink the files to occupy upto 79% of volume space. This script creates a procedure [dbo].[usp_AnalyzeSpaceCapacity] in [TempDB] database. It does not modify any system tables, so we don’t need to worry about any issue. In order to understand better about procedure, one can execute the procedure with @help = 1 parameter value.

EXEC tempdb..[usp_AnalyzeSpaceCapacity] @help = 1

Friends, please feel free to customize and use this script as per your needs. Also, kindly suggest any update that you feel could make the script better and more 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.