June 3, 2014 at 12:05 pm
Occasionally ... as in, very rarely... I've seen incidents of a transaction log backup not being recorded in the backup history tables (namely backupset) in msdb.
The only theory I can come up with is a blocking issue, because - at least in the most recent incident - a full backup of a very large database... the same one, in fact... had completed mere seconds before the transaction log backup did. So SQL was probably still in the process of logging that one to the history tables (the database itself has ~500 files to it). But... that's a weak theory at best as to why the record of the log backup doesn't get written at all. I would expect it to simply do what blocking is "for" ... get delayed a few seconds. Only way I can reconcile this is if there's some kind of timeout involved. There's no error message in SQL's or Window's logs. There simply isn't a record of the backup.
Has anyone else encountered this? And if so, did you find any more information about it? i.e. is it a bug? Is it something that can be avoided? Without having to disable the transaction log backups "about" when the full backup would finish?
It becomes an issue with another system (name withheld - this isn't a sales pitch 🙂 ) which relies on the backup history. In a nutshell it virtualizes a database, but it does so by basically sucking in the backups, including the log backups (it's very much akin to log shipping in that regard). But if a record of a log backup isn't recorded in the history tables, it doesn't know there was a log backup. It'll then try to apply the next log backup it does see in the history tables, and naturally it'll fail miserably on a LSN mismatch.
Like I said, this has been a rare thing - i.e. only twice that I can recall so far, once back in January, and again a few days ago. As Murphy would have it, of course both incidents occurred late on a weekend night. Easy enough to recover by manually forcing a restore of the "skipped" backup, but... would really love to not have to do that manually.
September 23, 2020 at 9:16 am
I'm in the same trouble on SQL Server 2019: FULL backup is taken daily but the query:
SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date,
CAST(msdb.dbo.backupset.backup_size AS NUMERIC(35,2))/1048576.0 AS backup_size_MB
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE msdb.dbo.backupset.type = 'D'
ORDER BY
backup_finish_date desc
says that the last backup finish date was 2 months ago. That's not true!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply