How to list all occurances of log truncation for SQL database(s)?

Hey all!  My backup solution (Barracuda Backup 490 appliance) has been failing on differential backups of some of our SQL databases (the same 9 databases out of 25 in that particular instance, we have several other SQL instances that are backing up fine on the same backup job).  The jist of the error message as I understand it is that an operation has caused a log truncate, breaking the log chain, and thus breaking the diff backup of the database.

So I always forget just how little I understand SQL until I'm forced to go troubleshoot a problem like this.  I figured this would be easy, but so far I'm coming up empty....what I'm looking for is a simple and concise way to pull a record of all instances where a database was truncated (breaking the log chain) so I can try and figure out what exactly is causing this and evaluate how to move forward.

From my googling, and the responses I'm reading, I'm sure lots of people will have lots of very good suggestions about how to approach this problem and things to check, and I appreciate it all.  But what's most important to me right now, and what I would be really thankful for if anyone can oblige, would be a query, or an Event Viewer filter or something that will give me a time-stamped list of every time a database's log was truncated?  Thanks in advanced!
TatankasaAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Yes, it seems that there's another process that's also backing up your databases.
Interesting that is interfering with your Barracuda Backups. I don't know that software but usually a backup from another process/tool shouldn't have impact on other processes/tools.

You can always query the SQL Server for the performed backups to see if you find what's taking parallel backups. Follow this article if you have troubles to retrieve the information.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Only a Backup Log operation will truncate the log. Can you post the correct error message?
0
 
TatankasaAuthor Commented:
Thanks for the response :)  The error message for each of the 9 databases is: Unable to perform differential backup: an external program has made a full backup of this database. Please run a full backup before attempting another differential backup

So if only a Backup Log operation will truncate it, is there a query or event filter or something that I can run that will show me all of those instances?  I suspect there is another backup application or maintenance plan at play here.

I understand there's other ways to approach the problem, but I would really love to be able to pull the list and say 'there, that was the event that broke the log chain' and then work from there to figure out what triggered it, where it came from.  Or maybe I'll find there was no truncate, and the backup appliance is not working correctly.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
TatankasaAuthor Commented:
Hmm, interesting...doesn't show any log backups, either some show as type 'Database' and some 'NULL'.  I wonder if the Barracuda is doing something odd, I'll have to look into that.

I think the logs are getting truncated though...do you know of a way to look more granularly for the actual Truncate event, as apposed to looking for the log backup event?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Only way the logs getting truncated without a backup log is when the database is configured for Simple Recovery Models. Full and Bulk-Logged recovery models require transaction log backups to truncate the log. You can learn more about Recovery Models here.
0
 
TatankasaAuthor Commented:
Ok, cool, that's basically the same answers I was seeing online.  I am making some progress though, I found some Barracuda documentation that seems to indicate that it can do 'differential' backups without performing an actual log backup, which would explain why I'm not seeing a log backup.  It also indicates I have to stop taking local disk backups with SQL maintenance, so I'll try that.

https://campus.barracuda.com/product/backup/doc/44436829/best-practices-for-microsoft-sql-server/

Thanks for your input!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.