Truncating very large transaction log in full recovery mode

While I always configure transaction log backups for every database in non-simple recovery model, and to compliment it I also have monitoring in place if transaction log doesn't get backed up, which does happen on rare occasions. For instance in my current organization where all applications/databases are third party apps with no in-house developed apps, once in a while an app/user may run a huge transaction, it could be that they decided to purge almost all historical data from a 500TB database and delete 450TB data from it all in a single transaction, leaving a huge transaction log and not enough available disk space on the backup drive (separate issue/topic).     

Per best practice and for almost any conceivable argument, its preferred that you take a transaction log backup and then shrink the log file/s. But in an emergency situation you could use the following SQL script to quickly transaction the transaction log.  I believe the script is self-explanatory but I missed anything or have questions, please feel free to post a comment here and I will go my best to get to back you. 

/* CAUTION:
USE THIS ONLY IN EMERGENCY WHERE THE TRANSACTION LOG HAS NOT BEEN BACKED 

IN A VERY LONG TIME AND HAS GROWN TO BE TOO BIG

The script does the following in order to shrink the transaction log file:
- Ensure the db is not running in Simple recovery mode
- Store the database recovery mode into a variable
- Change recovery mode to SIMPLE
- Issues checkpoint command
- Wait 2 seconds
- Shrink the log file
- Wait 2 seconds
- Change recovery mode back to original value

IMPORTANT: This script does not take a full or differential database backup so its crucial that you take a full or differential backup to re-set the transaction log chain. Otherwise any attempt to take a transaction log backup will fail, creating more problems.

*/

declare @db nvarchar(100) = 'MyDB'               -- Specify name of the database here
declare @target_log_size_mb nvarchar(20) = 64    -- if DB has multiple log files, each log file will be of this size
declare @recovery_mode nvarchar(100)
declare @sql nvarchar(4000)

select @recovery_mode = recovery_model_desc from sys.databases where name = @db

if @recovery_mode='SIMPLE'
   raiserror('Error: Use this script only on database in Full recovery mode.', 16, 1)
else if databasepropertyex(@db, 'Updateability')='READ_ONLY'
   raiserror('Error: Database is not updatable. Please ensure that the database is not read only, mirrored or otherwise inaccessible.', 16, 1)
else
BEGIN
		set @sql = '
		USE [master]
		ALTER DATABASE [' + @db + '] SET RECOVERY SIMPLE WITH NO_WAIT
		USE [' + @db + ']
		WAITFOR DELAY ''00:00:02''
		checkpoint
		WAITFOR DELAY ''00:00:02''

		-- open a cursor so we can iterate through and shrink all log files
		declare @log_file_name sysname
                declare c1 cursor for
		select name from sys.database_files where type=1
		open c1
		fetch c1 into @log_file_name
		while @@FETCH_STATUS = 0
		begin
			   print @log_file_name
			   DBCC SHRINKFILE (@log_file_name, ' + @target_log_size_mb +')
			   fetch c1 into @log_file_name
		end
		close c1
		deallocate c1

		WAITFOR DELAY ''00:00:02''
		USE [master]
		ALTER DATABASE [' + @db + '] SET RECOVERY ' + @recovery_mode + ' WITH NO_WAIT
		'
		print @sql
		exec sp_executesql @sql
END

For database participating in an AlwaysOn Availability Group:

/* CAUTION:
USE THIS ONLY IN EMERGENCY WHERE THE TRANSACTION LOG HAS 
NOT BEEN BACKED IN A VERY LONG TIME AND HAS GROWN TO BE TOO BIG

This script generates a TSQL script that does the 
following in order to shrink the transaction log file:

- Ensure the db is not running in Simple recovery mode
- If the DB is participating in an Availablity Group then
  ensure that we are on the primary replica server

- Store the database recovery mode into a variable
- Store the Availability Group name into a variable
- Change recovery mode to SIMPLE
- Issues checkpoint command
- Wait x number of seconds
- Shrink the log file/s
- Wait x number of seconds
- Change recovery mode back to original value

- AG settings, If applicable
  Backup the database 
  Add the database back to the AG
  Provide TSQL to drop the database on secondary replicas

WHAT IT DOESN'T DO:

- It won't delete/drop the database on the secondary replicas

*/

-- Specify the database name here

DECLARE @db nvarchar(100)  = 'VLDB';

-- If DB has multiple log files, each log file will be of this size
DECLARE @target_log_size_mb nvarchar(20) = 64;    

DECLARE @recovery_mode nvarchar(100);
DECLARE @sql nvarchar(4000);
DECLARE @ag_name nvarchar(100);
DECLARE @is_primary_replica int;

SELECT
		@recovery_mode      = recovery_model_desc, 
		@is_primary_replica = agrs.is_primary_replica, 
		@ag_name            = ag.name

FROM sys.databases d
left join sys.dm_hadr_database_replica_states agrs
ON d.database_id = agrs.database_id and agrs.is_local = 1
left join sys.availability_groups ag
ON ag.group_id = agrs.group_id
WHERE d.name =  @db;


