There are 2 types of recovery for SQL, I dont remember exactly the names but it is the basic one, that you restore tha backup in the time you took it, and the advanzed where you can restore a point in time.

The error you are getting is because you are selecting the option perform incremental backup after the full backup, and that option is only when you have the advanced recovery option enable in the SQL, just unselect that option and thats it.

The MSDB is using Circular Logging so there is no “starting log” or “ending log”.

David Anderson

Windows System Administrator

Scripps Information Services

Desk: (858) 678-6238

eMail: Anderson.david@scrippshealth.org

"…we live in a world where man has walked on the moon. It wasn't a miracle, we just decided to go". - Jim Lovell, Apollo 13 Astronaut

In 6.1 a new feature is been added to deal with the database which has simple recovery mode.

By default incremental backup cannot be performed on simple recovery database, If force incremental option is selected it will try to perform incremental backup after a full.

Edit the dataset for this backup and choose the option "Skip incremental with warning".

Thanks for the tip on the "Skip incremental with warning".  Most of my databases are simple recovery model because that's what the software company set as their standard, so this will be helpful so my logs look cleaner.  I guess this makes the salient question thus: with a simple recovery model, are we really getting good backups, or is there something else that needs to be tweaked in the settings?  I configured my SQL backup just like the rep who installed my Avamar solution told me to, with the options to the dataset as such:

I also set 2 streams, minimum stream size of 256MB based on the general power, memory, and processor of each of my SQL boxes.  Is there anything else I should be looking at?  I just had my Avamar installed literally last week, so just wanting to make sure I start with best-practices and good habits now so my backups (and logs) are clean.  Thanks!

I am also getting these "Unable to get log gap detection data" errors but not just on the msdb database but on all of my databases and all of my databases are set to either "Full" or "Bulk-insert" recovery mode so none of them are "Simple".

The odd thing is that the errors are random meaning one day I get then on all the databases, the next day I get it on may be half of the database, and the number of errored databases bounce up and down each day. 

The one thing I can seem to reproduce everytime is that if I re-run the client via the "Policy" window by selecting the client and clicking "Back Up" it will always fail with the above errors.  But if I do a manual backup via the "Backup and Restore" window and only select the Windows SQL portion of the same client and run the backup with all default settings (just like the default settings in the dataset policy) the backup completes successfully without the errors. 

One other thing I've noticed is that on the databases that give the "Unable to get log gap detection data" the transaction log does not get backed up and therefore can not be truncated until I can do a manual successful backup of that database.

This has been problem from the moment our grid was upgraded from 5.0.2 to 6.1 and I have several SEV 1 tickets open with EMC support and they have engaged their tier 3 support and the developers to figure this out.  It's been 2 weeks and still no resolution other than me doing manual backups everyday.

Ok to give everyone an update and a workaround as the problem is identify that Avamar 6 does NOT like anything touching the SQL databases such as VMware snapshots or any other backup or image base backup that is application aware and uses the SqlServerWriter VSS module or equivalent.  Since I also use VMware's Data Recovery product to do an image base (snapshot) backup of the VMs as an extra layer of protection in case I lose access to my Avamar grid (which is offsite) I had to exclude VMware from ever using SqlServerWriter VSS when it does a snapshot.  This in effect produces a inconsistent SQL database snapshot on VMware side and may be corrupt so restores are not always going to be good but it will at least make Avamar happy and backups complete without the errors.

If your problem are with VMs and you also do any VMware snapshots you can reference VMware KB1031200 as it will tell you how to exclude certain VSS modules when snapshots are made.  Without this workaround EMC Avamar engineering supports response was to NOT create VM snapshots at all which is completely unacceptable as that is one of the main features of VMs.

As far as getting someone to acknowledge this is a problem is going to be futile as EMC Avamar support is now saying is not their problem and it's VMware's problem and VMware support is saying it not their problem as no other backup products have this problem therefore it's an Avamar issue.  You would think two sister companies that are related would communicate and work together, you know what they say about family feuds. This is going to be the longest 'ping pong' game in world history.

The problem stems from the addition of the "Log Gap Detection" feature introduced in Avamar 6.1, this feature basically locks out all other SQL backup products to be used in conjuction with Avamar 6.1.  Avamar's support response is do NOT ever use any other SQL backup products or do any other SQL backups other than with Avamar period.  All other 3rd party SQL backup products aside if you want to do a one off backup of your SQL database using the built-in Microsoft SQL backup that is part of the original Microsoft SQL server installation you CAN NOT according to Avamar support as it will cause a Log Gap Detection error the next time Avamar does it's backup.

If Avamar support/developers were smart they should make the "Log Gap Detection" feature an option via a checkbox like some of the other SQL plugin features.  This way it provides flexiblity for the customers especially those that like to use more than just Avamar to backup their SQL databases.  I suggested this to them and their reply was NO they are not going to do anything nor even consider it case closed!

