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:
- 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.
- 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.123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107USE tempdbGOIF OBJECT_ID('dbo.usp_AnalyzeSpaceCapacity') IS NULLEXEC ('CREATE PROCEDURE dbo.usp_AnalyzeSpaceCapacity AS RETURN 0;')GOALTER 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 = 89ASBEGIN/*Created By: Ajay DwivediUpdated 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=1PRINT '1) Declaring Variables';--creating a temporary tableDECLARE @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=1PRINT '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 NULLDROP 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=1PRINT '3) Inside Begin Validatins';IF (@addDataFiles=1 OR @restrictDataFileGrowth=1 OR @getInfo=1)SET @_LogOrData = 'Data';ELSESET @_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)BEGINSET @getInfo = 0;SET @getLogInfo = 0;ENDELSE --IF (@help=0 OR @addDataFiles=0 OR @restrictDataFileGrowth=0 OR @generateCapacityException=0 OR @unrestrictFileGrowth=0 OR @removeCapacityException=0 OR @UpdateMountPointSecurity=0)BEGINIF (@getLogInfo=0)SET @getInfo = 1;ENDIF (COALESCE(@getInfo,@help,@addDataFiles,@addLogFiles,@restrictDataFileGrowth,@restrictLogFileGrowth,@generateCapacityException,@unrestrictFileGrowth,@smallDB_InititalSize_MB,@largeDB_InititalSize_MB,@largeDB_AutoGrowth_MB,@smallDBSize,@restrictMountPointGrowth,@expandTempDBSize,-999) = -999)BEGINSET @_errorMSG = 'Procedure does not accept NULL for parameter values.';IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;ELSEEXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;ENDIF (@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 ))BEGINSET @_errorMSG = '@help=1 is incompatible with any other parameters.';IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;ELSEEXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;ENDIF (@generateCapacityException = 1 AND (@addDataFiles=1 OR @restrictDataFileGrowth=1 OR @unrestrictFileGrowth=1 OR @help=1 OR @removeCapacityException=1))BEGINSET @_errorMSG = '@generateCapacityException=1 is incompatible with any other parameters.';IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;ELSEEXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;ENDIF (@unrestrictFileGrowth = 1 AND (@addDataFiles=1 OR @restrictDataFileGrowth=1 OR @generateCapacityException=1 OR @help=1 OR @removeCapacityException=1))BEGINSET @_errorMSG = '@unrestrictFileGrowth=1 is incompatible with any other parameters.';IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;ELSEEXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;ENDIF (@removeCapacityException = 1 AND (@addDataFiles=1 OR @restrictDataFileGrowth=1 OR @generateCapacityException=1 OR @help=1 OR @unrestrictFileGrowth=1))BEGINSET @_errorMSG = '@removeCapacityException=1 is incompatible with any other parameters.';IF (select CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(50)),2) AS INT)) >= 12EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;ELSEEXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;ENDIF ( (@addDataFiles=1 OR @addLogFiles=1) AND (@newVolume IS NULL OR @oldVolume IS NULL))BEGINSET @_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)) >= 12EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;ELSEEXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;ENDIF ( (@restrictDataFileGrowth=1 OR @restrictLogFileGrowth=1 OR @restrictMountPointGrowth=1) AND (@oldVolume IS NULL))BEGINSET @_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)) >= 12EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;ELSEEXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;ENDIF @_verbose=1PRINT '4) End Validatins';-- Validation on @oldVolume & @newVolume for size is in Common Code-- ============================================================================-- End: Validations-- ============================================================================-- ============================================================================-- Begin: Common Code-- ----------------------------------------------------------------------------IF @_verbose=1PRINT '5) Begin Common Code';-- Begin: Get Data & Log Mount Point VolumesSET @_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 outputDELETE @output;--inserting disk name, total space and free space value in to temporary tableINSERT @outputEXEC xp_cmdshell @_powershellCMD;IF @_verbose=1PRINT '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 @outputWHERE 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 vWHERE 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=1PRINT '5.2) Begin Common Code: Values populated for @mountPointVolumes';-- End: Get Data & Log Mount Point Volumes-------------------------------------------------------------------------------------------------- Add '\' Backslash at the end for @oldVolume & @newVolumeSELECT @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)BEGINSET @_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)) >= 12EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;ELSEEXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;ENDIF @_verbose=1PRINT '5.3) Begin Common Code: Trimming @oldVolume and @newVolume';-------------------------------------------------------------------------------------------------- Begin: Get All Databases with size informationDECLARE @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 @DatabasesBySizeSELECT DBName, database_id, [Size (GB)], (CASE WHEN [Size (GB)] <= @smallDBSize THEN 'Small' ELSE 'Large' END) as CategoryFROM (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 fGROUP BY db_name(database_id), database_id) AS d;IF @_verbose=1PRINT '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=1PRINT '5.5) Begin Common Code: Starting Find Data/Log files on @oldVolume';IF (@_LogOrData = 'Log')BEGINIF @_verbose=1PRINT '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 @oldVolumeSELECT 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 mf2WHERE mf2.type_desc = mf1.type_descAND mf2.database_id = mf1.database_idAND mf2.physical_name like (@newVolume+'%'))THEN 0ELSE 1END,[isExisting_UnrestrictedGrowth_on_OtherVolume] = CASE WHEN NOT EXISTS (SELECT mf2.*, NULL as [fileGroup]FROM sys.master_files mf2WHERE mf2.type_desc = mf1.type_descAND mf2.database_id = mf1.database_idAND mf2.growth <> 0AND 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 0ELSE 1ENDFROM sys.master_files mf1WHERE 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 fLEFT JOIN@DatabasesBySize AS dON d.database_id = f.database_idLEFT JOIN( SELECT database_id, DBName, type_desc, name, _name = (CASE WHEN CHARINDEX(DBName,name) <> 0 THEN DBName ELSE '' END)+_Name_Without_DBNameFROM (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) ENDFROM (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_03WHERE FileOrder = 1) AS mfON mf.database_id = f.database_idAND mf.type_desc = f.type_desc;ENDELSEBEGINIF @_verbose=1PRINT '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 @oldVolumeSELECT 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 mf2WHERE mf2.type_desc = mf1.type_descAND mf2.database_id = mf1.database_idAND mf2.data_space_id = mf1.data_space_id -- same filegroupAND mf2.physical_name like (@newVolume+'%'))THEN 0ELSE 1END,[isExisting_UnrestrictedGrowth_on_OtherVolume] = CASE WHEN EXISTS (SELECT mf2.*, NULL as [fileGroup]FROM sys.master_files mf2WHERE mf2.type_desc = mf1.type_descAND mf2.database_id = mf1.database_idAND mf2.data_space_id = mf1.data_space_id -- same filegroupAND mf2.growth <> 0AND LEFT(mf2.physical_name, CHARINDEX('\',mf2.physical_name,4)) IN (select Volume from @mountPointVolumes V WHERE V.Volume <> @oldVolume AND [freespace(%)] >= 20.0))THEN 1ELSE 0ENDFROM sys.master_files mf1 inner join sys.filegroups fg1 on fg1.data_space_id = mf1.data_space_idWHERE 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 @oldVolumeLEFT JOIN@DatabasesBySize AS dON d.database_id = f.database_idLEFT 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_idFROM (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 1THEN 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) ENDFROM (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 mfWHERE mf.type_desc = 'ROWS') AS T_Files_01) AS T_Files_02) AS T_Files_03WHERE FileOrder = 1) AS mfON mf.database_id = f.database_idAND mf.type_desc = f.type_descAND mf.data_space_id = f.data_space_id;ENDIF @_verbose=1PRINT '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=1PRINT '5.9) Initializing values for @_mirrorDatabases, @_principalDatabases, @_nonAccessibleDatabases, @_databasesWithMultipleDataFiles';IF @_mirroringPartner IS NOT NULLBEGIN-- 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 mWHERE m.mirroring_state IS NOT NULLAND 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 mWHERE m.mirroring_state IS NOT NULLAND 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 dWHERE 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);ENDIF @_verbose=1PRINT '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 NULLDROP TABLE #T_Files_Final;SELECT *, ROW_NUMBER()OVER(PARTITION BY isExistingOn_NewVolume ORDER BY f.Category, f.[Size (GB)]) as IDINTO #T_Files_FinalFROM #T_Files_Derived AS fWHERE 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=1PRINT '5.11) Created temp table #T_Files_Final';IF (@_LogOrData='Log')BEGINIF @_verbose=1PRINT '5.12) Inside logic @_LogOrData=''Log''. Update #T_Files_Final';UPDATE #T_Files_FinalSET 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)GOPRINT ''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)GOPRINT ''Adding new file '+QUOTENAME(_name)+' for database ['+dbName+']''' END),TSQL_RestrictFileGrowth = 'ALTER DATABASE ['+dbName+'] MODIFY FILE ( NAME = N'+QUOTENAME(name,'''')+', FILEGROWTH = 0)GOPRINT ''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%)GOPRINT ''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)GOPRINT ''Removing restriction for file '+QUOTENAME(name)+' of database ['+dbName+']''' END)ENDELSEBEGINIF @_verbose=1PRINT '5.13) Inside else logic of @_LogOrData=''Log''. Update #T_Files_Final';UPDATE #T_Files_FinalSET 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]GOPRINT ''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]GOPRINT ''Adding new file '+QUOTENAME(_name)+' for database ['+dbName+']''' END),TSQL_RestrictFileGrowth = 'ALTER DATABASE ['+dbName+'] MODIFY FILE ( NAME = N'+QUOTENAME(name,'''')+', FILEGROWTH = 0)GOPRINT ''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%)GOPRINT ''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)GOPRINT ''Removing restriction for file '+QUOTENAME(name)+' of database ['+dbName+']''' END)ENDIF @_verbose=1PRINT '5.14) Find the free space % on @oldVolume';-- Find the free space % on @oldVolumeSELECT --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' volumeFROM ( SELECT TOP 1 * FROM #T_Files_Derived ) as fCROSS APPLYsys.dm_os_volume_stats(f.database_id, f.file_id) as s;IF @_verbose=1PRINT '5.15) End Common Code';-- ------------------------------------------------------------------------------ End: Common Code-- ============================================================================-- ============================================================================-- Begin: @getInfo = 1-- ----------------------------------------------------------------------------IF @getInfo = 1BEGINIF @_verbose=1PRINT '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 tableDELETE @output;--inserting disk name, total space and free space value in to temporary tableINSERT @outputEXEC xp_cmdshell @_sqlGetMountPointVolumes;IF @_verbose=1PRINT '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 NULLDROP 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_idGROUP 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 @outputWHERE 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 mfINNER JOINT_FileGroup AS fgON mf.database_id = fg.database_id AND mf.data_space_id = fg.data_space_idWHERE 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 vWHERE 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 FileIDFROM T_Files_Filegroups AS fRIGHT OUTER JOINT_Volumes_Derived AS vON v.Volume = f.[Volume]),T_Files_Derived AS(SELECT DB_ID, DB_Name, [TotalFilesSize(GB)], FileGroup, STUFF((SELECT ', ' + f2.FileSettingsFROM T_Files as f2WHERE f2.Volume = f.Volume AND f2.DB_Name = f.DB_Name AND f2.FileGroup = f.FileGroupFOR XML PATH ('')), 1, 1, '') AS Files, FileDrive, growth, [growth(GB)], FileName, Volume, [capacity(MB)], [freespace(MB)], VolumeName, [capacity(GB)], [freespace(GB)], [freespace(%)], FileIDFROM T_Files as fWHERE f.FileID = 1)SELECT *INTO #FilesByFileGroupFROM T_Files_Derived;IF @_verbose=1PRINT '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 NumberFROM #FilesByFileGroup order by First4Char,Number) AS FD;SET @_sqlGetInfo = 'SELECT DB_ID, DB_Name, [TotalFilesSize(GB)], FileGroup, '+@_commaSeparatedMountPointVolumes+'FROM (SELECT DB_ID, DB_Name, [TotalFilesSize(GB)], FileGroup, Files, FileDriveFROM #FilesByFileGroup) upPIVOT (MAX(Files) FOR FileDrive IN ('+@_commaSeparatedMountPointVolumes+')) AS pvtORDER BY [DB_Name];';EXEC (@_sqlGetInfo);IF @_verbose=1PRINT '6.2) Inside Begin: @getLogInfo = 1 - Ended';END-- ------------------------------------------------------------------------------ End: @getInfo = 1-- ============================================================================-- ============================================================================-- Begin: @getLogInfo = 1-- ----------------------------------------------------------------------------IF @getLogInfo = 1BEGINIF @_verbose=1PRINT '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 tableINSERT @outputEXEC xp_cmdshell @_sqlGetMountPointVolumes;INSERT @DatabasesSELECT name FROM sys.databases;SET @_loopCounter = 1;SET @_loopCounts = (SELECT COUNT(*) FROM @Databases);WHILE (@_loopCounter <= @_loopCounts)BEGINSELECT @_DBName = DBName FROM @Databases WHERE ID = @_loopCounter;SET @_loopSQLText = 'DBCC LOGINFO ('+QUOTENAME(@_DBName)+')WITH NO_INFOMSGS;';INSERT #stageEXEC (@_loopSQLText);INSERT #LogInfoByFileSELECT @_DBName AS DBName,file_id as FileId,COUNT(*) AS VLFCountFROM #stageGROUP 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 @outputWHERE 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_descFROM sys.master_files AS mfINNER JOINsys.databases as dON d.database_id = mf.database_idLEFT JOINT_Files_Size AS lON l.database_id = mf.database_idWHERE 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 vWHERE 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 FileIDFROM T_Files_Filegroups AS fLEFT JOIN#LogInfoByFile AS lON l.DBName = DB_Name AND l.FileId = f.File_IdRIGHT OUTER JOINT_Volumes_Derived AS vON v.Volume = f.[Volume]),T_Files_Derived AS(SELECT DB_ID, DB_Name, [TotalFilesSize(GB)], STUFF((SELECT ', ' + f2.FileSettingsFROM T_Files as f2WHERE f2.Volume = f.Volume AND f2.DB_Name = f.DB_NameFOR XML PATH ('')), 1, 1, '') AS Files, FileDrive, growth, [growth(GB)], FileName, Volume, [capacity(MB)], [freespace(MB)], VolumeName, [capacity(GB)], [freespace(GB)], [freespace(%)], FileIDFROM T_Files as fWHERE f.FileID = 1)SELECT *INTO #LogFilesFROM T_Files_Derived;--SELECT * FROM #LogFiles;SELECT @_commaSeparatedMountPointVolumes = COALESCE(@_commaSeparatedMountPointVolumes+', '+QUOTENAME(FileDrive), QUOTENAME(FileDrive)) --DISTINCT FileDriveFROM (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, FileDriveFROM #LogFiles) upPIVOT (MAX(Files) FOR FileDrive IN ('+@_commaSeparatedMountPointVolumes+')) AS pvtORDER BY [DB_Name];';EXEC (@_sqlGetInfo);IF @_verbose=1PRINT '7) Inside Begin: @getLogInfo = 1 - Ended';END-- ------------------------------------------------------------------------------ End: @getLogInfo = 1-- ============================================================================-- ============================================================================-- Begin: @help = 1-- ----------------------------------------------------------------------------IF @help = 1BEGINIF @_verbose=1PRINT '8) Inside Begin: @help = 1 ';PRINT 'NAME[dbo].[usp_AnalyzeSpaceCapacity]SYNOPSISAnalyze the Data Volume mount points for free space, database files, growth restriction and capacity exception.SYNTAXEXEC [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 = 1This 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 = 1This 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 = 1This 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 = 1EXEC [dbo].[usp_AnalyzeSpaceCapacity] @expandTempDBSize = 1, @tempDBMountPointPercent = 89This generates TSQL Code to increase TempdDB data files size to occupy 89% of mount point volume.';IF @_verbose=1PRINT '8.2) Finished executing Begin: @help = 1 ';END-- ------------------------------------------------------------------------------ End: @help = 1-- ============================================================================-- ============================================================================-- Begin: @addDataFiles = 1-- ----------------------------------------------------------------------------IF @addDataFiles = 1BEGINIF @_verbose=1PRINT '9) Inside Begin: @addDataFiles = 1';IF (SELECT COUNT(*) FROM @mountPointVolumes as V WHERE V.Volume IN (@newVolume,@oldVolume))<>2BEGIN -- Begin block for Validation of Data volumesSET @_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)) >= 12EXEC sp_executesql N'THROW 50000,@_errorMSG,1',N'@_errorMSG VARCHAR(200)', @_errorMSG;ELSEEXEC sp_executesql N'RAISERROR (@_errorMSG, 16, 1)', N'@_errorMSG VARCHAR(200)', @_errorMSG;END -- End block for Validation of Data volumesELSEBEGIN -- Begin Else portion for Validation of Data volumesIF @_verbose=1PRINT '9.1) Inside Begin: @addDataFiles = 1 - Begin Else portion for Validation of Data volumes';IF @_mirrorDatabases IS NOT NULLPRINT '/* 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 NULLPRINT '/* 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 NULLPRINT '/* 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 NULLPRINT '/* 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 existIF EXISTS (SELECT * FROM #T_Files_Final WHERE isExistingOn_NewVolume = 0 AND isExisting_UnrestrictedGrowth_on_OtherVolume = 0 )BEGIN -- Begin block for tsql code generationIF @_verbose=1PRINT '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_AddSELECT 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=1PRINT '9.3) Inside Begin: @addDataFiles = 1 - Starting to print Data File Addition Code in loop';WHILE @_loopCounter <= @_loopCountsBEGIN -- Begin Block of LoopSELECT @_loopSQLText = '-- Add File: '+CAST(@_loopCounter AS VARCHAR(5))+TSQL_AddFile FROM @T_Files_Final_Add as f WHERE f.ID = @_loopCounter;IF @_loopCounter = 1SET @_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 LoopIF @_verbose=1PRINT '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 @newVolumeIF 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 @newVolumeIF @_verbose=1PRINT '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_AddUnrestrictSELECT (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 mfINNER JOIN@DatabasesBySize AS sON s.database_id = mf.database_idINNER JOIN(SELECT mf1.database_id, mf1.data_space_id, MAX(mf1.file_id) AS MAX_file_idFROM sys.master_files AS mf1WHERE 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 growthWHERE 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 rfON rf.database_id = mf.database_idAND rf.data_space_id = mf.data_space_idAND 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_idSELECT @_loopCounter=MIN(ID), @_loopCounts=MAX(ID) FROM @T_Files_Final_AddUnrestrict;WHILE @_loopCounter <= @_loopCountsBEGIN -- Begin Block of LoopSELECT @_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 = 1SET @_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 LoopIF @_verbose=1PRINT '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 @newVolumeIF 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 volumesEND -- End block of @addDataFiles = 1-- ------------------------------------------------------------------------------ End: @addDataFiles = 1-- ============================================================================END -- End ProcedureIn 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 🙂