Quick Scan Report – Full or Bulk Logged Recovery Model with No Backups

What this check looks for

Databases whose recovery_model_desc is FULL or BULK_LOGGED, which have full backups in msdb.dbo.backupset and have no log backups. Snapshot backups are excluded.

The combination is what matters. A full recovery database with no backups at all is a different, simpler finding. This one is about a database somebody is looking after, where one half of the arrangement is missing.

Why it matters

This is the worst of both options: all of the cost of full recovery and none of the benefit.

Full recovery exists to give you point in time recovery. It does that by keeping every change in the transaction log until a log backup copies it out. The bargain is: you take log backups, and in exchange you can restore to any moment.

With no log backups, only your side of the bargain happens:

• The log never truncates. log_reuse_wait_desc sits on LOG_BACKUP permanently and the log grows until the drive fills. Taking full backups does not help; a full backup does not truncate the log in full recovery, which is the single most commonly misunderstood thing about this.
• You cannot restore to a point in time anyway, because there are no log backups to roll forward. Your recovery point is the last full backup, exactly as it would be in simple recovery.
• Recovery is worse than simple recovery would be, because a huge log file means a slower restore and a slower startup.

So the database is exposed to unbounded log growth in exchange for nothing. Typically it ends with error 9002 and a full drive, followed by somebody shrinking the log, which works once and then happens again.

The usual cause is a default. model is in full recovery on a default installation, so every new database inherits it. Somebody set up full backups, the database worked, and nobody asked whether log backups were needed until the log drive filled.

How to confirm it yourself

SELECT d.[name],
       d.[recovery_model_desc],
       d.[log_reuse_wait_desc],
       MAX(CASE WHEN bs.[type] = 'D' THEN bs.[backup_finish_date] END) AS [last_full],
       MAX(CASE WHEN bs.[type] = 'L' THEN bs.[backup_finish_date] END) AS [last_log]
  FROM sys.databases AS d WITH (NOLOCK)
  LEFT JOIN msdb.dbo.backupset AS bs WITH (NOLOCK)
         ON bs.[database_name] = d.[name] AND bs.[is_snapshot] = 0
 WHERE d.[recovery_model_desc] IN ('FULL', 'BULK_LOGGED')
   AND d.[database_id] > 4
 GROUP BY d.[name], d.[recovery_model_desc], d.[log_reuse_wait_desc]
 ORDER BY [last_log];

A row with a last_full and a null last_log is the finding. log_reuse_wait_desc reading LOG_BACKUP on the same row is the confirmation that it is already costing you.

And how much it has cost so far:

SELECT DB_NAME([database_id])                                AS [database_name],
       [name]                                                AS [logical_name],
       CAST([size] * 8.0 / 1024 AS DECIMAL(12,1))            AS [size_mb],
       [physical_name]
  FROM sys.master_files WITH (NOLOCK)
 WHERE [type] = 1
 ORDER BY [size] DESC;

A log file larger than its data file is the usual signature, and it has its own check.

How to fix it

Decide what the database actually needs, then make the configuration match. Both answers are legitimate; the current state is not.

If you need point in time recovery, start taking log backups:

BACKUP LOG [YourDatabase]
  TO DISK = N'\\backupserver\sqlbackups\YourDatabase_log.trn'
  WITH CHECKSUM, COMPRESSION;

Schedule them at the interval that matches how much data you are willing to lose. Every 15 minutes is a common choice and means a worst case loss of 15 minutes. That interval is your recovery point objective, stated in a job schedule rather than in a document.

After the first log backup the existing space inside the log becomes reusable, so the file stops growing. It does not shrink, and it should not need to: it grew to the size it needed.

If you do not need point in time recovery, say so properly:

ALTER DATABASE [YourDatabase] SET RECOVERY SIMPLE;

In simple recovery the log truncates at every checkpoint and this whole problem disappears. This is the right answer for development databases, for reporting copies rebuilt from elsewhere, and for anything where losing everything since the last full backup is acceptable.

Do not do this to a database in an Availability Group or with log shipping, both of which require full recovery.

Then fix model, so new databases do not arrive with the same mismatch:

ALTER DATABASE [model] SET RECOVERY SIMPLE;   -- only if simple is your house default

If the log has already grown enormous, shrink it once after the first log backup, then set a sensible fixed growth so it comes back in large steps rather than thousands of small ones.

How long it takes

About two hours to decide per database and schedule the log backups. Shrinking and resizing an oversized log adds a quiet window.

Related reports

Related checks

Frequently asked questions

Do full backups truncate the log? No, and this is the single most common misunderstanding about recovery models. In full recovery only a log backup truncates the log.

Is bulk logged the same as full here? For this purpose, yes. Bulk logged minimally logs certain bulk operations, and it still needs log backups and still prevents point in time recovery into a period containing one.

We take a full backup every hour instead. That works as a recovery strategy and it does nothing about the log, which keeps growing. Simple recovery plus hourly full backups is the coherent version of that plan.

Will switching to simple lose anything? It breaks the log chain, so point in time recovery from existing log backups ends there. If you never had log backups, you had no such capability to lose. Take a full backup afterwards.