When SQL Server will not start, the temptation is to search the one number you were given. That number is usually the least useful line in the file. To find out what master damage actually produces, I broke it four different ways in a throwaway container and read the log each time. 3417 did not appear in any of them. Something more specific always did, and that was always the line worth acting on.
This is the same shape as error 3013 on a failed restore: a closing message that tells you the operation failed, sitting underneath the message that says why.
What Master Damage Actually Produces
| What was damaged | What the log said | What it points at |
|---|---|---|
| Files left owned by the wrong account | 17204 then 5120, OS error 5, access denied | Permissions, not corruption. The most common real cause, and the easiest to fix. |
master.mdf header destroyed | 5172, then 5173 twice | The file is not a database file any more. Restore or rebuild. |
mastlog.ldf overwritten | 824, severity 24, torn page | The log cannot be read. Recovery never starts. |
master.mdf interior pages, header intact | recovery runs, then 824, incorrect checksum | The file opens and recovery gets partway. Genuine page corruption. |
Four failures, four different first lines, and in every case the instance exited rather than continuing. The practical consequence: the fix is decided by the specific error, and three of these four are not the same problem at all.
This one happened by accident during testing, which is exactly how it happens in production: the files were copied back into place by an account that left the ownership wrong. The instance behaves as though the database is broken.
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file /var/opt/mssql/data/master.mdf for file number 1.
OS error: 5(Access is denied.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "/var/opt/mssql/data/master.mdf".
Operating system error 5: "5(Access is denied.)".Nothing is corrupt. Check ownership and permissions on the data directory before you reach for a backup. After a file move, a restore from a copy, a drive remap or a service account change, this is the first thing to rule out and it costs a minute.
The first pages of the file zeroed, so the header no longer identifies it as a database.
Starting up database 'master'.
Error: 5172, Severity: 16, State: 15.
The header for file '/var/opt/mssql/data/master.mdf' is not a valid database file header.
The PageAudit property is incorrect.
Error: 5173, Severity: 16, State: 1.
One or more files do not match the primary file of the database.5172 is the one to search, not 3417. The 5173 that follows is a consequence: the log file no longer matches a primary file that is no longer readable. This is a restore or rebuild, and no amount of restarting will change it.
Master’s own transaction log filled with random data, leaving the data file untouched.
Starting up database 'master'.
Error: 824, Severity: 24, State: 2.
SQL Server detected a logical consistency-based I/O error: torn page
(expected signature: 0xaaaaaaaa; actual signature: 0x8834a602). It occurred during a
read of page (2:0) in database ID 1 at offset 0000000000000000
in file '/var/opt/mssql/data/mastlog.ldf'.A textbook error 824, on master’s own log, with real signatures. Recovery never begins because the log cannot be read. Note that database ID 1 in an 824 means master, and that is worth recognising instantly.
The most interesting one, because the file opens and recovery genuinely runs before it fails.
Starting up database 'master'.
16 transactions rolled forward in database 'master' (1:0).
Error: 824, Severity: 24, State: 2.
SQL Server detected a logical consistency-based I/O error: incorrect checksum
(expected: 0x524b60b1; actual: 0x8af60429). It occurred during a read of page (1:56)
in database ID 1 at offset 0x00000000070000
in file '/var/opt/mssql/data/master.mdf'.Read that middle line. Sixteen transactions rolled forward before the failure, so recovery was working. A partial start is not a good sign here, it just means the damage was further in. The checksum values are the giveaway that this is real page corruption rather than a configuration problem.
Finding the Log When the Instance Is Down
You cannot query anything, so the error log has to be read from disk. It is plain text and the newest one is always ERRORLOG with no extension.
# Windows, default path. Adjust the instance folder for a named instance.
Get-Content "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG" -Tail 60
# The lines that matter, rather than the whole file
Select-String -Path "C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Log\ERRORLOG" `
-Pattern "Error:|Cannot recover|FCB::Open|not a valid database file header"# Linux
sudo tail -n 60 /var/opt/mssql/log/errorlog
# In a container the log goes to the container's output as well
docker logs <container> | tail -n 40Read from the bottom upwards. The instance stops at the first thing it cannot survive, so the last error in the file is the one that killed it, and anything after it is shutdown noise.
Getting It Back
In order of how much you lose, least first. Work down this list, do not start at the bottom.
- Rule out permissions. Case 1 above. Free, instant, and it is the answer more often than corruption is.
- Put back a known-good copy of the files, if you have one from a snapshot or a file-level backup taken while the instance was stopped. Confirmed working in testing: replacing
master.mdfandmastlog.ldfwith intact copies brought the instance straight back up with no other action. - Restore master from a database backup. This needs the instance started in single-user mode, because you cannot restore master while it is serving normally. This is the path Microsoft’s own message points at, and it is only available if someone was backing master up.
- Rebuild the system databases, then restore master. The last resort. It gives you a working instance with none of your logins, jobs, linked servers or configuration, which you then restore. The rebuild command differs by platform and version, so take it from the documentation for the exact build you are on rather than from memory.
The uncomfortable part of that list is that steps 3 and 4 both depend on having a master backup. Master is small, changes rarely, and is the one database whose loss costs you every login and every job on the instance. If it is not in your backup schedule, that is the thing to fix today rather than the thing to discover during an outage.
What Not To Do
- Do not restart repeatedly hoping it catches. Every one of the four cases failed identically on every attempt. Restarting only costs you the time you need for the log.
- Do not delete or move the damaged files before you have read the log. The file names and paths in the error are half the diagnosis, and a damaged master may still be restorable.
- Do not rebuild the system databases as a first move. It works, and it throws away every login, job, linked server and server-level setting. It is step 4 for a reason.
- Do not assume corruption because the instance is down. Ownership and permissions produce the same headline symptom and none of the damage.
- Do not practise this on a real instance. Everything on this page was reproduced in a disposable container, which took minutes and cost nothing.
Common Questions
I searched 3417 and found nothing useful. Why?
Does an 824 on database ID 1 mean the whole instance is lost?
The instance will not start after we moved the data files. Is it corruption?
FCB::Open failed and operating system error 5 in the log, which is a permissions problem on the file or the folder. Reproduced here by accident, because copying files back into place as the wrong user is exactly how it happens for real.Can I just copy master.mdf from another server?
How do I practise this safely?
Should I be backing up master?
Related Scripts
- I/O Errors 823 and 824, the error two of these four cases actually produced
- Unable to Open the Physical File (Error 5120), the access denied message on its own, and why it does not mean what it says
- Backup & Recovery scripts, including whether master is in your backup schedule at all
- DBCC CHECKDB Found Corruption, once the instance is up and you need the extent
- SQL Server Errors: The Complete Guide, the index for the whole error series
Leave a Reply