Transaction log filling up issue.

Question

Transaction log filling up issue.

Rick-153145

SSCrazy

Points: 2823
More actions
January 8, 2019 at 3:42 am

#416608

Hi,
I am seeing a bit of a strange issue on a lot of servers in the place I am currently working. They have Commvault doing full backups every day, but not transaction logs, so they are taking full and then transaction log backups locally to try to keep things in check. The issue is the logs are filling up regardless. Now, I have an idea that it may be due to LSN mismatches due to the commvault backup (the full backup schedule is sometimes before, but mostly after local backups) and the next Log backup after the full, so therefore the log isn't checkpointing, but I can't seem to find anything on this issue anywhere. Does anyone know if this would be the case or could point me in the right direction? I have said that commvault should just be backing up the local copies of the file, but I have to back that up with something (no pun intended).

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

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

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 1

Rick-153145 - Tuesday, January 8, 2019 3:42 AM
Hi,
I am seeing a bit of a strange issue on a lot of servers in the place I am currently working. They have Commvault doing full backups every day, but not transaction logs, so they are taking full and then transaction log backups locally to try to keep things in check. The issue is the logs are filling up regardless. Now, I have an idea that it may be due to LSN mismatches due to the commvault backup (the full backup schedule is sometimes before, but mostly after local backups) and the next Log backup after the full, so therefore the log isn't checkpointing, but I can't seem to find anything on this issue anywhere. Does anyone know if this would be the case or could point me in the right direction? I have said that commvault should just be backing up the local copies of the file, but I have to back that up with something (no pun intended).

Is the commvault is taking a native SQL backup. Check the following, how many log backup have taken for the database and make sure you are running frequent log backup in the full mode for the high transaction databases.

SELECT top 100 b.server_name ,b.database_name,b.user_name, f.physical_device_name,
b.backup_finish_date,b.backup_size /1024/1024 AS size_MB,b.type,
b.is_copy_only--,compressed_backup_size/1024/1024 AS C_size_MB
FROM MSDB.DBO.BACKUPMEDIAFAMILY F
JOIN MSDB.DBO.BACKUPSET B
ON (f.media_set_id=b.media_set_id)
--WHERE database_name='test'
AND B.type='L'
--and b.backup_finish_date >='2018-08-02 22:37:50.000'
ORDER BY b.backup_finish_date DESC

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Rick-153145 SSCrazy Points: 2823 More actions · Answer 2

Rick-153145

SSCrazy

Points: 2823

January 8, 2019 at 6:26 am

#2018276

At the moment it is, this is what I want to stop.

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 3

Full backups and log backups are not directly connected (Differentials on the other hand...). Just talking about full backups, whether from commvault or not, shouldn't be affecting log backups. Instead, it sounds like the log backups either are not occurring or are not occurring frequently enough. If you're doing them once an hour (just as an example), maybe every fifteen minutes would work better.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 4

One exception to what I just typed, the very first full backup and the very first log backup ever taken on a given database are kind of linked. However, once that first full backup is done, all subsequent full backups are completely divorced from the logs.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Rick-153145 SSCrazy Points: 2823 More actions · Answer 5

Thanks Grant, that's helpful, but not in the way I hoped. 😉 I just can't get my head around what is occurring as log backups are frequent enough, but the log are still growing and not emptying as they should, to me it just seems like they aren't check-pointing correctly, at least I can't blame commvault for it, unless I somehow find they are doing diffs. Will investigate more.

Grant Fritchey SSC Guru Points: 398715 More actions · Answer 6

Take one database and try running a full backup on it, then running a log backup (both manually, both using standard SQL Server BACKUP commands). Maybe that first full backup hasn't been initialized. Otherwise... replication, mirroring, availability groups, other settings that can mark transactions such that they're not cleared as complete from the log.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 7

Rick-153145 - Tuesday, January 8, 2019 8:40 AM
Thanks Grant, that's helpful, but not in the way I hoped. 😉 I just can't get my head around what is occurring as log backups are frequent enough, but the log are still growing and not emptying as they should, to me it just seems like they aren't check-pointing correctly, at least I can't blame commvault for it, unless I somehow find they are doing diffs. Will investigate more.

Can you run this and let us know the wait of transaction log.

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases name='DBname'

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Toby Harman SSCarpal Tunnel Points: 4168 More actions · Answer 8

So there are a couple of things that block the re-use of a transaction log.

Firstly, backups prevent the re-use of the transaction log. Backups need the database to be in a consistent state, with no new changes applied after the backup starts, so the state of each block at the time the backup starts is written to the transaction log in case it's needed by the backup. So do you have a backup that is still listed as "running" somewhere?
Secondly, long running transactions can keep your transaction log open. Use dbcc opentran() in the context of the database you are having problems with to check for a stray session.

You might want to try and persuade either CommVault OR the local backups to do a COPY-ONLY backup just to avoid the problem you are suggesting, but as Grant says above, once you start log backups, unless you change the recovery model the transaction logs are nose-to-tail regardless of other full backups. Whatever you do, don't make them BOTH COPY-ONLY or you will have to restore all your transaction logs to recover

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

Toby Harman - Tuesday, January 8, 2019 10:24 PM
You might want to try and persuade either CommVault OR the local backups to do a COPY-ONLY backup just to avoid the problem you are suggesting

Copy only log backups are near-useless, they can't be restored from alone. Useful if you need a log backup to bring a restored copy of the DB up to date without waiting for the next scheduled log backup, but that's about it.

Full backups DO NOT and never have affected the log chain, and so there's absolutely no need to suggest copy only full backups to avoid interfering with logs. Copy only on a full backup means it doesn't reset the differential base. That is the only difference. There is nothing wrong with two different schedules of full backups (unless you're doing diffs as well).

Firstly, backups prevent the re-use of the transaction log. Backups need the database to be in a consistent state, with no new changes applied after the backup starts, so the state of each block at the time the backup starts is written to the transaction log in case it's needed by the backup

Full backups do indeed prevent log reuse while they're running, however backups don't write anything to the log (well, other than the DCM page changes they make and the checkpoints they cause). Rather the portion of the log needed to make a restored copy consistent is included in the full backup at the end, hence why the log can't be discarded during the 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