How to Shrink Log File

Sometimes after one or more large transactions, the t-log (transaction log) will become full. In these particular cases you may receive an error message indicating the transaction log is full.

In order to alleviate this issue, you need to find the names of the transaction logs on your system and then shrink them.

To find the names, execute the following statement:

[cc lang=”sql”]
SELECT name
FROM sys.database_files
WHERE TYPE = 1
[/cc]

Once you’ve found the names of your transaction logs, use them in the DBCC SHRINKFILE command in order to make them smaller.

DBCC SHRINKFILE(‘AdventureWorksLT2008_Log’);

If your databases are in full recovery mode, then using this methodology will shrink the transaction logs while retaining full recovery mode.

A riskier, yet highier yielding way to free disk space is to utilize:

[cc lang=”sql”]
— database name below
BACKUP LOG
AdventureWorksLT2008 — database name
WITH TRUNCATE_ONLY

— t-log name below
DBCC SHRINKFILE
(
‘AdventureWorksLT2008_Log’ — transaction log name
,1
)
[/cc]

While this command will shrink the transaction log to a greater degree, it will drop the previous transactions since the last T-Log backup. Essentially changing the recovery model of the database to simple since the last transaction log backup. Depending upon the backup schedule you have, this may or may not be risky. If you backup the transaction logs every hour, then the max possible data loss (should a complete loss occur) would be one hours worth of data. While the chance of this are very slim, it should be noted that with the truncate_only option, point-in-time recovery will not be supported since the last t-log backup.