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
*/
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