Full/diff backup does not clear transaction log ("clear" is sometimes referred as "truncate" and actually means "mark parts of tran log as free for reuse". Clearing tran log DOES NOT make tran log file smaller!). Only transaction log backup can clear transaction log. Only shrink can shrink the tran log size if certain conditions are met.

Transaction log backup consists just of the log records after the previous transaction log backup. They have nothing to do with tran log size, as strange it seems! So saying "I can't run them very often because they're too big" is not true - fortunately for you. Log file size is big, but log backup size is not (except the initial tran log backup which really will be around 80GB)!

This is the query which will show you VLF's (virtual log files - logical units of transaction log that can be marked as "free to reuse"):

declare @vlfs table

(--RecoveryUnitId int, -- sql2012 only

FileId int,

FileSize bigint, -- VLF size in bytes

StartOffset bigint, -- VLF offset in bytes from beginning of transaction log

FSeqNo int,

Status int,

Parity tinyint,

CreateLSN decimal(25,0)

)

insert into @vlfs

--(RecoveryUnitId, -- sql2012 only

--FileId, FileSize, StartOffset, FSeqNo, Status, Parity, CreateLSN

--)

exec('DBCC LOGINFO () WITH TABLERESULTS, NO_INFOMSGS')

SELECT

file_id = v.FileId, logical_name = f.name, f.physical_name, log_size_kb = REPLACE(CONVERT(varchar, f.size*$8, 1), '.00', ''),

vlf_size_kb = REPLACE(CONVERT(varchar, v.FileSize/$1024, 1), '.00', ''),

vlf_physical_offset = v.StartOffset,

WriteSequenceNo = ROW_NUMBER() OVER(ORDER BY v.FSeqNo),

Status = CASE WHEN v.Status=2 THEN 'ACTIVE' ELSE 'INACTIVE (free)' END,

v.CreateLSN

FROM @vlfs v

JOIN sys.database_files f on f.file_id = v.FileId

order by v.StartOffset

You will see which VLF's are free, and their sizes. You should not have more than 50 VLF's, so possibly you should do some tran. log optimizations after this situation is resolved.

If you already did not do that very slow initial 80GB tran log backup, and in order to avoid it, AND if you can afford to be without point-in-time restore capability until this operation is finished, you can do it with this extra-fast procedure. But again, it WILL BREAK the log backup chain:

- make diff backup. That is the point you can recover to if anything goes wrong. DIFF backup is much faster than initial backup of 80GB tran. log., and also much faster and smaller than full backup.

- switch db to simple recovery model (that breaks the log backup chain)

- checkpoint and shrink the log file (should be very fast operation)

- switch db to full recovery model

- make differential backup to initialize new log backup chain, and actually start behaving like full recovery model. Why diff backup and not full? Diff will be faster than full, so you get less time spent exposed in simple recovery model. From now on you take regular transaction log backups as usual, and as often is required. You can recover point-in-time from time that diff backup finished and on.

SCRIPT that as a whole unit, and TEST before you run it.

That is the fastest method and the process should be finished in minutes (as long as two diff backups take plus few seconds), AND you do not have to find 80GB of space for initial tran log backup.

If you do not want to lose point-in-time recovery capability, and are willing to wait spending your time in a much longer process, do this procedure (no log backup chain is lost here):

1) manually start the job that takes transaction log backup and wait for it to finish OR do it yourself (be careful to change filename in each iteration):

BACKUP LOG MYDatabase TO DISK='D:\transactionLogBackup1.trn'

This step will take a VERY long time for the initial 80GB backup. Log backups after that will be fast and small (relatively to initial log backup) even the log itself is still 80GB.

2) checkpoint

3) DBCC SHRINKFILE('MyTransactionLogName', 1000) -- 1000 MB. Set as you like.

Repeat those 1,2,3 until the log is shrinked to desired size, probably 1-2 times will be enough.

Use backup compression (if available) for all the backup steps (diff and tran log backups) to speed up the process.

After that, optimize the number of VLF's, log size, and growth size, but that's another story.

Good luck!