Quick Scan Report – Master Database Not in Simple Recovery Mode

What this check looks for

The recovery model of the master database, from sys.databases. The message names the model it is currently set to.

Why it matters

Full recovery model exists to enable log backups and point in time recovery. master supports neither, so setting it to full gets you all of the cost and none of the benefit.

Specifically, for master:

• Log backups are not supported. BACKUP LOG [master] fails.
• Differential backups are not supported. Only full backups can be taken.
• Only a full backup can be restored, and only in single user mode with the instance started with the -m flag.

So there is no point in time recovery to be had. The best possible recovery of master is “the state it was in at the last full backup”, and that is true regardless of recovery model.

What full recovery costs on master instead:

• The transaction log never truncates. In full recovery the log can only be truncated by a log backup, and you cannot take one. So the log grows until the disk fills or somebody intervenes.
• log_reuse_wait_desc will read LOG_BACKUP, permanently, which is exactly the condition the log truncation checks report. This finding and that one usually appear together.
• Backup jobs may fail. A maintenance plan configured to take log backups of all databases will fail on master, and that failure can mask the log backup failures you do care about.

How it happens is almost always the model database. New databases inherit their recovery model from model, and while master is not created that way, several deployment and hardening scripts set every database to full recovery in a loop without excluding the system databases. The other common route is a well intentioned “set everything to full recovery for safety” instruction applied literally.

What the correct configuration looks like:

master still needs backing up, and that part is often forgotten in the relief of fixing the recovery model. It holds your logins, server level configuration, linked servers, credentials and endpoints. Losing it means rebuilding all of that by hand.

How to confirm it yourself

Every system database with its recovery model and log state:

SELECT [name],
       [recovery_model_desc],
       [log_reuse_wait_desc],
       [state_desc],
       [is_read_only]
  FROM sys.databases WITH (NOLOCK)
 WHERE [database_id] <= 4
 ORDER BY [database_id];

master showing FULL with log_reuse_wait_desc of LOG_BACKUP is the complete picture of this finding.

How large the log has grown as a result:

SELECT DB_NAME(mf.[database_id])                     AS [database_name],
       mf.[name]                                     AS [logical_name],
       mf.[type_desc],
       CAST(mf.[size] * 8.0 / 1024 AS DECIMAL(12,1)) AS [size_mb],
       CASE WHEN mf.[is_percent_growth] = 1 THEN CAST(mf.[growth] AS VARCHAR(10)) + ' %'
            ELSE CAST(mf.[growth] * 8 / 1024 AS VARCHAR(10)) + ' MB' END AS [growth],
       mf.[physical_name]
  FROM sys.master_files AS mf WITH (NOLOCK)
 WHERE mf.[database_id] = 1;

When master was last backed up, which is the more important question this finding tends to surface:

SELECT bs.[database_name],
       MAX(bs.[backup_finish_date]) AS [last_full_backup],
       DATEDIFF(DAY, MAX(bs.[backup_finish_date]), GETDATE()) AS [days_ago]
  FROM msdb.dbo.backupset AS bs
 WHERE bs.[type] = 'D'
   AND bs.[database_name] IN ('master', 'model', 'msdb')
 GROUP BY bs.[database_name];

Whether a job is failing because of this:

SELECT TOP (50)
       j.[name] AS [job_name],
       msdb.dbo.agent_datetime(h.[run_date], h.[run_time]) AS [ran],
       h.[run_status],
       h.[message]
  FROM msdb.dbo.sysjobhistory AS h
 INNER JOIN msdb.dbo.sysjobs  AS j ON j.[job_id] = h.[job_id]
 WHERE h.[run_status] = 0
   AND h.[message] LIKE '%master%'
 ORDER BY h.[run_date] DESC, h.[run_time] DESC;

And the whole instance, for context, since a script that set master to full recovery probably set other things too:

SELECT [recovery_model_desc], COUNT(*) AS [databases]
  FROM sys.databases WITH (NOLOCK)
 GROUP BY [recovery_model_desc];

How to fix it

Set it back to simple. It is immediate, online, and carries no risk.

ALTER DATABASE [master] SET RECOVERY SIMPLE;

Then reclaim the log space the full recovery model caused it to accumulate:

USE [master];
GO
DBCC SHRINKFILE (N'mastlog', 2);

The master log has no reason to be large; a couple of megabytes is normal. Once the database is in simple recovery the log truncates at checkpoint and the shrink succeeds cleanly.

Confirm:

SELECT [name], [recovery_model_desc], [log_reuse_wait_desc]
  FROM sys.databases WHERE [database_id] = 1;

log_reuse_wait_desc should now read NOTHING.

Then check the other system databases, because whatever set master probably set them:

ALTER DATABASE [msdb] SET RECOVERY SIMPLE;

Think about model separately. It is the template for new databases, so its recovery model is a deliberate choice rather than a correction. Full recovery on model means every new database starts in full recovery, which is right for production if you take log backups and wrong if you do not, since it produces the log growth problem on every new database.

Then fix the script that did it. A hardening or deployment script that loops over sys.databases needs to exclude the system databases:

-- the exclusion that was missing
SELECT [name] FROM sys.databases WHERE [database_id] > 4;

And make sure master is actually backed up, which is the more valuable outcome of this finding:

BACKUP DATABASE [master]
    TO DISK = N'E:\SQLBackups\master_full.bak'
  WITH COMPRESSION, CHECKSUM, INIT, STATS = 10;

Back up master, model and msdb daily, and after any change to logins, jobs, linked servers or server configuration. They are small, the backup takes seconds, and rebuilding them by hand takes days.

Know how a master restore actually works, before you need it: the instance must be started in single user mode with the -m flag, then restored, then restarted. It is worth reading through once while nothing is broken.

How long it takes

About an hour, most of it checking the other system databases and confirming the backups are in place. The recovery model change itself is instant.

Related reports

Related checks

Frequently asked questions

Is simple recovery less safe for master? No. master cannot take log or differential backups in any recovery model, so full recovery provides no additional recoverability. It only prevents the log from truncating.

What about msdb? Simple is the usual choice. Full is defensible if you genuinely need point in time recovery of job and backup history, and in that case you must actually take the log backups.

Do I need to restart after changing this? No. ALTER DATABASE ... SET RECOVERY SIMPLE is immediate and online.

How often should I back up master? Daily, and after any change to logins, server configuration, linked servers or Agent jobs. It is small and the backup takes seconds.