SQL - Shrink DB

alter PROCEDURE dbo._as_SQL_CheckAndShrinkDatabase
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @database_name NVARCHAR(128) = DB_NAME();
    DECLARE @file_id INT;
    DECLARE @file_name NVARCHAR(128);
    DECLARE @used_space_mb FLOAT;
    DECLARE @total_space_mb FLOAT;
    DECLARE @free_space_percent FLOAT;
    DECLARE @target_size_mb FLOAT;
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @start_time DATETIME;
    DECLARE @end_time DATETIME;
    DECLARE @time_taken_ms INT;
    DECLARE @mail_subject NVARCHAR(255);
    DECLARE @mail_body NVARCHAR(MAX);

    -- Create a temporary table to store the file information
    CREATE TABLE #FileSpaceInfo (
        file_id INT,
        file_name NVARCHAR(128),
        used_space_mb FLOAT,
        total_space_mb FLOAT,
        free_space_percent FLOAT
    );

    -- Get the space information for each data file in the database
    INSERT INTO #FileSpaceInfo
    SELECT 
        file_id,
        name AS file_name,
        CAST(fileproperty(name, 'SpaceUsed') AS FLOAT) / 128.0 AS used_space_mb,
        size / 128.0 AS total_space_mb,
        100.0 - (CAST(fileproperty(name, 'SpaceUsed') AS FLOAT) / CAST(size AS FLOAT) * 100.0) AS free_space_percent
    FROM sys.database_files
    WHERE type = 0; -- Only data files (0 = Data File, 1 = Log File)

    -- Loop through each file to check and shrink if necessary
    DECLARE file_cursor CURSOR FOR
    SELECT file_id, file_name, used_space_mb, total_space_mb, free_space_percent FROM #FileSpaceInfo;

    OPEN file_cursor;
    FETCH NEXT FROM file_cursor INTO @file_id, @file_name, @used_space_mb, @total_space_mb, @free_space_percent;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- If the free space is greater than 15%, shrink the file to used space + 10%
        IF @free_space_percent > 15
        BEGIN
            BEGIN TRY
				print concat('@used_space_mb: ',@used_space_mb)
                SET @target_size_mb = @used_space_mb * 1.1; -- Shrink to used space + 10%
				print concat('@@target_size_mb: ',@target_size_mb)
                SET @start_time = GETDATE();
                SET @sql = CONCAT('DBCC SHRINKFILE (' , QUOTENAME(@file_name) , ', ' , CAST(@target_size_mb AS int) , ');');
                --EXEC sp_executesql @sql;
				print @sql
                SET @end_time = GETDATE();
                SET @time_taken_ms = DATEDIFF(MILLISECOND, @start_time, @end_time);

                SET @mail_subject = 'Database Shrink Report for ' + @database_name;
                SET @mail_body = 'Shrinking data file ' + @file_name + CHAR(13) + CHAR(10) +
                                 'Original Size: ' + CAST(@total_space_mb AS NVARCHAR(50)) + ' MB' + CHAR(13) + CHAR(10) +
                                 'Target Size: ' + CAST(@target_size_mb AS NVARCHAR(50)) + ' MB' + CHAR(13) + CHAR(10) +
                                 'Free Space Percentage Before: ' + CAST(@free_space_percent AS NVARCHAR(50)) + '%' + CHAR(13) + CHAR(10) +
                                 'Time Taken: ' + CAST(@time_taken_ms AS NVARCHAR(50)) + ' ms' + CHAR(13) + CHAR(10) +
								 +'SQL: ' + @sql;

                EXEC msdb.dbo.sp_send_dbmail
                    @profile_name = 'Ambro DB Mail',
                    @recipients = 'alex+AmbroSQL@asamco.com',
                    @subject = @mail_subject,
                    @body = @mail_body;

                PRINT 'Shrinking data file ' + @file_name + ' to ' + CAST(@target_size_mb AS NVARCHAR(50)) + ' MB as it had more than 15% free space.';
            END TRY
            BEGIN CATCH
                PRINT 'Error occurred while shrinking data file ' + @file_name + ': ' + ERROR_MESSAGE();
            END CATCH
        END
        ELSE
        BEGIN
            PRINT 'Data file ' + @file_name + ' does not require shrinking.';
        END

        FETCH NEXT FROM file_cursor INTO @file_id, @file_name, @used_space_mb, @total_space_mb, @free_space_percent;
    END

    CLOSE file_cursor;
    DEALLOCATE file_cursor;

    DROP TABLE #FileSpaceInfo;
END
GO