Urgent: Log Files Won't Truncate

Question

Urgent: Log Files Won't Truncate

joshdbguy

SSCertifiable

Points: 7707
More actions
September 3, 2015 at 10:38 am

#302879

There was an issue early this morning that caused log files to go crazy. I believe it was related to some runaway transactions on one of our servers. I'm attempting to shrink log files but every time I run a t-log backup the log file doesn't truncate.. My guess is that there's something basically uncommitted or stuck and the t-log won't shrink. My next step, if this would work, would be to restart the sql server service and hopefully fix that issue. Any thoughts, what should I do?

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

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

joshdbguy SSCertifiable Points: 7707 More actions · Answer 1

Another thought, which I just tested is to switch the databases to simple recovery and then back to full. Which seemed to work for on of my test db's.

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

http://www.sqlservercentral.com/articles/Transaction+Logs/72488/

Switching to simple recovery will only do anything if the cause of the logs not truncating was the lack of a log backup. If that was the cause, then doing a log backup would fix it, without the side effects of breaking your log chain and leaving you exposed to data loss until the next full/diff backup.

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

joshdbguy SSCertifiable Points: 7707 More actions · Answer 3

GilaMonster (9/3/2015)
http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
Switching to simple recovery will only do anything if the cause of the logs not truncating was the lack of a log backup. If that was the cause, then doing a log backup would fix it, without the side effects of breaking your log chain and leaving you exposed to data loss until the next full/diff backup.

Sorry if I wasn't super clear in the original post. T-Log backups are the issue. When I run them, the log file won't truncate. The log chain is already broken so I'm not too worried about that.

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

Then the log backups are not the issue. The issue is that something else is preventing log reuse. Switching to simple will do exactly the same as a log backup (in this case nothing)

You need to identify what is preventing the log from being truncated, then 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

joshdbguy SSCertifiable Points: 7707 More actions · Answer 5

Thanks, I figured it out. The Availability Group was the problem. I removed the databases from the AG and ran a backup and free space went back up to 99%.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Sounds like you dealt with the symptom but failed to identify and resolve the problem.

What affect did removing the databases from your AG have on your system(s) and processes?

joshdbguy SSCertifiable Points: 7707 More actions · Answer 7

Maybe you guys can help me identify the issue? So free space dropped to almost 0% on the data/log drive. AG was still running and t-logs wouldn't truncate. My guess is that the AG was attempting to transfer data to the secondary node but failed to do so, causing it to basically lock up the transaction log file.

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

Don't guess. Diagnose and identify the exact problem. Guessing just wastes time.

Problem is by pulling the DB out of the AG, you'll have lost any information that was in AG-related DMVs.

Oh, and you wouldn't have been able to set the DB to simple recovery while it was in an AG.

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

joshdbguy SSCertifiable Points: 7707 More actions · Answer 9

GilaMonster (9/3/2015)
Don't guess. Diagnose and identify the exact problem. Guessing just wastes time.
Problem is by pulling the DB out of the AG, you'll have lost any information that was in AG-related DMVs.
Oh, and you wouldn't have been able to set the DB to simple recovery while it was in an AG.

I just pulled them out and then ran a t-log backup. Before I did that though, I ran a normal backup. I re-added them to the AG and everything is working.

What's the best place to start to determine why the AG was the problem? There are no errors in the AG event file other than a single timeout error.

I also see Flush failures and ReportIO Errors for backups in the sql server log.