Maintain database indexes

You can just create script or run right now the reindex.
Verify the configuration to filter databases or to change the FILLFACTOR.

PRINT '-- ***************************************** --'
PRINT '-- Reindex all tables'
PRINT '-- on selected databases'
PRINT '-- ***************************************** --'
PRINT '--'
PRINT '/*'
PRINT @@servername;
PRINT Getdate();
PRINT '*/'
SET NOCOUNT ON;

-- CONFIGURATION
DECLARE @runScriptRightNow INT = 0;
DECLARE @fillfactor AS NVARCHAR(10) = '99' -- occuped% -- https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index
BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH;
SELECT [name] INTO #dbs FROM sys.databases
WHERE database_id > 4
AND name NOT IN ('MyDatabaseNoReindex')
ORDER BY [name]

-- CONFIGURATION

USE [MASTER];

DECLARE @dbname nvarchar(200);
DECLARE @sql NVARCHAR(MAX);
DECLARE @ToKill AS NVARCHAR(MAX);

DECLARE @templateSelectTable AS NVARCHAR(MAX) = 'SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM [$(dbname)].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' 
DECLARE @templateReindex AS NVARCHAR(MAX) ='ALTER INDEX ALL ON $(tablename) REBUILD WITH (FILLFACTOR = $(fillfactor));'
SET @templateReindex = REPLACE(@templateReindex,'$(fillfactor)',@fillfactor)
 