I just found Avamar's attitude told this to be communistic as after working with them on this for one month their final reply was ONLY use Avamar period and nothing else otherwise it's your problem.  And by the way during the whole one month it was I the customer that troubleshooted and indentified the problem and NOT Avamar support, all they did was insisting I was not doing the Avamar backup right even when I had Avamar support webex in and watch it following their exact instructions.

I've contacted Avamar twice for two different issues and both times I had to give Avamar support a negative rating.  Now on the other hand EMC's Clariion and Celerra support are top notches and I give their support the highest rating as their CS and CE are willing to LISTEN to the customers.  It's like Avamar is not part of the EMC family or it's the step child nobody likes.  Don't get me wrong I love the Avamar product but my two support experiences have left a very sour taste in my mouth.

Hello fl,

I apologize that this has been such a bad experience. 

The Log Gap Detection feature was not added to keep you from using other backup products; it was to keep Avamar from creating bad savesets if you choose to do so.  Prior to Avamar 6.1, a misconfiguration could cause Avamar to report successful backups, when in fact the resulting savesets could not be recovered. 

Microsoft SQL, like most databases, uses a two-stage commit protocol, using transaction logs as the first stage.  SQL Server keeps track of entries in transaction log with LSNs (Logical Sequence Numbers).  SQL Server would need *all* of the transaction entries to do a recovery.  If there is a gap in the chain of logs, a recovery *will* fail.

When a SQL backup is run, depending upon the options selected, log entries are deleted from the SQL transaction log.  This is only done after a backup operation saves the transactions; if a recovery is needed, the backup product recovers the Full saveset, and all of the Incremental and Differential savesets taken since the Full, so the complete chain of transaction logs is available for the recovery. 

But consider: if a *different* backup product does a save, and a portion of the transaction logs has thereby been deleted, then that portion will not be available to Avamar when we next attempt an Incremental or Differential backup; we would have a broken chain.

This new feature has been added to protect DBAs and Backup Admins from that scenario.  The fact that you're seeing this error now suggests that many of the savesets that were being created by the previous version would not have been valid protection of your databases.

I hope this helps,

_Scott

Scott Bingham

Manager, Development Engineering

EMC Avamar

Bellevue, WA

sfbing

We just upgraded and have run into the same thing.  So far, running on-demand full backups with forced-incremental has been working to clean up the errors, ensure that the logs are backed up, and properly truncating the logs.  While good backups are required, truncating the logs to prevent autogrowth and the creation of unnecessary VLF's, or worse, filling up a drive and halting databases is critical for us!

I like the concept of this new feature because it emphasizes the importance of not running backups outside of Avamar, and if you have to, to coordinate the effort to keep our production recovery system (Avamar) consistent.  In practice, when a customer requires a sql bak file, I restore the newest Avamar backup to a dev server, then create the SQL backup over there, never touching the production database for this exact reason.

We also have been experiencing this exact issue – we have a MIS department which prefers to run a scheduled SQL backup and random SQL backups prior to upgrades or changes at will and by doing so they interrupt our Avamar backups with these errors…  We have previously tested out restores understanding any point in time restores would require all points of backup and this has worked flawlessly in 5.x and 6.0, but now at 6.1 Avamar has made this impossible even when trying to use the “COPY_ONLY” option.

Our solution, for now, has been to remain at version 6.1.0-402 which still allows us to use 6.0.101-66 (Client/SQL agent) for only our SQL servers.  All other servers are at this 6.1.0-402 client version, only our SQL servers need to be at this older version of the client/agent…

In discussions with the support team and I guess even one of the higher end developers, we have been informed in order to allow for multiple systems to perform backups (with point in time restores) we will be required to use these alternative methods and use Avamar file level backups to capture these backup files…

Additionally, we use ATO to retain backups for longerperiods rather than using expensive grid space – we also, for now, retain monthly SQL backups indefinitely, by storing this on external encrypted media – it would be nice if there was a way to take advantage of SQL compression so when restoring the DBs out of Avamar for storing onto our external media, a 20 gig file compressed to 2 gig would be terrific.  Ideally, an option box in the SQL dataset to flag SQL compression and storing these f-0 backups into the grid also compressed…

So in order to take full advantage of backup flexibility, when we are forced to upgrade Avamar to SP1 of 6.1, we will be required to either commit to Avamar entirely or switch to SQL file based backups for truncating and point-in-time + take advantage of SQL compression followed by a file-level backup by Avamar.  But for now we are sticking with the older version of Avamar client/agent to allow ourselves exactly what works.

And our DBA indicates the COPY_ONLY should have been a proper avenue to prevent these Gap Detection/Snapup errors; however, even with this in our SQL backups, Avamar continues to ignore and error out regardless.  And just to add, the backups performed by the DBA are simple - non log - COPY_ONLY full backups, no truncating or etc - just a simple file based backup with out point in time restore ability.  Generally taken prior to an upgrade.