IF DB_ID(@db) IS NULL
	RAISERROR('No database found with name Database %s.', 16, 1, @db);
ELSE IF @recovery_mode='SIMPLE'
	RAISERROR('Error: Use this script only on database in Full recovery mode.', 16, 1);
ELSE IF databasepropertyex(@db, 'Updateability')='READ_ONLY'
	RAISERROR('Error: Database is not updatable. Please ensure that the database is not read only, mirrored or otherwise inaccessible.', 16, 1);
ELSE IF @ag_name is not null and @is_primary_replica = 0
BEGIN
	RAISERROR('Database %s is participating in an AlwaysOn Availablity Group.', 16, 1, @db);
	RAISERROR('This must be run on the primary replica server.', 16, 1);

END
ELSE IF @ag_name is null
BEGIN
	RAISERROR('Database %s is not part of an AlwaysOn Availablity Group.', 16, 1, @db);
END

ELSE
BEGIN
		SET @sql ='';
		IF  @ag_name is not null and @is_primary_replica = 1
		SET @sql = @sql + '
		/* EXECUTE ON THE PRIMARY REPLICA SERVER */

		RAISERROR(''INFO: Removing database [%s] from Availability Group [%s]...'', 10, 1, ''' + @db + ''', ''' + @ag_name +''')  WITH NOWAIT;

		USE [master]
		ALTER AVAILABILITY GROUP [' + @ag_name + '] REMOVE DATABASE [' + @db + '];'

		SET @sql = @sql  + '
		RAISERROR(''INFO: Change database recovery model to SIMPLE....'', 10, 1) WITH NOWAIT
		USE [master];
		ALTER DATABASE [' + @db + '] SET RECOVERY SIMPLE WITH NO_WAIT;
		USE [' + @db + '];
		WAITFOR DELAY ''00:00:01''
		CHECKPOINT;		WAITFOR DELAY ''00:00:01'';

		-- open a cursor so we can iterate through and shrink all log files
		RAISERROR(''INFO: Truncate all transaction log files.....'', 10, 1) WITH NOWAIT;
		DECLARE @log_file_name sysname;
          DECLARE c1 cursor for
		SELECT name from sys.database_files where type=1;
		OPEN c1;
		FETCH c1 into @log_file_name;
		WHILE @@FETCH_STATUS = 0
		BEGIN
----			   PRINT @log_file_name;
			   RAISERROR(''INFO: Truncating transaction log file %s....'', 10, 1, @log_file_name); 
			   DBCC SHRINKFILE (@log_file_name, ' + @target_log_size_mb +');
			   FETCH c1 into @log_file_name;
		end
		CLOSE c1;
		DEALLOCATE c1;

		WAITFOR DELAY ''00:00:01'';
		RAISERROR(''INFO: Change back the database recovery model to %s...'', 10, 1, ''' + @recovery_mode +''') WITH NOWAIT
		USE [master];
		ALTER DATABASE [' + @db + '] SET RECOVERY ' + @recovery_mode + ' WITH NO_WAIT;

		'
		SET @sql = @sql  + '

		RAISERROR(''INFO: Backup the database...'', 10, 1, ''' + ''') WITH NOWAIT

		BACKUP DATABASE [' + @db + ']  
		TO  DISK = N''O:\' + @db + '_' + CONVERT(nvarchar(10), getdate(), 112) + '.bak'' 
		WITH NOFORMAT, 
			 INIT,  
			 COMPRESSION,  STATS = 10;

		RAISERROR(''INFO: Add the database back to the availability group....'', 10, 1) WITH NOWAIT;
		ALTER AVAILABILITY GROUP [' + @ag_name + '] ADD DATABASE [' + @db + '];

		/* EXECUTE ON THE SECONDARY REPLICAS

		-- DROP THE DATABASE ON THE SECONDARY REPLICAS
		USE MASTER; ALTER AVAILABILITY GROUP [' + @ag_name + '] GRANT CREATE ANY DATABASE;
		
		IF DB_ID(''' + @db + ''') IS NULL
			RAISERROR(''No database found with name Database %s.'', 16, 1, ''' + @db + ''');
		ELSE IF EXISTS (SELECT * FROM SYS. DATABASES
             WHERE NAME = ''' + @db + '''
			   AND STATE_DESC = ''RESTORING'')
          BEGIN
			PRINT ''-- Dropping database: ' + @db + '''
			DROP DATABASE [' + @db + ']
		END
		ELSE
			RAISERROR(''Error: Cannot drop database %s. Ensure that it exists and is not in RESTORING state.'', 16, 1, ''' + @db + ''');


		*/
		'
		PRINT @sql;
END;

Obviously, if a DBA or a more experienced Database Developer is the one running a gigantic transaction (e.g. delete 450TB worth of data from a 500TB database), they would most likely switch the recovery mode to SIMPLE and disable the transaction log backups BEFORE running such a transaction, then change it back to FULL/Bulk-Load mode, take a full backup and re-enable transaction log backup job.

They would also likely to break such operations into a much smaller, manageable batches/transactions.