Transaction log full but TL backups is is just 8KB

Question

Transaction log full but TL backups is is just 8KB

askmesql

Valued Member

Points: 63
More actions
October 22, 2019 at 7:49 pm

#3691106

I am in a bit strange situation here.
I have a SQL Cluster that has been working fine, Also databases has been scheduled for a logshipping (every 15 mins)that has been working untill early morning.
My reindexing Job creates a huge log file and my logshipping job usually take care of it. But that day the reindexing job timed out after 7 hrs around 7.30 AM (failing to REORGANIZE a small table usually takes under 5 sec ), usually it takes under 40 Mins.
Here are the sizes of the TL files
2019-10-21 0:32 first LSN: 3438321:3610468:1, last LSN: 3438321:3668661:1, XXXX_20191021063000.trn 6440 KB
2019-10-21 1:15 first LSN: 3438321:3668661:1, last LSN: 3438441:195108:1, XXXX_20191021064501.trn 7,154,091 KB
2019-10-21 1:32 first LSN: 3438441:195108:1, last LSN: 3438483:2365541:1, XXXX_20191021070000.trn 7,671,435 KB
2019-10-21 1:43 first LSN: 3438483:2365541:1, last LSN: 3438491:4172516:1, XXXX_20191021071500.trn 4,071,186 KB
2019-10-21 1:55 first LSN: 3438491:4172516:1, last LSN: 3438491:4172595:1, XXXX_20191021073000.trn 18 KB
2019-10-21 2:04 first LSN: 3438491:4172595:1, last LSN: 3438491:4172595:1, XXXX_20191021074500.trn 9 KB
2019-10-21 2:26 first LSN: 3438491:4172595:1, last LSN: 3438491:4172595:1, XXXX_20191021080001.trn 9 KB
2019-10-21 2:34 first LSN: 3438491:4172595:1, last LSN: 3438491:4172595:1, XXXX_20191021081500.trn 9 KB
2019-10-21 2:56 first LSN: 3438491:4172595:1, last LSN: 3438491:4172595:1, XXXX_20191021083000.trn 9 KB
2019-10-21 3:05 first LSN: 3438491:4172595:1, last LSN: 3438491:4172595:1, XXXX_20191021084501.trn 9 KB
2019-10-21 3:26 first LSN: 3438491:4172595:1, last LSN: 3438491:4172595:1, XXXX_20191021090001.trn 9 KB
2019-10-21 3:36 first LSN: 3438491:4172595:1, last LSN: 3438491:4172595:1, XXXX_20191021091500.trn 9 KB
2019-10-21 3:55 first LSN: 3438491:4172595:1, last LSN: 3438491:4172595:1, XXXX_20191021093000.trn 9 KB
2019-10-21 4:04 first LSN: 3438491:4172595:1, last LSN: 3438491:4172595:1, XXXX_20191021094500.trn 9 KB
2019-10-21 4:25 first LSN: 3438491:4172595:1, last LSN: 3438491:4172595:1, XXXX_20191021100001.trn 9 KB
2019-10-21 4:34 first LSN: 3438491:4172595:1, last LSN: 3438491:4172595:1, XXXX_20191021101500.trn 9 KB
And I have been getting the following error since 1.20 AM
The transaction log for database 'XXXX' is full due to 'ACTIVE_TRANSACTION'.
Any idea why my transaction backups are empty from 1:55 am , while I was receiving the log full error.
I didnt see any blocking tasks during that time (I do have a scheduled task that checks for blocking tasks)

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steve T SSCommitted Points: 1994 More actions · Answer 1

Not sure if this is the same issue, but we have AG group that the T-LOG full on the receiving side. While we had plenty of room on the primary AG database, the secondary did not. The records are sitting waiting to be sent to the secondary. I recall expanding the database log file on the secondary.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 2

Have you checked to see if you actually do have an open transaction on the database? And, yeah... I agree with Steve T in his suggestion. I've not had to be the one to fix it but I've seen others with that problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DinoRS SSCrazy Points: 2683 More actions · Answer 3

reorganizing an index is written to the transaction log and is a transaction which means until it finishes with the REORGANIZE (or it's being killed) taking transaction log backups will only affect things which are not in an open transaction state. Once the REORGANIZE finishes, the next transaction log backup should be rather huge. Reference

And no, AAG behaviour is not the same as a cluster service, you don't do log replay in a WSFC, you failover the service and volumes.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

run a dbcc opentran() command against your source database - this might point you in the right direction

if you have one uncommitted transaction in that database then anything that uses the t-log will suffer (replication, log shipping, log backups etc)

MVDBA

askmesql Valued Member Points: 63 More actions · Answer 5

Thanks for the reply. Unfortunately I didnt get any time to investigate, my coworker failed over the instance before i got any chance.

As I mentioned, the failed REORGANIZE statement, usually takes under 5 sec to do its job and also that table /index is not a frequently used. table in question has only 600K records.

Eventhough i posted the restore status log here, all the transaction logs taken during this period is not empty. The log file size was limited to 100GB though.

Steve T SSCommitted Points: 1994 More actions · Answer 6

DinoRS wrote:

And no, AAG behaviour is not the same as a cluster service, you don't do log replay in a WSFC, you failover the service and volumes.

I agree it is not, but they mention log shipping which indicates that they probably have replication or a mirror database(s) on a different system.