We help IT Professionals succeed at work.

Can't truncate logs

jssong2000
jssong2000 asked
on
One of our customer can't truncate logs, the following is the issue:
Appreciated!!!

The logs are HUGE – Apparently, and I didn’t realize this, the backup software will easily backup the DB’s, but doesn’t touch the transaction logs. One was 18.5 GB and the other was almost 26 GB. I did a SQL backup of them last night, and I’m attempting to truncate them, but receiving
“Cannot shrink log file 2 (Consumer_log) because of minimum log space required.
Comment
Watch Question

CERTIFIED EXPERT
Commented:
BAck trh transaction logs up before truncating them.
I heard before there is a bug with backing up the transaction logs, so you must backup twice.. but after you do so, just simply run the following:

USE DatabaseName
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO
AnujSQL Server DBA
Top Expert 2011

Commented:
For SQL Server 2008, backing up the transaction log files will truncate the inactive portion of the log files and this space is reused. In SQL Server 2005 and below you need to truncate it as follows

BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

The above command will fail for SQL Server 2008.

Then you need to shrink the Log file to desired size using DBCC SHRINKFILE(<LogFileName>,2048) this shrinks the file to 2GB. If that doesn't work run CHECKPOINT, then shrink the log file.

Author

Commented:
My customer made a backup and could shrink now. And the file is only 564KB. Thanks!
CERTIFIED EXPERT

Commented:
You're welcome.

This is a very common issue. For some reason it's the default setting of the database to do full recovery, which is a good thing, but nowhere does it tell you that you need to do a log backup along with a database backup when in that mode.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.