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!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Only a Backup Log operation will truncate the log. Can you post the correct error message?
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.
Vitor MontalvãoMSSQL 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

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 you know of a way to look more granularly for the actual Truncate event, as apposed to looking for the log backup event?
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.
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.

Thanks for your input!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.