0

One transaction log file is growing and growing for a couple of days and I can't find out why. Now I figured out that either the way the activities are stored in the sql logfile OR the behaviour of sql server/backup software changed.

The log files have been backed up every 15 minutes by a third party software.

Until a specific point some days ago, the log backup event is visible every 15 mins as it always used to in the log with the following entries (newer first):

BackupLog: SafeCheckpoint done

Backup: Media open

BackupLog: Work estimate done

BackupLog: Log files done

BackupLog: Writing history records

Log was backed up. Database: xy_db, creation date(time): 2014/01/10(03:31:14), first LSN: .............. This is an informational message only. No user action is required.

BackupLog: Writing history records

BackupLog: Finished

After that specific day, there is no more such entry visible. But instead I can see the following log entries indicating something like a log backup as well (newer first):

BackupDatabase: Log files done

Database differential changes were backed up....

Completed file yyy.mdf

Started file xxx.mdf

BackupDatabase: Log files done

BackupDatabase: Database xy_db

I wonder if or why the presentation of the log backups may have changed and if the new (bolded) entries indicate the same action? I have checked

 DBCC TRACESTATUS (3226);

that tells me status = 0 but I can't find any hints in the log that this ever has been changed these days what also makes me unsure if those Backup Log" entries I was used to ever depended on this trace flag?

EDIT: I have checked the trace flags for different servers. the trace flag 3226 is turned off but I can see the Log entries I am used to in the sql server logs. So this seems not to be involved here.

Where are those different / new log entries possibly coming from?

0

1 Answer 1

2

The final reason for this effect was that the Backupexec process responsible to take regular log backups was still present and running (so IT monitoring did not alert anything), but it was kind of internally crashed, so it did not do it's job properly.

In order to address this kind of event I decided to add the log file events and it's metadata details to the SQL Server monitoring. Once every day those information is captured and added to the monitoring information repository, where I can run reports and queries against to see progress and find out abnormal behavior.

This ended in a report about the log files looking similar to this sample. It shows the file size over time of each TL and also the percentage of usage at each point. If point size increases, the file contents grow. If they grow too much, the file size increases. Dramatically file size decrease was caused by manual shrink operations (followed by rolling eyes after it grew and grew again...). Basically log file usage and backups should be aligned in a way it almost never grows.

enter image description here

This is very useful to keep an eye on and eventually could also be used to alert by using this report as a data driven subscription (which I have not tried yet).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.