If you’re running SQL Server 2016, you may want to install SQL Server Service Pack 1, CU8. I came across an issue recently that effectively caused the transaction log backups to hang. Through our proactive monitoring, we were alerted to the fact that a log backup had not been taken in 4 hours. Upon investigation, I could see the log backup had failed. This occurred when the backup operation was kicked off via an SQL Agent job, or manually executed within a query window.
The backup wasn’t failing but SQL Server wasn’t actively performing the operation; it had hung. I couldn’t see that disk I/O was increasing when querying the SPID number, plus, when running my script that gathers percentage completed, this wasn’t moving; it remained at 0% for the duration of the active session. As I couldn’t see any reason for this, I checked the SQL Server error log, this revealed the below error:
“BACKUP LOG was unable to log updates for database ‘DatabaseName’. Subsequent log backups will be required to advance the backup point from () to () after log space is made available for logging them.”
In the wild, it would look something like:
To me, the error message makes very little sense (grammatically and informationally), but what stood out to me was the ‘after log space is made available for logging them.’ snippet. The strange thing here is that the log file had plenty of free space available internally and the disk had quite a bit of free space available for the log to auto-grow.
Researching this, it became apparent that this is a known issue and Microsoft have released a fix within SQL Server 2016 Service Pack 1, CU8. As far as I know, this only affects instances running on SQL Server 2016, but from a few Microsoft articles, they imply it’s also been noted in 2014 and more recently, 2017.
Work Around
I did figure out a temporary work around by growing the affected database transaction log manually by just a few kilobytes. After doing this, the transaction log backup job completed successfully.
The problem with this is that it could reoccur again at any moment, therefore I would strongly recommend patching your instance as soon as possible. It can be caught reasonably early if you’re monitoring for log backups not being taken in X hours or if a log file is reaching near capacity.
Update
As users of SQL Server were still see this error after applying CU8, on the 30th of May, Microsoft have released Cumulative Update 1 for SQL Server 2016 SP2 which includes a fix for this issue. The update can be found here.