SQL SERVER – FIX : Error: 3456, Severity: 21 – Could not redo log record (xx:xx:xx), for transaction ID (x:xx), on page (x:xx), database ‘master’

A client’s SQL Server would not start. SQL Server could not redo a log record in the master database, and they had no backup of any system database. We got them back, but it took a rebuild, a permissions puzzle and some luck with msdb. I am not going to corrupt master on my own machine to show you the error. What I did check on my own instance is the part that would have saved them, and the answer was a little embarrassing.

The Error

Starting up database 'master'.
Error: 3456, Severity: 21, State: 1.
Could not redo log record (2579:456:5), for transaction ID (0:151685), on page
(1:375), database 'master' (database ID 1). Page: LSN = (2579:424:5), type = 1.
Log: OpCode = 4, context 2, PrevPageLSN: (2579:368:3). Restore from a backup of
the database, or repair the database.

When SQL Server starts, it replays the transaction log to bring each database to a consistent state. That is the redo step. Here it read a log record that did not match the page it was meant to apply to. The page and the log disagreed, so SQL Server could not trust either, and it refused to go further.

In a user database that takes one database offline. In master it takes the whole instance down, because nothing starts without master. The message offers two ways out, restore or repair, and for master that really means restore or rebuild.

Step One: Rebuild the System Databases

With no backup, the only way to get an instance to start was to rebuild master, model and msdb as new, empty databases. It is done from SQL Server setup:

setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER
      /SQLSYSADMINACCOUNTS="DOMAIN\YourAdminLogin"
      /SAPWD="<a strong sa password>"

MSSQLSERVER is the name for a default instance. For a named instance, use its name. /SAPWD is only needed when the instance uses mixed mode authentication. And copy every existing MDF and LDF somewhere safe first, including the broken master. The rebuild replaces the system database files, and you may want those old msdb and model files back later. We did.

Once it finished, SQL Server started. It started as a stranger: no logins, no linked servers, no jobs, no configuration. A fresh install that happened to be on the old machine.

Step Two: Attach the User Database, and Error 5123

The user database files were fine. So we attached them, and got this:

CREATE FILE encountered operating system error 5(Access is denied.) while
attempting to open or create the physical file 'S:\...\MSSQL\Data\AX_PROD.mdf'.
(Microsoft SQL Server, Error: 5123)

Operating system error 5 is Windows saying access denied. SQL Server was not refusing the file. Windows was refusing SQL Server.

When we looked at the file’s security properties, it had no owner we could recognise. The account that used to own it was gone, and the new SQL Server had no right to open it. We made the SQL Server service account the owner of the files, and the attach went through.

Step Three: msdb and model From the Old Files

Here is where the copies from step one paid off. The corruption was in master, not in msdb. So we stopped SQL Server, put the old msdb and model files back in place of the freshly rebuilt ones, and started it again.

It worked. msdb came back, and with it the SQL Agent jobs. That was luck, not a plan. If msdb had been damaged too, all of that would have had to be rebuilt by hand.

master was the one thing we could not get back. Logins had to be created again. My client had only a few, which made that part manageable.

What I Checked on My Own Machine

Having told this story, I thought I should look at my own test instance. Here is the query:

SELECT d.name,
       COUNT(b.backup_set_id) AS full_backups,
       MAX(b.backup_finish_date) AS latest
FROM   sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS b
       ON b.database_name = d.name AND b.type = 'D'
WHERE  d.database_id <= 4
GROUP BY d.name;

name     full_backups   latest
master   0              NULL
model    0              NULL
msdb     0              NULL
tempdb   0              NULL

Zero. It is a test machine, so nothing is lost, but it is exactly my client’s position. Nobody decides not to back up master. It just never gets onto the list.

And it is not a big job. On my instance the data files of all three come to about 128 MB:

master   data 101.6 MB
model    data   8.0 MB
msdb     data  18.9 MB

tempdb is the one exception. It is rebuilt every time SQL Server starts, so there is nothing to back up.

What Would Have Made This Simple

With a backup of master, the recovery is a known procedure. Start the instance in single user mode with the -m startup option, restore master, and SQL Server shuts itself down when it finishes. Start it normally and every login, linked server and setting is back.

So back up master, model and msdb every day, in the same job as your user databases. Back up master again after any change to logins or server configuration. Keep a copy of those backups off the server. And once in a while, restore master on a test instance, because a backup you have never restored is only a hope.

Error 3456 in master is not the disaster, it is the day you find out whether master was ever backed up.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.