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.

One comment
Anni 23 Aug 2015 at 4:53 pm

Hi,
We are taking full backups of our DB every day and maintaining the last 10 backups. We observed that the ldf file is growing in size. The question is is it OK to truncate the ldf file as we have the latest backup available in case some thing goes wrong. What is the best way to handle this scenario? We don’t want to do incremental backups.

Thank you.
Anni

Featured Articles

 Site Author

  • Thanks for visiting!