Log file full-Unaable to run Backup log

Question

Log file full-Unaable to run Backup log

coolchaitu

SSChampion

Points: 10332
More actions
November 16, 2015 at 10:11 am

#399332

Good morning Experts,
The transaction of log of a database got full.It is in FULL recovery model. I am unable to run backup log statement as there is no space to backup the log. Could you please advise on how to fix the issue.
December 7, 2015 at 10:14 pm

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734564 More actions · Answer 1

Add space to the disk or add a new log file on another drive. Then backup the log to that location.

If you can't do anything else, then you can use the WITH TRUNCATE option to clear the log, but be sure that you then run a full backup ASAP.

You should also read Managing Transaction Logs[/url]

Brandie Tarvin SSC Guru Points: 173003 More actions · Answer 2

And if you happen to already be backing up the transaction log, then you may have bigger issues than just a full disk drive. Such as a runaway process that's filling up said transaction log.

So do what Steve advised, make sure you have a regular backup plan for the transaction log in place, then search for anything that could be runaway processes (assuming your transaction log has already been backed up on a regular basis).

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Steve Jones - SSC Editor (11/16/2015)
If you can't do anything else, then you can use the WITH TRUNCATE option to clear the log

Not on SQL 2008. WITH TRUNCATE_ONLY throws an error on all versions of SQL after 2005.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

coolchaitu (11/16/2015)
Could you please advise on how to fix the issue.

The first thing you need to do is find out what the data loss allowance for this database is, and whether there are already scheduled log backups. Also, you need to determine why the log is full. Taking a log backup will only help if the reason for the full log is that the DB needs a log backup.

Take a look here for the reasons why a log might fill: http://www.sqlservercentral.com/articles/Transaction+Logs/72488/

If it is due to no log backup, and the data loss allowance is 24 hours or more, then you can switch the DB to simple recovery model and just rely on full backups. If you need log backups, then either take that log backup to another location (another drive, network share, etc) and shrink the log file to a sensible size.

If log backups are failing, then you need to identify why and fix it, or this will just happen again.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brandie Tarvin SSC Guru Points: 173003 More actions · Answer 5

GilaMonster (11/16/2015)
Steve Jones - SSC Editor (11/16/2015)
If you can't do anything else, then you can use the WITH TRUNCATE option to clear the log
Not on SQL 2008. WITH TRUNCATE_ONLY throws an error on all versions of SQL after 2005.

Good catch, Gail. I totally missed that comment (and forgot about that problem).

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

And it will only do anything if the cause of the full log really is replication. Plus, it'll break transactional replication and require that the subscriptions be reinitialised and it'll cause CDC to miss changes.

The first step in fixing a full log must be to identify why it's full. Not run random statements with side effects hoping one of them will fix the problem.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

coolchaitu SSChampion Points: 10332 More actions · Answer 7

How about changing the recovery model to simple and then change back to Full and then take a fresh backup

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

GilaMonster (11/16/2015)
Also, you need to determine why the log is full. Taking a log backup will only help if the reason for the full log is that the DB needs a log backup.
Take a look here for the reasons why a log might fill: http://www.sqlservercentral.com/articles/Transaction+Logs/72488/

GilaMonster (12/8/2015)
The first step in fixing a full log must be to identify why it's full. Not run random statements with side effects hoping one of them will fix the problem.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass