Fix it now
Recovery could not finish. Error 3417 means it failed on master, so the instance stops; error 3414 means it failed on one database, which Microsoft documents as leaving that database SUSPECT. The line above the error in the error log names the file and the operating system error, and that line is the fault you have to fix.
SELECT name, state_desc FROM sys.databases;
RESTORE DATABASE [YourDb] FROM DISK = 'D:\Backup\YourDb.bak' WITH REPLACE, NORECOVERY;
RESTORE LOG [YourDb] FROM DISK = 'D:\Backup\YourDb_log.trn' WITH RECOVERY;
DBCC CHECKDB ([YourDb]) WITH NO_INFOMSGS;
- Read the error log first. Its folder is the
-estartup parameter on the Startup Parameters tab of the instance’s properties in SQL Server Configuration Manager; the file isERRORLOGwith no extension and opens in a text editor while the service is down. - Find the line immediately above the 3414 or 3417 entry. It names the database, the file and usually a Windows error number: 5 is access denied, 32 is the file held open, 112 is a full volume, 3 is a path that no longer exists.
- If the failure names master, restore master from backup or rebuild the system databases with setup, both covered below. Reinstalling SQL Server recovers nothing.
- If only a user database failed, the query above shows it SUSPECT. Restore it from the last good full backup and every log backup since, then prove the result with
DBCC CHECKDB.
Never delete or rename a transaction log to force a database online, and do not attach a data file without its log. Both routes discard committed transactions and leave a database that looks healthy and is not.
If the database is back online and CHECKDB is clean you can stop here. If master is the problem, or there is no backup, the next sections deal with that.
Why it happens
Every time a database starts, SQL Server replays its transaction log: analysis to work out what was in flight, redo to reapply committed work, undo to roll back the rest. If any part of that cannot complete, because a file is missing, the log is truncated, a page will not read or the storage returns an error, the engine stops rather than opening a database in which committed transactions might be missing.
Error 3414 is that refusal scoped to one database. Microsoft’s published text is that an error occurred during recovery preventing the database from restarting, and that you should diagnose the recovery errors and fix them, or restore from a known good backup. The documented consequence is that the database is left in the SUSPECT state, so every later attempt to use it returns error 926 instead. If the same thing happens to tempdb the instance shuts down.
Error 3417 is the same failure on master, and master is recovered first because it holds the file locations, logins and configuration the rest of the instance needs. Microsoft’s text is blunt: cannot recover the master database, SQL Server is unable to run, restore master from a full backup, repair it, or rebuild it. The same page records a second cause that is easy to miss, which is a cumulative update or service pack whose upgrade scripts failed.
The companion codes narrow the picture rather than adding new problems. Error 5171 is published as “is not a primary database file”, which is what you get when the wrong file is copied into place or the first pages are damaged. Error 942 is a deliberate state, not damage: the database is offline. Error 945 is the resource complaint, published as a database that cannot be opened due to inaccessible files or insufficient memory or disk space.
The system database files are present but the engine cannot use them
You have this one if 3417 appears within seconds of the service starting, with a 17053 line and an operating system error such as 5 or 32 just above it.
- Clear the compress and encrypt attributes on the DATA folder. System databases cannot be recovered from a compressed or encrypted folder.
- Confirm the per-service identity has modify rights on that folder. Microsoft’s rule is that Setup grants those rights at its own locations only, so a folder someone moved the files into has to be granted by hand.
- Exclude the SQL Server data, log and backup folders from on-access antivirus scanning, then clear any read-only attribute on
master.mdfandmastlog.ldf.
If a drive letter changed, correct the -d, -l and -e startup parameters instead of moving files back to satisfy them.
master is damaged and has to be restored
You have this one if The files are readable, permissions are right, and recovery of master still fails.
- Start the instance so that only sqlcmd can connect: from the instance’s BINN folder,
sqlservr -c -f -s <instance> -mSQLCMD. - From a second command prompt,
sqlcmd -S <instance> -E -d master, thenRESTORE DATABASE master FROM DISK = 'D:\Backup\master.bak' WITH REPLACE; - The instance shuts itself down when the restore completes. Start the service normally afterwards, then restore msdb and model if they are affected too.
There is no master backup, so the system databases must be rebuilt
You have this one if master cannot be recovered and no backup of it exists anywhere.
- Copy the existing system database files somewhere safe first, so a specialist still has something to work with.
- Run setup from media at the instance’s version:
setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=<accounts> /SAPWD=<password>. - Restore master, model and msdb from backup if any exist, then reapply every cumulative update or service pack the instance was running.
A rebuild discards everything in master, model and msdb: logins, jobs, backup history, Database Mail configuration, distributor settings and any user objects.
A user database failed recovery after a crash or a full volume
You have this one if The instance is up, one database reads SUSPECT, and the log shows the failure while opening a file or during redo.
- Free space on the volume if it filled, or bring back the LUN if a SAN volume mounted after SQL Server started, then try
ALTER DATABASE [YourDb] SET ONLINE; - Otherwise restore from the last full backup and every log backup since.
- Run
DBCC CHECKDB ([YourDb]) WITH NO_INFOMSGS;afterwards to prove the restored copy is clean.
Repair is the only option left
You have this one if No usable backup exists, the database is SUSPECT, and the business needs whatever can be salvaged.
- Copy the mdf and ldf files somewhere safe first, so you get a second attempt.
ALTER DATABASE [YourDb] SET EMERGENCY;thenALTER DATABASE [YourDb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;DBCC CHECKDB ([YourDb], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;thenALTER DATABASE [YourDb] SET MULTI_USER;and reconcile with the application owners.
Full reference
Turning the Windows error number into a decision
| Operating system error | What it means here | What to do |
|---|---|---|
| 3 | The path in a startup parameter or a file location no longer exists | Correct the -d, -l or -e parameter, or restore the volume |
| 5 | Access denied to the service identity | Grant the per-service SID modify rights on that folder |
| 32 | The file is open in another process | Antivirus or a backup agent has it; exclude the folder |
| 112 | The volume is full | Free space, then start the service again |
Restoring master
- Stop the SQL Server service and SQL Server Agent.
- From the instance’s BINN folder run
sqlservr -c -f -s <instance> -mSQLCMD. The-cstarts the engine as an application,-fuses a minimal configuration and-mSQLCMDrestricts connections to sqlcmd. - From another command prompt, connect with
sqlcmd -S <instance> -E -d master. - Run
RESTORE DATABASE master FROM DISK = '<path>' WITH REPLACE;and let it finish. - The instance shuts down and terminates sqlcmd. Remove the startup parameters and start the service normally.
- Restore or attach whatever else was affected, then check for logins and users that no longer match.
Rebuilding the system databases, and what it costs
This is the route when master cannot be recovered and there is no backup of it. Setup recreates master, model and msdb from templates, so the instance survives but its contents do not.
| Parameter | Purpose |
|---|---|
/ACTION=REBUILDDATABASE |
Recreates the system databases |
/INSTANCENAME= |
MSSQLSERVER for a default instance, otherwise the instance name |
/SQLSYSADMINACCOUNTS= |
Windows accounts or groups to add to the sysadmin role, separated by spaces |
/SAPWD= |
Required when the instance uses mixed authentication |
/SQLCOLLATION= |
Only if you are deliberately changing the server collation |
- Record
SELECT * FROM sys.configurations;, the current collation and the system database file locations before you start, because you will be putting them back. - Everything in master, model and msdb goes: logins, jobs, backup history, mail configuration, replication distributor settings.
- Restore master, model and msdb from backup afterwards if you have them, but not if you changed the collation during the rebuild.
- Every cumulative update and service pack has to be reapplied after a rebuild.
The states you will see, and what each one means
| State | Published meaning |
|---|---|
| ONLINE | Available; the primary filegroup is online even if undo has not finished |
| RECOVERING | Being recovered. Transient: it becomes online if recovery succeeds and suspect if it fails |
| RECOVERY PENDING | A resource-related error occurred during recovery. The database is not damaged, but files may be missing |
| SUSPECT | At least the primary filegroup is suspect and may be damaged. This is where 3414 leaves a database |
| RESTORING | Files of the primary filegroup are being restored. Unavailable |
| EMERGENCY | Set by you. Read-only, single-user, logging disabled, sysadmin only, for troubleshooting |
| OFFLINE | Set by you, and the state error 942 reports |
When it started after a patch
Microsoft documents 3417 as a possible outcome of a failed cumulative update or service pack, where the upgrade scripts could not complete against the system databases. If the error appeared on the first start after patching rather than after a crash, treat it as a patching failure and work through the update’s own logs before you touch master. Restoring an older master over a patched instance makes that situation worse, not better.
Routes that make it worse
Do not reinstall SQL Server to clear either code. A reinstall does not recover data, and it usually costs you the instance’s patch level and configuration as well. Do not run REPAIR_ALLOW_DATA_LOSS on a copy you have not backed up first, and do not run it at all while a usable backup exists.
Every code this article covers
| Code | What it points at | Source |
|---|---|---|
3414 |
An error occurred during recovery, preventing the database from restarting; Microsoft documents the database being left SUSPECT, and the instance shutting down if this happens in tempdb | Microsoft Learn |
3417 |
Cannot recover the master database, so SQL Server is unable to run; restore master, repair it or rebuild the system databases | Microsoft Learn |
5171 |
The file offered is not a primary database file | Microsoft Learn |
942 |
The database cannot be opened because it is offline, which is a deliberate state rather than damage | Microsoft Learn |
945 |
The database cannot be opened due to inaccessible files or insufficient memory or disk space | Microsoft Learn |
Confirm the fix worked
SELECT name, state_desc, user_access_desc FROM sys.databases;reports every database ONLINE and MULTI_USER.- The current error log shows a completed recovery line for each database and nothing after it.
DBCC CHECKDBreturns no errors on anything that was recovered, restored or repaired.SELECT @@VERSION;still matches the build you expect, which catches a rebuild that lost its updates.- Restart the service once deliberately and confirm the instance comes up clean with nobody watching it.
Questions people ask about this
Does 3417 mean I have to reinstall SQL Server?
No, and reinstalling recovers nothing. The documented options are restoring master from a full backup, repairing it, or rebuilding the system databases with setup and restoring msdb afterwards. All three keep the installation itself.
Is this a licensing or edition problem?
No, and it costs nothing to fix. Recovery failures come from storage, permissions, an interrupted write or a failed upgrade script. No key, edition or purchase changes whether a database can be recovered.
Can I attach the mdf on its own if the log is missing?
You can force it, and you are choosing to discard every transaction that had not reached the data file when the crash happened. Restore from backup if one exists. If not, copy the files first, because that route is one way only.
Recovery has been running for an hour. Should I restart the service?
No. Restarting makes redo and undo start again from the beginning, so it extends the outage. The error log prints a percentage per database; let it finish.
The database shows RECOVERY PENDING rather than SUSPECT. Is that the same thing?
No, and the difference is useful. RECOVERY PENDING is documented as a resource-related error where the database is not damaged but files may be missing, so it often clears once you fix a path or a permission. SUSPECT means at least the primary filegroup is suspect and may be damaged, which is where error 3414 leaves a database.
