Search code examples
sql-serversql-server-2012backupsql-server-2019dbcc

SQL Server :: FULL backup not recorded in msdb.dbo.backupset and log file unusually large

I have a server that is running SQL Server 2019 but the databases are still on Compatibility level 110 (so that means SQL Server 2012 basically).

We take a FULL backup every night and I indeed see the files backed up in the right folder every day. But then if I run this query , and I add backup_finish_date desc to check when the last FULL backup was taken I see that the date is months back:

enter image description here

So I found this guide that says it might be a bug in SQL Server and ask to run this check:

USE msdb
GO
SELECT server_name, database_name, backup_start_date, is_snapshot, database_backup_lsn
FROM backupset

"...In the result, notice the database_backup_lsn column and the is_snapshot column. An entry that represents an actual database backup operation has the following characteristics: The value of the database_backup_lsn column is not 0. The value of the is_snapshot column is 0."

enter image description here

All good to me, it looks like database_backup_lsn column is not 0 and is_snapshot column is 0

Then the guide says to run this query to verify the integrity of the backup:

WITH backupInfo AS( SELECT database_name AS [DatabaseName], 
name AS [BackupName], is_damaged AS [BackupStatus],
backup_start_date AS [backupDate],
ROW_NUMBER() OVER(PARTITION BY database_name 
ORDER BY backup_start_date DESC) AS BackupIDForDB 
FROM msdb..backupset) SELECT DatabaseName 
FROM backupinfo WHERE BackupIDForDB = 1 and BackupStatus=1

enter image description here

The result is nothing!

And the guide says: "...If the this query returns any results, it means that you do not have good database backups after the reported date."

So now I'm scared that our backup is fucked up. We take the backup with CHECKSUM but we haven't run DBCC CHECKDB in ages so we are maybe (successfully and with CHECKSUM) taking backups of corrupted databases. Let's run:

DBCC CHECKDB('msdb') WITH NO_INFOMSGS, ALL_ERRORMSGS

enter image description here

And the result is nothing, so it seems all good.

And at the same time the size of the log file (155GB) appears unusually large compared to the data file size of 514GB

EDIT:

I take Full backup every night and Log backup every hour

EDIT 2:

Brent Ozar suggests to run SELECT name, log_reuse_wait_desc FROM sys.databases; and as result I have NOTHING nearly everywhere:

enter image description here

Solution

  • ...And the solution was:

    We use Always On availability groups and the backups were taken on the failover environment (the replica server).

    I see on the web a lot of question similar to mine an they don't really have an answer. I believe I'm not the only one that faced this problem