Transaction Log Backups

Full Transaction Log Destination

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...

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

dbcc shrinkfile(myDatabase_log, 8000)

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

Troubleshooting

To identify the amount of free space in your transaction log...

use [myDatabase]

SELECT name,

       size / 128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) / 128.0 AS AvailableSpaceInMB

  FROM sys.database_files;

Log not truncating

Move Transaction Logs

ALTER DATABASE myDatabase SET OFFLINE;

ALTER DATABASE myDatabase

MODIFY FILE (Name = myDatabase_Log, Filename = 'F:\NewDir\myDatabase.ldf')

Move the file in the filesystem

ALTER DATABASE myDatabase SET ONLINE;

RECONFIGURE

Bibliography