DECLARE @table AS table (TableName nvarchar(256))
DECLARE @script AS table (Idx int identity,SqlScript nvarchar(max))
DECLARE @Idx int;
DECLARE @SqlScript nvarchar(max)
WHILE (EXISTS(SELECT * FROM #dbs))
BEGIN

    SELECT TOP 1 @dbname = name FROM #dbs;
    DELETE FROM #dbs     WHERE name = @dbname;

 SET @sql = REPLACE(@templateSelectTable,'$(dbname)',@dbname);
 DELETE FROM @table;
 INSERT INTO @table (TableName)
 EXEC(@sql)

 INSERT INTO  @script(SqlScript)
 select REPLACE(@templateReindex,'$(tablename)',TableName) from  @table;

 WHILE (EXISTS(SELECT * FROM @script))
 BEGIN
  SELECT TOP 1 @Idx = Idx, @SqlScript = SqlScript FROM @script t;
  DELETE FROM @script WHERE Idx = @Idx;
  
  IF (@runScriptRightNow = 1)
  BEGIN
   RAISERROR (@SqlScript, 10, 1) WITH NOWAIT;
   EXEC (@SqlScript);
  END
  ELSE
  PRINT @SqlScript
 END

END

Same code with safe mode and try again

GO
PRINT '-- ***************************************** --'
PRINT '-- Reindex all tables with safe mode'
PRINT '-- on selected databases'
PRINT '-- ***************************************** --'
PRINT '--'
PRINT '-- ' +@@servername;
PRINT '-- ' +cast(Getdate() as nvarchar(100));
SET NOCOUNT ON;

-- CONFIGURATION
DECLARE @runScriptRightNow INT = 1;
DECLARE @fillfactor AS NVARCHAR(10) = '98' -- occuped% -- https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index

BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH;
SELECT [name] INTO #dbs FROM sys.databases
WHERE database_id > 4
AND name   IN ('MyDatabaseNoReindex')
ORDER BY [name]

-- CONFIGURATION$(ESCAPE_NONE(dbname))

USE [MASTER];

DECLARE @dbname nvarchar(200);
DECLARE @sql NVARCHAR(MAX);
DECLARE @ToKill AS NVARCHAR(MAX);

DECLARE @templateSelectTable AS NVARCHAR(MAX) = 'SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM [[#dbname]].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''  
DECLARE @templateReindex AS NVARCHAR(MAX) ='
DECLARE @tryAgain INT = 2;
WHILE (@tryAgain>0)
BEGIN
    BEGIN TRY
        PRINT ''-- REINDEX Table  : [#tablename]'';
        ALTER INDEX ALL ON [#tablename] REBUILD WITH (FILLFACTOR = [#fillfactor]));
    END TRY
    BEGIN CATCH
        SET @tryAgain = @tryAgain - 1;
        WAITFOR DELAY ''00:00:10'';
        CONTINUE;
    END CATCH
    BREAK;
END;
'
SET @templateReindex = REPLACE(@templateReindex,'[#fillfactor])',@fillfactor)
 
DECLARE @table AS table (TableName nvarchar(256))
DECLARE @script AS table (Idx int identity,SqlScript nvarchar(max))
DECLARE @Idx int;
DECLARE @SqlScript nvarchar(max)
WHILE (EXISTS(SELECT * FROM #dbs))
BEGIN

    SELECT TOP 1 @dbname = name FROM #dbs;
    DELETE FROM #dbs     WHERE name = @dbname;

    SET @sql = REPLACE(@templateSelectTable,'[#dbname]',@dbname);
    DELETE FROM @table;
    INSERT INTO @table (TableName)
    EXEC(@sql)

    INSERT INTO  @script(SqlScript)
    select REPLACE(@templateReindex,'[#tablename]',TableName) from  @table;

    WHILE (EXISTS(SELECT * FROM @script))
    BEGIN
        SELECT TOP 1 @Idx = Idx, @SqlScript = SqlScript FROM @script t;
        DELETE FROM @script WHERE Idx = @Idx;
        
        IF (@runScriptRightNow = 1)
        BEGIN
            RAISERROR (@SqlScript, 10, 1) WITH NOWAIT;
            RAISERROR ('GO', 10, 1) WITH NOWAIT;
            EXEC (@SqlScript);
        END
        ELSE
        PRINT @SqlScript
    END

END
GO

Sources :

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index
https://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/

Create backup operator login

PRINT '-- Create backup operator login'

DECLARE @loginName as NVARCHAR(256) = N'UserDbBackupOperator'
DECLARE @password as nvarchar(128) = N'MyDbBackupStrongPassword'

DECLARE @sql AS NVARCHAR(MAX)
If not Exists (select loginname from master.dbo.syslogins where name = @loginName )
Begin
    Select @SQL = 'CREATE LOGIN ' + QUOTENAME(@loginName) + 'WITH PASSWORD=N'''+@password+''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
    EXEC sp_executesql @sql
End
select @sql = 'USE [?];IF (NOT EXISTS(SELECT * FROM [?].sys.database_principals WHERE name = '''+@loginName+''')) BEGIN CREATE USER ['+@loginName+'] FOR LOGIN ['+@loginName+'];ALTER ROLE [db_backupoperator] ADD MEMBER ['+@loginName+']; END';
EXEC sp_MSforeachdb @sql
go

Just add backup operator login over all databases

PRINT '-- Add backup operator login over all databases'

DECLARE @loginName as NVARCHAR(256) = N'UserDbBackupOperator'
DECLARE @sql AS NVARCHAR(MAX)
select @sql = 'USE [?];IF (NOT EXISTS(SELECT * FROM [?].sys.database_principals WHERE name = '''+@loginName+''')) BEGIN CREATE USER ['+@loginName+'] FOR LOGIN ['+@loginName+'];ALTER ROLE [db_backupoperator] ADD MEMBER ['+@loginName+']; END';
EXEC sp_MSforeachdb @sql
go

Script to get Linked server list. We have repeated linked server name if it has more than one associated remote login.

SELECT @@SERVERNAME AS 'Server Name'
,      sys.servers.server_id AS 'IdLinkedServer'
,      sys.servers.name AS 'Linked Server Name'
,      CASE sys.servers.Server_id WHEN 0 THEN 'Current Server'
                                         ELSE 'Remote Server' END AS 'Server '
,      sys.servers.product
,      sys.servers.provider
,      sys.servers.data_source
,      sys.servers.catalog
,      CASE sys.linked_logins.uses_self_credential WHEN 1 THEN 'Uses Self Credentials'
                                                          ELSE sys.server_principals.name END AS 'Local Login '
,      sys.linked_logins.remote_name AS 'Remote Login Name'
,      CASE sys.servers.is_rpc_out_enabled WHEN 1 THEN 'True'
                                                  ELSE 'False' END AS 'RPC Out Enabled'
,      CASE sys.servers.is_data_access_enabled WHEN 1 THEN 'True'
                                                      ELSE 'False' END AS 'Data Access Enabled'
,      sys.servers.modify_date
FROM            sys.servers          
LEFT OUTER JOIN sys.linked_logins     ON sys.servers.server_id = sys.linked_logins.server_id
LEFT OUTER JOIN sys.server_principals ON sys.server_principals.principal_id = sys.linked_logins.local_principal_id

Source: https://gallery.technet.microsoft.com/scriptcenter/Get-List-of-Linked-Server-d6c95d9c

Context: Database log file is huge and you are not able to shrink it. You do not have more free space. Disaster is coming, or it is already there.

What you have done? I hope you never use this script, but if you need to use this script, you must to think how you never use again. This atomic database bomb.

Shrink database is bad practice (Increases fragmentation and reduces performance).  "Shrink database" is for puny dba, but this, well, you are god-like DBA.
I am sure, you have no choice at this moment.

This script creates a script to delete the log file when we use Always On.

Steps :

1. Remove database from availability group
2. Set recovery to simple
3. Shrink database
4. Set recovery to full
5. Backup database
6. Add database to availability group
7. Backup database
8. Restore database on secondary
9. Backup transactions
10. Restore transactions on secondary
11. Wait for the replica to start communicating
12. Alter database set HADR on secondary

Required:

1. Network Shared folder.
2. Use SQLCMD mode.
3. You must be sysadmin.
4. Change parameters : dbname, AlwaysOnName, FullPathBackupFile

If you use "Results to Text" on your query, probably, the created script will cut. Use "Results to grid" on you query execution.

/****************************************
*
* Tools for AlwaysOn - Exec on Primary Server
*
* When you use this:
*  - You have Always On Server
*  - Log file is full OR you need to add new database into 'Always On'
*
****************************************/
GO
--------------------------------------------------------------
-- Configuration --
--------------------------------------------------------------

DECLARE @dbname nvarchar(100) = 'Northwind';
DECLARE @AlwaysOnName nvarchar(100) = 'MyAlwaysOn';
DECLARE @FullPathBackupFile nvarchar(1024) = '\\SQL01\backup$\';

--------------------------------------------------------------

DECLARE @sql nvarchar(MAX);
DECLARE @PrimaryServer NVARCHAR(100);
DECLARE @SecondaryServer NVARCHAR(100);
SELECT @PrimaryServer = replica_server_name FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id WHERE role=1;
SELECT @SecondaryServer = replica_server_name FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id WHERE role=2;

IF (NOT (ISNULL(@PrimaryServer,'') = @@servername))
BEGIN
PRINT '--';
PRINT '-- You MUST execute this script on primary server.';
PRINT '--';
raiserror('You MUST execute this script on primary server.', 20, -1) with log
END

IF (NOT EXISTS(select * FROM sys.availability_groups WHERE [name] = @AlwaysOnName))
BEGIN
PRINT '';
PRINT '';
PRINT '-- We can not continue, Availability group does NOT exist: ';
PRINT ' ' + @AlwaysOnName;
PRINT '';
PRINT '';
raiserror('** We can not continue, Availability group does NOT exist **', 20, -1) with log;
END

IF (NOT EXISTS(select * FROM sys.databases WHERE [name] = @dbname))
BEGIN
PRINT '';
PRINT '';
PRINT '-- We can not continue, the database does NOT exist: ';
PRINT ' ' + @dbname;
PRINT '';
PRINT '';
raiserror('** We can not continue, Availability group does NOT exist **', 20, -1) with log;
END

SET NOCOUNT ON;
DECLARE @SHRINKFILE AS nvarchar(MAX) = '';
SELECT @SHRINKFILE=@SHRINKFILE+'USE [#(dbname)];DBCC SHRINKFILE (N'''+mf.name+''' , 0, TRUNCATEONLY);'+CHAR(10)+CHAR(13) FROM sys.master_files AS mf , sys.databases AS db WHERE mf.[type] = 1 AND mf.database_id = db.database_id AND db.name = @dbname;


IF (EXISTS(
SELECT replica_server_name
FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states
ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id
WHERE role=1 and replica_server_name = @@servername) -- role = 1 : primary
)
BEGIN


SET @sql = '
/****************************************
*
* DBA - Disaster Recovery
*
* You MUST exec on Primary Server
*
* Database name : #(dbname)
* Primary Server : #(PrimaryServer)
* Secondary Server : #(SecondaryServer)
*
* When you use this:
*    -  You have "Always On" SQL Server
*    -  Log file is full
*    -  You want to add new database into your "Always On"
*
* Script steps:
*    - Remove database from Availability Group, if it is there.
*    - Set recovery simple
*    - Shrink log file
*    - Backup database
*    - Set recovery full
*    - Backup database
*    - Restore database
*    - Backup transactions
*    - Restore transactions
*    - Add database to Always On
*
* by Sozezzo
* #(getdate)
*
****************************************/

-- Check SQLCMD mode
:SETVAR CHECK SQLCMD
GO
IF (NOT ''$(CHECK)'' = ''SQLCMD'')
BEGIN
PRINT '''';
PRINT '''';
PRINT '' ** YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE. **'';
PRINT '''';
PRINT '''';
raiserror(''YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE'', 20, -1) with log
END

DECLARE @PrimaryServer NVARCHAR(100);
DECLARE @SecondaryServer NVARCHAR(100);
SELECT @PrimaryServer = replica_server_name FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id WHERE role=1;
SELECT @SecondaryServer = replica_server_name FROM sys.availability_replicas INNER JOIN sys.dm_hadr_availability_replica_states ON sys.availability_replicas.replica_id = sys.dm_hadr_availability_replica_states.replica_id WHERE role=2;

IF ( NOT ( ''#(PrimaryServer)'' = @PrimaryServer AND ''#(SecondaryServer)'' = @SecondaryServer))
BEGIN
PRINT '''';
PRINT '' ** The context of servers have been changed. **'';
PRINT '''';
PRINT '' This script will be create when '';
PRINT '' * Primary server is : #(PrimaryServer)'';
PRINT '' * Secondary server is : #(SecondaryServer)'';
PRINT '''';
raiserror(''The context of servers have been changed.'', 20, -1) with log
END

PRINT ''---------------------------------------------'';
PRINT '''';
PRINT ''-- Disaster recovery database : #(dbname)''
PRINT '''';
PRINT ''-- '' + CAST(GETDATE() AS NVARCHAR(100));
PRINT '''';
PRINT ''---------------------------------------------'';
GO
-- ALTER AVAILABILITY GROUP
:CONNECT #(PrimaryServer)
GO
USE [master];

IF (NOT EXISTS(select * FROM sys.availability_groups WHERE [name] = ''#(AlwaysOnName)''))
BEGIN
PRINT '''';
PRINT '''';
PRINT ''-- We can not continue, Availability group does NOT exist: '';
PRINT '' #(AlwaysOnName) '';
PRINT '''';
PRINT '''';
raiserror(''** We can not continue, Availability group does NOT exist **'', 20, -1) with log;
END

IF (exists(select * FROM sys.availability_groups AS ag INNER JOIN sys.availability_databases_cluster AS adc ON ag.group_id = adc.group_id WHERE adc.database_name = ''#(dbname)'' and ag.name = ''#(AlwaysOnName)''))
BEGIN
PRINT ''---------------------------------------------'';
PRINT ''-- REMOVE DATABASE [#(dbname)] FROM AVAILABILITY GROUP [#(AlwaysOnName)]'';
ALTER AVAILABILITY GROUP [#(AlwaysOnName)] REMOVE DATABASE [#(dbname)];
END
GO
-- Shrink database on primary server
:CONNECT #(PrimaryServer)
GO
USE [master];
GO
PRINT ''---------------------------------------------'';
PRINT ''Set recovery to simple with no wait'';
ALTER DATABASE [#(dbname)] SET RECOVERY SIMPLE WITH NO_WAIT
GO
PRINT ''---------------------------------------------'';
PRINT ''Shrink database on primary server'';
#(SHRINKFILE)
GO
USE [master];
GO
PRINT ''---------------------------------------------'';
PRINT ''Set recovery to full'';
ALTER DATABASE [#(dbname)] SET RECOVERY FULL WITH NO_WAIT;
GO
PRINT ''---------------------------------------------'';
PRINT ''Backup database : 1'';
-- FIX: Database might contain bulk logged changes that have not been backed up.
BACKUP DATABASE [#(dbname)] TO DISK = N''#(FullPathBackupFile)\#(dbname).bak'' WITH NOFORMAT, INIT, NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
GO
PRINT ''---------------------------------------------'';
PRINT ''Add database to availability group'';
ALTER AVAILABILITY GROUP [#(AlwaysOnName)] ADD DATABASE [#(dbname)];
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
GO
PRINT ''---------------------------------------------'';
PRINT ''Backup database : 2'';
-- FIX: This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap.
BACKUP DATABASE [#(dbname)] TO DISK = N''#(FullPathBackupFile)\#(dbname).bak'' WITH NOFORMAT, INIT, NAME = N''Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10;
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
GO
-- Restore database on secondary server
:CONNECT #(SecondaryServer)
GO
IF (@@servername = ''#(SecondaryServer)'')
BEGIN
PRINT ''---------------------------------------------'';
-- FIX : Exclusive access could not be obtained because the database is in use
WAITFOR DELAY ''00:00:10'';
PRINT ''Restore database'';
RESTORE DATABASE [#(dbname)] FROM DISK = N''#(FullPathBackupFile)\#(dbname).bak'' WITH NORECOVERY, NOUNLOAD, STATS = 5;
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
END
-------------------------------------
GO
-- Backup transaction database on primary server
:CONNECT #(PrimaryServer)
GO
IF (@@servername = ''#(PrimaryServer)'')
BEGIN
PRINT ''---------------------------------------------'';
PRINT ''Backup database log'';
BACKUP LOG [#(dbname)] TO DISK = N''#(FullPathBackupFile)\#(dbname).trn'' WITH NOFORMAT, INIT, NOSKIP, NOREWIND, NOUNLOAD, STATS = 10;
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
END

GO
-- Restore transaction database
:CONNECT #(SecondaryServer)
GO
IF (@@servername = ''#(SecondaryServer)'')
BEGIN
PRINT ''---------------------------------------------'';
PRINT ''Restore database log'';
RESTORE LOG [#(dbname)] FROM DISK = N''#(FullPathBackupFile)\#(dbname).trn'' WITH NORECOVERY, NOUNLOAD, STATS = 5;
IF (@@error <> 0) raiserror(''** We can not continue, you MUST check error. Maybe, it can have changed the database. **'', 20, -1) with log;
END

---------------------------------------------
GO
:CONNECT #(SecondaryServer)
GO
IF (@@servername = ''#(SecondaryServer)'')
BEGIN
PRINT ''---------------------------------------------'';
PRINT ''Wait for the replica to start communicating'';
-- Wait for the replica to start communicating
begin try
declare @conn bit
declare @count int
declare @replica_id uniqueidentifier
declare @group_id uniqueidentifier
set @conn = 0
set @count = 30 -- wait for 5 minutes

if (serverproperty(''IsHadrEnabled'') = 1)
and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty(''ComputerNamePhysicalNetBIOS'') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)
and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)
begin
select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N''#(AlwaysOnName)''
select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id
while @conn <> 1 and @count > 0
begin
set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)
if @conn = 1
begin
-- exit loop when the replica is connected, or if the query cannot find the replica status
break
end
waitfor delay ''00:00:10''
set @count = @count - 1
end
end
end try
begin catch
-- If the wait loop fails, do not stop execution of the alter database statement
end catch

PRINT ''---------------------------------------------'';
PRINT ''Alter database set HADR'';
ALTER DATABASE [#(dbname)] SET HADR AVAILABILITY GROUP = [#(AlwaysOnName)];
END
GO
PRINT ''---------------------------------------------'';
PRINT ''-- '' + CAST(GETDATE() AS NVARCHAR(100));
PRINT ''-- done! '';

GO'
;

SET @sql = replace(@sql,'#(SHRINKFILE)' , @SHRINKFILE);
SET @sql = replace(@sql,'#(AlwaysOnName)' , @AlwaysOnName);
SET @sql = replace(@sql,'#(getdate)' , CAST(GETDATE() AS NVARCHAR(100)));
SET @sql = replace(@sql,'#(dbname)' , @dbname);
SET @sql = replace(@sql,'#(PrimaryServer)' , @PrimaryServer);
SET @sql = replace(@sql,'#(SecondaryServer)' , @SecondaryServer);
SET @sql = replace(@sql,'#(FullPathBackupFile)', @FullPathBackupFile);

declare @pText nvarchar(max) = @sql;
declare @pTextNewLine nvarchar(2) = CHAR(13) + CHAR(10); -- ** it is a good practice to use CR and LF together. CHAR(13) + CHAR(10)
declare @pTextMax int = 256;  -- ** default maximum number caracters displayed - SSMS -- but you can change it
declare @pTextPrint nvarchar(max);
declare @pTextCR Int
select @pText = @pText + @pTextNewLine;
while (LEN(@pText) > 0)
begin

 SELECT @pTextCR = CHARINDEX(@pTextNewLine, @pText);
 IF ((@pTextCR =-1) OR (@pTextCR > @pTextMax)) SELECT @pTextCR = @pTextMax;
 
 select @pTextPrint = SUBSTRING(@pText,0,@pTextCR),
 @pText = SUBSTRING(@pText, @pTextCR+len(@pTextNewLine), len(@sql));
 
 print @pTextPrint

end
END

This create a new script, copy and paste on new query, and execute with SQLCMD mode.

If you have any error, you can run over and over again and expecting a good results without errors. At this point, it's safe script.

Sources:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-add-a-database
https://blog.sqlauthority.com/2015/02/08/interview-question-of-the-week-006-is-shrinking-database-good-or-bad/
https://blog.sqlauthority.com/2015/08/08/sql-server-adding-file-to-database-in-alwayson-availability-group/
https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

When we have un have an unhealthy data synchronization state, we can always use graphic interface to resume data movement.

This script resume when the database need it, but you must connect on secondary server to run it.

-- You can use SQLCMD Mode
-- :CONNECT <MyServer>
PRINT '-- SQL Script to SET HADR RESUME'
PRINT ''
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);

DECLARE @dbname nvarchar(255);
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT sys.databases.[name] INTO #db
FROM sys.dm_hadr_database_replica_states INNER JOIN sys.databases
    ON    sys.dm_hadr_database_replica_states.database_id = sys.databases.database_id
    WHERE
        sys.dm_hadr_database_replica_states.synchronization_health = 0
        AND sys.dm_hadr_database_replica_states.is_primary_replica = 0
        AND sys.databases.state_desc = 'ONLINE'
DECLARE @SqlTemplate nvarchar(MAX) = 'ALTER DATABASE [#(name)] SET HADR RESUME;'
WHILE ((SELECT count(*)    FROM #db)>0)
BEGIN

    SET @sql = '';
    SELECT TOP 1 @dbname = name
    ,            @sql = REPLACE(@SqlTemplate,'#(name)', name)
    FROM #db f ORDER BY f.name;
    DELETE FROM #db WHERE name = @dbname;

    IF NOT(@sql = '')
    BEGIN
        SET @sql = 'PRINT ''Database:'+@dbname+''';' + CHAR(13) + CHAR(10)+@sql;
        PRINT @sql;
        --EXEC (@sql);
    END

END

Lire la suite de Resuming data movement on database HADR