Easy Trick to Debug Long TSQL Codes

In my previous organization, working as Senior SQL Developer, I was often asked for help by fellow colleagues for identifying issues inside long and complex TSQL codes. Finding issues becomes much trickier if the issue is Runtime error.

So, as a solution for finding out issues in TSQL code, we have 2 methods:

  1. Using the Transact-SQL Debugger
    The Transact-SQL debugger in SQL Server Management Studio enables you to find errors in Transact-SQL scripts, stored procedures, triggers, and functions by observing their run-time behavior. You can start the debugger when you are using the Database Engine Query Editor window. By using the Transact-SQL debugger, you can do the following:

    • Step through the Transact-SQL statements in the editor line by line, or set breakpoints to stop at specific lines.
    • Step into or over Transact-SQL stored procedures, functions, or triggers that are run by the code in the editor window.
    • Watch the values that are assigned to variables, and observe system objects such as the call stack and threads.

    Below video by kudvenkat explains T-SQL Debugger in easy way.

    The only problem with this approach is slowness when we are debugging code from SSMS away from Server machine.

  2. Using intermittent message with tsql Print statement
    Let’s understand this by example. Recently I published a blog post with title Create/Restrict Data Files – Automation. I was using the same script on one of our server when I received below runtime error message:-

    In order resolve this, I redesigned the code to print intermittent messages inside TSQL code. So, below is the final query.

    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, @expandTempDBSize 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, @tempDBMountPointPercent TINYINT = 89
    AS
    BEGIN
    /*
    	Created By:		Ajay Dwivedi
    	Updated on:		06-May-2017
    */
    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)
    		,@_verbose TINYINT = 1;
    IF @_verbose=1
    	PRINT	'1) Declaring Variables';
    
    --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 @T_Files_ReSizeTempDB TABLE (ID INT IDENTITY(1,1), TSQL_ResizeTempDB_Files VARCHAR(2000));
    DECLARE @T_Files_restrictMountPointGrowth TABLE (ID INT IDENTITY(1,1), TSQL_restrictMountPointGrowth VARCHAR(2000));
    IF @_verbose=1 
    	PRINT	'2) Declaring Table Variables';
    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 @_verbose=1 
    	PRINT	'3) Inside Begin Validatins';
    	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 OR @expandTempDBSize=1)
    	BEGIN	
    		SET	@getInfo = 0;
    		SET @getLogInfo = 0;
    	END
    	ELSE --IF (@help=0 OR @addDataFiles=0 OR @restrictDataFileGrowth=0 OR @generateCapacityException=0 OR @unrestrictFileGrowth=0 OR @removeCapacityException=0 OR @UpdateMountPointSecurity=0)
    	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,@expandTempDBSize,-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 OR @expandTempDBSize=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
    IF @_verbose=1 
    	PRINT	'4) End Validatins';
    	--	Validation on @oldVolume & @newVolume for size is in Common Code
    --	============================================================================
    	--	End:	Validations 
    --	============================================================================
    
    --	============================================================================
    	--	Begin:	Common Code 
    --	----------------------------------------------------------------------------
    IF @_verbose=1 
    	PRINT	'5) 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;
    IF @_verbose=1 
    	PRINT	'5.1) Begin Common Code: Get Data & Log Mount Point Volumes executed';
    	;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]\';
    
    IF @_verbose=1 
    	PRINT	'5.2) Begin Common Code: Values populated for @mountPointVolumes';
    	--	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
    IF @_verbose=1 
    	PRINT	'5.3) Begin Common Code: Trimming @oldVolume and @newVolume';
    	------------------------------------------------------------------------------------------------
    	--	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;
    IF @_verbose=1 
    	PRINT	'5.4) Begin Common Code: Get All Databases with size information executed';
    	--	End: Get All Databases with size information
    	------------------------------------------------------------------------------------------------
    
    	--	============================================================================================
    	--	Begin: Find Data/Log files on @oldVolume
    	--	------------------------------------------------------------------------
    IF @_verbose=1 
    	PRINT	'5.5) Begin Common Code: Starting Find Data/Log files on @oldVolume';
    	IF (@_LogOrData = 'Log')
    	BEGIN
    		IF @_verbose=1 
    			PRINT	'5.6) Begin Common Code: inside @_LogOrData = ''Log''';
    		--	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
    		IF @_verbose=1 
    			PRINT	'5.7) Begin Common Code: inside else part of @_LogOrData = ''Log''';
    		--	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
    	IF @_verbose=1 
    		PRINT	'5.8) Begin Common Code: End of Find Data/Log files on @oldVolume';
    	--	------------------------------------------------------------------------
    	--	End: Find Data/Log files on @oldVolume
    	--	============================================================================================
    
    	--select * into tempdb..T_Files_Derived from #T_Files_Derived;
    	--SELECT * into tempdb..T_LogFiles_Derived from #T_LogFiles_Derived;
    
    	IF @_verbose=1 
    		PRINT	'5.9) Initializing values for @_mirrorDatabases, @_principalDatabases, @_nonAccessibleDatabases, @_databasesWithMultipleDataFiles';
    	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
    	IF @_verbose=1 
    		PRINT	'5.10) End of Initializing values for @_mirrorDatabases, @_principalDatabases, @_nonAccessibleDatabases, @_databasesWithMultipleDataFiles';
    
    	--	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 @_verbose=1 
    		PRINT	'5.11) Created temp table #T_Files_Final';
    
    IF (@_LogOrData='Log')
    BEGIN
    	IF @_verbose=1 
    		PRINT	'5.12) Inside logic @_LogOrData=''Log''. Update #T_Files_Final';
    		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
    	IF @_verbose=1 
    		PRINT	'5.13) Inside else logic of @_LogOrData=''Log''. Update #T_Files_Final';
    		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
    
    	IF @_verbose=1 
    		PRINT	'5.14) Find the free space % on @oldVolume';
    	--	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;
    IF @_verbose=1 
    	PRINT	'5.15) End Common Code';
    --	----------------------------------------------------------------------------
    	--	End:	Common Code 
    --	============================================================================
    
    --	============================================================================
    	--	Begin:	@getInfo = 1
    --	----------------------------------------------------------------------------
    IF	@getInfo = 1
    BEGIN
    	IF @_verbose=1 
    		PRINT	'6) Inside Begin:	@getInfo = 1';
    	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 @_verbose=1 
    		PRINT	'6.1) Inside Begin:	@getInfo = 1 - inserting disk name, total space and free space value in to temporary table';
    
    	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(CONVERT(BIGINT,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;
    
    	IF @_verbose=1 
    		PRINT	'6.2) Inside Begin:	@getInfo = 1 - Using CTE, Select into #FilesByFileGroup';
    
    	SELECT	@_commaSeparatedMountPointVolumes = COALESCE(@_commaSeparatedMountPointVolumes+', '+QUOTENAME(FileDrive), QUOTENAME(FileDrive)) --DISTINCT FileDrive
    	--FROM	(SELECT DISTINCT FileDrive FROM #FilesByFileGroup) AS FD;
    	FROM (	SELECT DISTINCT TOP 100 FileDrive, LEFT(FileDrive,4) AS First4Char, CAST(SUBSTRING(FileDrive, PATINDEX('%[0-9]%', FileDrive), PATINDEX('%[0-9][^0-9]%', FileDrive + 't') - PATINDEX('%[0-9]%', 
                        FileDrive) + 1) AS INT) AS Number
    			FROM #FilesByFileGroup order by First4Char,Number
    		) AS FD;
    
    	SET @_sqlGetInfo = '
    	SELECT	DB_ID, DB_Name, [TotalFilesSize(GB)], FileGroup, '[email protected]_commaSeparatedMountPointVolumes+'
    	FROM  (
    			SELECT	DB_ID, DB_Name, [TotalFilesSize(GB)], FileGroup, Files, FileDrive
    			FROM	#FilesByFileGroup
    		  ) up
    	PIVOT	(MAX(Files) FOR FileDrive IN ('[email protected]_commaSeparatedMountPointVolumes+')) AS pvt
    	ORDER BY [DB_Name];
    	';
    
    	EXEC (@_sqlGetInfo);
    	IF @_verbose=1 
    		PRINT	'6.2) Inside Begin:	@getLogInfo = 1 - Ended';
    
    END
    --	----------------------------------------------------------------------------
    	--	End:	@getInfo = 1
    --	============================================================================
    
    
    --	============================================================================
    	--	Begin:	@getLogInfo = 1
    --	----------------------------------------------------------------------------
    IF	@getLogInfo = 1
    BEGIN
    	IF @_verbose=1 
    		PRINT	'7) Inside Begin:	@getLogInfo = 1';
    
    	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, 
    				--f.FileName+' (Growth by '+[growth(GB)]+') with '+CAST(l.VLFCount AS VARCHAR(20))+' VLFs' 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 * FROM #LogFiles;
    	
    	SELECT	@_commaSeparatedMountPointVolumes = COALESCE(@_commaSeparatedMountPointVolumes+', '+QUOTENAME(FileDrive), QUOTENAME(FileDrive)) --DISTINCT FileDrive
    	FROM	(SELECT DISTINCT FileDrive FROM #LogFiles) AS FD;
    
    	SET @_sqlGetInfo = '
    	SELECT	DB_ID, DB_Name, [TotalFilesSize(GB)] as [TotalLogFilesSize(GB)], '[email protected]_commaSeparatedMountPointVolumes+'
    	FROM  (
    			SELECT	DB_ID, DB_Name, [TotalFilesSize(GB)], Files, FileDrive
    			FROM	#LogFiles
    		  ) up
    	PIVOT	(MAX(Files) FOR FileDrive IN ('[email protected]_commaSeparatedMountPointVolumes+')) AS pvt
    	ORDER BY [DB_Name];
    	';
    
    	EXEC (@_sqlGetInfo);
    	
    	IF @_verbose=1 
    		PRINT	'7) Inside Begin:	@getLogInfo = 1 - Ended';
    END
    --	----------------------------------------------------------------------------
    	--	End:	@getLogInfo = 1
    --	============================================================================
    
    --	============================================================================
    	--	Begin:	@help = 1
    --	----------------------------------------------------------------------------
    IF	@help = 1
    BEGIN
    	IF @_verbose=1 
    		PRINT	'8) Inside Begin:	@help = 1 ';
    	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 } ,@oldVolume = <drive_name>
    											|
    											@restrictLogFileGrowth = { 1 | 0 } ,@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>
    											|
    											@expandTempDBSize = { 1 | 0}
    										  } [;]
    
    	<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.
    
    	--------------------------------------- EXAMPLE 12 ----------------------------------------------
    	EXEC [dbo].[usp_AnalyzeSpaceCapacity] @expandTempDBSize = 1
    	EXEC [dbo].[usp_AnalyzeSpaceCapacity] @expandTempDBSize = 1, @tempDBMountPointPercent = 89
    
    	This generates TSQL Code to increase TempdDB data files size to occupy 89% of mount point volume.
    ';
    	IF @_verbose=1 
    		PRINT	'8.2) Finished executing Begin:	@help = 1 ';
    END
    --	----------------------------------------------------------------------------
    	--	End:	@help = 1
    --	============================================================================
    
    --	============================================================================
    	--	Begin:	@addDataFiles = 1
    --	----------------------------------------------------------------------------
    IF	@addDataFiles = 1
    BEGIN
    	IF @_verbose=1 
    		PRINT	'9) Inside Begin:	@addDataFiles = 1';
    	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 @_verbose=1 
    			PRINT	'9.1) Inside Begin:	@addDataFiles = 1 - Begin Else portion for Validation of Data volumes';
    		IF	@_mirrorDatabases IS NOT NULL
    			PRINT	'/*	NOTE: Following '+CAST(@_mirrorDatabaseCounts_Mirroring AS VARCHAR(5))+' database(s) '+(case when @_mirrorDatabaseCounts_Mirroring > 1 then 'are' else 'is' end) +' in role of ''Mirroring Partner''. So add secondary files on Partner server '''[email protected]_mirroringPartner+''' for these dbs.
    			'[email protected]_mirrorDatabases+'
    */';
    	
    		IF	@_nonAccessibleDatabases IS NOT NULL
    		PRINT	'/*	NOTE: Following '+CAST(@_nonAccessibleDatabasesCounts AS VARCHAR(5))+' database(s) '+(case when @_nonAccessibleDatabasesCounts > 1 then 'are' else 'is' end) +' in Restoring mode. Either wait, or resolve the issue, and then create/restrict Data files.
    			'[email protected]_nonAccessibleDatabases+'
    */';
    
    		IF	@_principalDatabases IS NOT NULL
    			PRINT	'/*	NOTE: Following '+CAST(@_principalDatabaseCounts_Mirroring AS VARCHAR(5))+' database(s) '+(case when @_principalDatabaseCounts_Mirroring > 1 then 'are' else 'is' end) +' in role of ''Mirroring Principal''. So generating code to add secondary files for these dbs. Kindly make sure that Same Data Volumes exists on DR server '''[email protected]_mirroringPartner+''' as well. Otherwise this shall fail.
    			'[email protected]_principalDatabases+'
    */';
    
    		IF	@_databasesWithMultipleDataFiles IS NOT NULL
    			PRINT	'/*	NOTE: Following '+CAST(@_databasesWithMultipleDataFilesCounts AS VARCHAR(5))+' database(s) exists that have multiple files per filegroup on @oldVolume '+QUOTENAME(@oldVolume,'''') + '. But, this script will add only single file per filegroup per database on @newVolume '+QUOTENAME(@newVolume,'''') + '.
    			'[email protected]_databasesWithMultipleDataFiles+'
    */';
    
    		--	Generate TSQL Code for adding data files when it does not exist
    		IF EXISTS (SELECT * FROM #T_Files_Final WHERE isExistingOn_NewVolume = 0 AND isExisting_UnrestrictedGrowth_on_OtherVolume = 0 )
    		BEGIN	-- Begin block for tsql code generation
    			IF @_verbose=1 
    				PRINT	'9.2) Inside Begin:	@addDataFiles = 1 - Generate TSQL Code for adding data files when it does not exist';
    
    			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;
    			
    			IF @_verbose=1 
    				PRINT	'9.3) Inside Begin:	@addDataFiles = 1 - Starting to print Data File Addition Code in loop';
    			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
    			IF @_verbose=1 
    				PRINT	'9.4) Inside Begin:	@addDataFiles = 1 - Loop Ended for print Data File Addition Code';
    		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
    			IF @_verbose=1 
    				PRINT	'9.5) Inside Begin:	@addDataFiles = 1 - 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
    			--WHERE	mf.type_desc = 'ROWS'
    			--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 mf.database_id, mf.data_space_id, MAX(file_id) AS MAX_file_id 
    			--FROM sys.master_files AS mf 
    			--WHERE mf.type_desc = 'ROWS' 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 t.data_space_id = mf.data_space_id) 
    			--GROUP BY mf.database_id, mf.data_space_id
    
    			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
    			IF @_verbose=1 
    				PRINT	'9.5) Inside Begin:	@addDataFiles = 1 - End block for Un-Restrict File Growth if file already exists on @newVolume';
    		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
    --	============================================================================
    END -- End Procedure

    In above code, I have declared a variable @_verbose at line 52. After that I am printing message for debugging purpose if value for @_verbos is equal to 1. This way I get to know which portion of my code is not working or throwing error.

    Now, below is the output from above modified query:-
    Finally, once our TSQL code debugging is complete, we can set off all the Print Messages just by setting the value of @_verbos variable to 0 at line 52. So, this way, we can debug our complex and long TSQL code just by changing the value of @_verbos variable from 0 to 1, and 1 to 0.

Friends, if you like the article, do Like & Share. Happy Coding 🙂

Leave a Reply

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