SQL 2012 Cluster, DB logs not backed up, kb1746 is applied

[gmcoburn]

Hello,

I have deployed a SQL 2012 Cluster on Windows 2012 R2 that spans datacentres (a geocluster). For various reasons, to enable shared storage between servers we have used DoubleTake Availability, and the solution is working well.

I currently back up the two SQL servers in each site with Veeam B&R 7.0.0.871.

Whilst the backups are processed without error, I've run into the old Veeam\SQL 2012 backup issue of the database logs files not being backed up and truncated as per http://www.veeam.com/kb1746. I have the made change as per the KB to the NT AUTHORITY\SYSTEM SQL Login to have the sysadmin role.

The database logs are still not being backed up or truncated.

Can anyone offer any advice or workaround?

Regards

George

[foggy]

George, just to make sure, is application-aware image processing option enabled in the job settings as well as logs truncation? Have you checked the VM Windows Event Log for possible related errors? Have you checked state of the VSS writers inside guest OS?

Btw, how do you tell logs are not truncated? Do you check the size of the logs and it isn't reduced?

Also, with all technical issues it is always recommended to contact technical support directly, since most likely job debug logs investigation will be required.

[gmcoburn]

Hi foggy,

Thanks for your questions.

1. Yes, the app aware image processing is enabled. Veeam progress job log even says, Truncating Logs. Other SQL VMs (not clustered), within the same backup job are working as expected after applying the workaround from kb1746

2. Event id 18264 for a successful DB backup for each database is created in the Application event log of the servers, but no Event id 18265 for successfull Log Backup. Indeed, of all the SQL backup\restore event ids (18264, 18265, 18266, 18267, 18268, 18269, 18270, 18271, 18276, and 18277) only 18264 is in the Application Log.

3. The properties of the databases that are set to a Full recovery model states that the Last Database Log Backup log backups are not being performed. The test databases that I am using to fault diagnose that have show the last successfull Last Database Log Backup as None.

4. Viewing the file sizes of the log files shows them to not have shrunk, are close to 100% of "Currently allocated space", and are roughly the same size as the DB file.

Re raising a support ticket, I understand, and that was going to be my next step if nobody came up with a "have you done this" answer to which I would give myself a facepalm and go away and berate myself for missing the obvious!

Regards

George

[veremin]

4. Viewing the file sizes of the log files shows them to not have shrunk, are close to 100% of "Currently allocated space", and are roughly the same size as the DB file.

That is expected. Please, be aware that SQL log truncation doesn't reduce the logs size, but rather removes transactions for the corresponding logs. Of course, you can use the autoshrink option, but it's against MS best practices.

Thanks.

[gmcoburn]

Yep, I am aware of that v.Eremin, and shrinking the log file is my secondary concern after the log file not being backed up at all.

But what a successful log backup does do is free up space within the log file for future transactional use, which I have shown is not the case as the log file is close to 100% of "Currently allocated space" after a backup. In my scenario, transactions are not cleared from the log, and the log file will just keep on growing.

Cheers

George

[veremin]

Ok, getting back to Foggy's questions, what is the state of VSS writers inside a given VMs? Do all of them return success? Thanks.

[gmcoburn]

The vssadmin list writers command shows all as Stable and No Error.

As this is a SQL Cluster, the Volume Shadow Copy service is set to Manual. I have personally seen this service start as part of the backup, and this is confirmed in the System Event log.

Also in the System Event Log is, in order

Event 7045 - VeeamVssSupport, Veeam Guest Agent successful installation
Event 7036 - The Volume Shadow Copy service entered the running state.
Event 7036 - The VeeamVssSupport service entered the running state.
Event 7036 - The Microsoft Software Shadow Copy Provider service entered the running state.
Event 98 - Volume SQL_Data (\Device\HarddiskVolumeShadowCopy121) is healthy. No action is needed. (This is the drive with both the DB and DB Logs)
Event 98 - Volume System Reserved (\Device\HarddiskVolumeShadowCopy122) is healthy. No action is needed.
Event 98 - Volume ?? (\Device\HarddiskVolumeShadowCopy123) is healthy. No action is needed. (This can only be the C Drive, which doesn't have a drive label, hence the ??)

Other than the VSS shutting down due to inactivity after the backup has completed, that’s it for the System Event Log. As noted earlier, the Application Log shows a successful Database backup only.

Cheer

George

[foggy]

Then it is time to open a case with our technical support so they could look deeper at your debug logs.

[gmcoburn]

Thanks, I was thinking the same thing. Will do.

[foggy]

We would appreciate your case ID posted here for further reference. Thanks.

[gmcoburn]

Case id is 00627981

Regards

George