Create/Restrict Data Files – Automation

Ajay Dwivedi | April 16, 2017 | Disk Space Analysis, TSQL | No Comments

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, '+@_commaSeparatedMountPointVolumes+'
	FROM  (
			SELECT	DB_ID, DB_Name, [TotalFilesSize(GB)], FileGroup, Files, FileDrive
			FROM	#FilesByFileGroup
		  ) up
	PIVOT	(MAX(Files) FOR FileDrive IN ('+@_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)], '+@_commaSeparatedMountPointVolumes+'
	FROM  (
			SELECT	DB_ID, DB_Name, [TotalFilesSize(GB)], Files, FileDrive
			FROM	#LogFiles
		  ) up
	PIVOT	(MAX(Files) FOR FileDrive IN ('+@_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 '''+@_mirroringPartner+''' for these dbs.
			'+@_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.
			'+@_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 '''+@_mirroringPartner+''' as well. Otherwise this shall fail.
			'+@_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,'''') + '.
			'+@_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 '''+@_mirroringPartner+''' for these dbs.
			'+@_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.
			'+@_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 '''+@_mirroringPartner+''' as well. Otherwise this shall fail.
			'+@_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 '''+@_mirroringPartner+''' for these dbs.
			'+@_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.
			'+@_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.
			'+@_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.
			'+@_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 '''+@_mirroringPartner+''' for these dbs.
			'+@_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.
			'+@_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.
			'+@_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.
			'+@_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 '''+@_mirroringPartner+''' for these dbs.
			'+@_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.
			'+@_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.
			'+@_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', @key=@key,@value_name='Domain',@value=@Domain 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 '''+@@SERVERNAME+'%'' AND volumeName = '''+LEFT(e.Volume,LEN(e.Volume)-1)+''')
BEGIN
		DECLARE	@DateOfException SMALLDATETIME = GETDATE();

		--	Space Capacity Exception
		EXEC dbo.AddException_SpaceCapacity
				@pServerName	= '''+@@servername+'.'+@Domain+''',
				@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 = (((100-@mountPointGrowthRestrictionPercent)*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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

488

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

560

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

782

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

806

807

808

809

810

811

812

813

814

815

816

817

818

819

820

821

822

823

824

825

826

827

828

829

830

831

832

833

834

835

836

837

838

839

840

841

842

843

844

845

846

847

848

849

850

851

852

853

854

855

856

857

858

859

860

861

862

863

864

865

866

867

868

869

870

871

872

873

874

875

876

877

878

879

880

881

882

883

884

885

886

887

888

889

890

891

892

893

894

895

896

897

898

899

900

901

902

903

904

905

906

907

908

909

910

911

912

913

914

915

916

917

918

919

920

921

922

923

924

925

926

927

928

929

930

931

932

933

934

935

936

937

938

939

940

941

942

943

944

945

946

947

948

949

950

951

952

953

954

955

956

957

958

959

960

961

962

963

964

965

966

967

968

969

970

971

972

973

974

975

976

977

978

979

980

981

982

983

984

985

986

987

988

989

990

991

992

993

994

995

996

997

998

999

1000

1001

1002

1003

1004

1005

1006

1007

1008

1009

1010

1011

1012

1013

1014

1015

1016

1017

1018

1019

1020

1021

1022

1023

1024

1025

1026

1027

1028

1029

1030

1031

1032

1033

1034

1035

1036

1037

1038

1039

1040

1041

1042

1043

1044

1045

1046

1047

1048

1049

1050

1051

1052

1053

1054

1055

1056

1057

1058

1059

1060

1061

1062

1063

1064

1065

1066

1067

1068

1069

1070

1071

1072

1073

1074

1075

1076

1077

1078

1079

1080

1081

1082

1083

1084

1085

1086

1087

1088

1089

1090

1091

1092

1093

1094

1095

1096

1097

1098

1099

1100

1101

1102

1103

1104

1105

1106

1107

1108

1109

1110

1111

1112

1113

1114

1115

1116

1117

1118

1119

1120

1121

1122

1123

1124

1125

1126

1127

1128

1129

1130

1131

1132

1133

1134

1135

1136

1137

1138

1139

1140

1141

1142

1143

1144

1145

1146

1147

1148

1149

1150

1151

1152

1153

1154

1155

1156

1157

1158

1159

1160

1161

1162

1163

1164

1165

1166

1167

1168

1169

1170

1171

1172

1173

1174

1175

1176

1177

1178

1179

1180

1181

1182

1183

1184

1185

1186

1187

1188

1189

1190

1191

1192

1193

1194

1195

1196

1197

1198

1199

1200

1201

1202

1203

1204

1205

1206

1207

1208

1209

1210

1211

1212

1213

1214

1215

1216

1217

1218

1219

1220

1221

1222

1223

1224

1225

1226

1227

1228

1229

1230

1231

1232

1233

1234

1235

1236

1237

1238

1239

1240

1241

1242

1243

1244

1245

1246

1247

1248

1249

1250

1251

1252

1253

1254

1255

1256

1257

1258

1259

1260

1261

1262

1263

1264

1265

1266

1267

1268

1269

1270

1271

1272

1273

1274

1275

1276

1277

1278

1279

1280

1281

1282

1283

1284

1285

1286

1287

1288

1289

1290

1291

1292

1293

1294

1295

1296

1297

1298

1299

1300

1301

1302

1303

1304

1305

1306

1307

1308

1309

1310

1311

1312

1313

1314

1315

1316

1317

1318

1319

1320

1321

1322

1323

1324

1325

1326

1327

1328

1329

1330

1331

1332

1333

1334

1335

1336

1337

1338

1339

1340

1341

1342

1343

1344

1345

1346

1347

1348

1349

1350

1351

1352

1353

1354

1355

1356

1357

1358

1359

1360

1361

1362

1363

1364

1365

1366

1367

1368

1369

1370

1371

1372

1373

1374

1375

1376

1377

1378

1379

1380

1381

1382

1383

1384

1385

1386

1387

1388

1389

1390

1391

1392

1393

1394

1395

1396

1397

1398

1399

1400

1401

1402

1403

1404

1405

1406

1407

1408

1409

1410

1411

1412

1413

1414

1415

1416

1417

1418

1419

1420

1421

1422

1423

1424

1425

1426

1427

1428

1429

1430

1431

1432

1433

1434

1435

1436

1437

1438

1439

1440

1441

1442

1443

1444

1445

1446

1447

1448

1449

1450

1451

1452

1453

1454

1455

1456

1457

1458

1459

1460

1461

1462

1463

1464

1465

1466

1467

1468

1469

1470

1471

1472

1473

1474

1475

1476

1477

1478

1479

1480

1481

1482

1483

1484

1485

1486

1487

1488

1489

1490

1491

1492

1493

1494

1495

1496

1497

1498

1499

1500

1501

1502

1503

1504

1505

1506

1507

1508

1509

USE tempdb

IF OBJECT_ID('dbo.usp_AnalyzeSpaceCapacity') IS NULL

EXEC ('CREATE PROCEDURE dbo.usp_AnalyzeSpaceCapacity AS RETURN 0;')

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

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,

)

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)

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)

PRINT ''Adding new file '+QUOTENAME(_name)+' for database ['+dbName+']''' END)

,TSQL_RestrictFileGrowth = '

ALTER DATABASE ['+dbName+'] MODIFY FILE ( NAME = N'+QUOTENAME(name,'''')+', FILEGROWTH = 0)

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%)

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)

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]

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]

PRINT ''Adding new file '+QUOTENAME(_name)+' for database ['+dbName+']''' END)

,TSQL_RestrictFileGrowth = '

ALTER DATABASE ['+dbName+'] MODIFY FILE ( NAME = N'+QUOTENAME(name,'''')+', FILEGROWTH = 0)

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%)

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)

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, '+@_commaSeparatedMountPointVolumes+'

FROM (

SELECT DB_ID, DB_Name, [TotalFilesSize(GB)], FileGroup, Files, FileDrive

FROM #FilesByFileGroup

) up

PIVOT (MAX(Files) FOR FileDrive IN ('+@_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);

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)], '+@_commaSeparatedMountPointVolumes+'

FROM (

SELECT DB_ID, DB_Name, [TotalFilesSize(GB)], Files, FileDrive

FROM #LogFiles

) up

PIVOT (MAX(Files) FOR FileDrive IN ('+@_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 '''+@_mirroringPartner+''' for these dbs.

'+@_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.

'+@_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 '''+@_mirroringPartner+''' as well. Otherwise this shall fail.

'+@_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,'''') + '.

'+@_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]

-- =====================================================================================================

-- 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]

-- =====================================================================================================

-- 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 '''+@_mirroringPartner+''' for these dbs.

'+@_mirrorDatabases+'

*/';

IF @_nonAccessibleDatabases IS NOT NULL

'+@_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 '''+@_mirroringPartner+''' as well. Otherwise this shall fail.

'+@_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]

-- =====================================================================================================

-- 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]

-- =====================================================================================================

-- 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 '''+@_mirroringPartner+''' for these dbs.

'+@_mirrorDatabases+'

*/';

IF @_nonAccessibleDatabases IS NOT NULL

'+@_nonAccessibleDatabases+'

*/';

IF @_principalDatabases IS NOT NULL

'+@_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.

'+@_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]

-- =====================================================================================================

-- 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 '''+@_mirroringPartner+''' for these dbs.

'+@_mirrorDatabases+'

*/';

IF @_nonAccessibleDatabases IS NOT NULL

'+@_nonAccessibleDatabases+'

*/';

IF @_principalDatabases IS NOT NULL

'+@_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.

'+@_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]

-- =====================================================================================================

-- 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 '''+@_mirroringPartner+''' for these dbs.

'+@_mirrorDatabases+'

*/';

IF @_nonAccessibleDatabases IS NOT NULL

'+@_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.

'+@_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]

-- =====================================================================================================

-- 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', @key=@key,@value_name='Domain',@value=@Domain 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 '''+@@SERVERNAME+'%'' AND volumeName = '''+LEFT(e.Volume,LEN(e.Volume)-1)+''')

BEGIN

DECLARE @DateOfException SMALLDATETIME = GETDATE();

-- Space Capacity Exception

EXEC dbo.AddException_SpaceCapacity

@pServerName = '''+@@servername+'.'+@Domain+''',

@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

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 = (((100-@mountPointGrowthRestrictionPercent)*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+']

DBCC SHRINKFILE (N'''+[FileName]+''' , '+cast(convert(numeric,(SpaceUsed+512) ) as varchar(50))+')

-- 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