Consider your recovery options when deciding how frequently to backup Transaction Logs. More frequent Transaction Log backups reduce the potential amount of data lost in a recovery event. Backing up transaction logs every 60 seconds is viable (but do make sure you have scripted and tested recovery because the large number of backup files to process will make this cumbersome to do by hand). Also consider options like AlwaysOn Availability Groups.

If you need to take a transaction log backup to a different location (maybe you are hitting space constraints with the default backup location)...

BACKUP LOG [myDatabase]

TO DISK = N'E:\SQLBackups\myDatabase_Emergency.trn'

WITH

COMPRESSION,

CHECKSUM,

STATS = 5;

GO

To check backup history...

SELECT TOP (20)

bs.database_name,

bs.backup_start_date,

bs.backup_finish_date,

bs.type AS backup_type,

bs.backup_size / 1024.0 / 1024.0 AS backup_size_mb,

bmf.physical_device_name

FROM msdb.dbo.backupset AS bs

JOIN msdb.dbo.backupmediafamily AS bmf

ON bs.media_set_id = bmf.media_set_id

WHERE bs.database_name = N'myDatabase'

ORDER BY bs.backup_finish_date DESC;

GO

SELECT

name,

state_desc,

recovery_model_desc,

log_reuse_wait_desc

FROM sys.databases

WHERE name = N'myDatabase';

GO

USE [myDatabase];

GO

SELECT

total_log_size_in_bytes / 1024.0 / 1024.0 AS total_log_mb,

used_log_space_in_bytes / 1024.0 / 1024.0 AS used_log_mb,

used_log_space_in_percent

FROM sys.dm_db_log_space_usage;

GO

Check there is no backup executing...

SELECT

session_id,

command,

status,

percent_complete,

start_time,

estimated_completion_time

FROM sys.dm_exec_requests

WHERE command LIKE N'BACKUP%';

GO

Identify  (and note) which database(s) is to blame...

use [master]

go

dbcc sqlperf(logspace);

Make a transaction log backup (to truncate the log)...

SSMS - SQL Server Agent - Job Activity Monitor - "DatabaseBackup - USER_DATABASES - LOG" - Start Job at Step...

Check for open transactions...

DBCC OPENTRAN (N'myDatabase')l

GO

Reclaim space...

use [myDatabase]

go

sp_helpfile

Work out an appropriate size to shrink the file to. This can be a case of educated guesswork. Your first aim is obviously to get the disk usage back under the alerting threshold (this site can be useful: http://www.calculator.net/percent-calculator.html). It is conceivably possible that this would be better achieved by shrinking several log files rather than just the largest log file. You may consider referring to the output from the dbcc sqlperf(logspace) command you ran earlier which will also have shown the % log space used. Refer to the output from before the backup as after a backup this % will be low (unless there is an active transaction which prevented the backup from freeing up space - which may warrant further investigation). It may be that a full transaction log destination should actually be resolved by adding storage. Certainly, if you have to reclaim space on a regular basis, you should consider increasing the disk size. If you can find no evidence to suggest otherwise, a size of 7000MB or 8000MB is often a good starting point. Be aware that making the log file too small will cause a performance hit as the log regrows back to its natural size.

use [myDatabase]

go

checkpoint;

go

dbcc shrinkfile(myDatabase_log, 8000);

go

If the file has not shrunk repeat the backup and shrink steps again.