Table of Contents
- Introduction
- Solution description
- Fix the broken paths
- Reset the SA password
- Eventlog
- See Also/sources
Introduction
After the installation of
Microsoft SQL Server 2014 Express we encountered a problem that the services could not be started. It seems that the installation have set the paths to the
model, MSDB and tempdev to the wrong location. After uninstall the SQL Server instance and reinstalling it, the problem persists. No settings are altered in the setup properties, also I could not reproduce
this same error on another server even using the same deployment image for the Azure VM.
Solution description
The solution is to change the paths to those 3 databases to the right location with starting the instance in the master-only recovery mode.
Fix the broken paths
First thing to do is to start the command prompt with elevated rights.
When it is started we need to start MSSQL with the following command:
NET START MSSQLSERVER /f /T3608
Note that MSSQLSERVER is the instance name of the service that encounter the problem. If the instance name is SQLEXPRESS, you’ll need to use
NET START MSSQL$SQLEXPRESS /f /T3608
Connect to the instance by using Windows Authentication to interactively run Transact-SQL with the following command:
or with the instance name as:
Now you want to check what is wrong with the file paths. Look up on the server for the right location, for SQL Server 2014 Express x64 the default path is “C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA“.
Use the following SQL commands:
SELECT
name
, physical_name, state_desc
FROM
sys.master_files ORDER
BY
database_id;
go
With the following command you can change the path of the files to the right location:
ALTER
DATABASE
model MODIFY
FILE ( NAME
= modeldev, FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAmodel.mdf'
);
ALTER
DATABASE
model MODIFY
FILE ( NAME
= modellog, FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAmodellog.ldf'
);
ALTER
DATABASE
msdb MODIFY
FILE ( NAME
= MSDBData, FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAMSDBData.mdf'
);
ALTER
DATABASE
msdb MODIFY
FILE ( NAME
= MSDBLog, FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAMSDBLog.ldf'
);
ALTER
DATABASE
tempdb MODIFY
FILE ( NAME
= tempdev, FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAtemp.mdf'
);
ALTER
DATABASE
tempdb MODIFY
FILE ( NAME
= templog, FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAtemp.ldf'
);
go
Use exit to close the SQLCMD, stop the MSSQLSERVER instance in the master-only recovery mode and start the instance normal.
Reset the SA password
After fixing the paths and when you open the SQL Management Studio to check if everything is working as intended, you’ll notice the users provided during the setup as administrators of the instance can’t logon. Also the ‘sa’ user is not able to logon to
the instance.
Again open the elevated command prompt and start the instance in the master-only recovery mode.
After that use the following command to reset the password of the ‘sa’ user:
ALTER
LOGIN sa
WITH
PASSWORD
= 'newpassword'
UNLOCK
go
exit
Restart the service of the MSSQL instance. Start the SQL Management Studio and connect with SQL Server Authentication.
Now you can create the other users you need to provide access to this instance.
Eventlog
These messages are logged
FCB::Open failed: Could not open file E:sql12_main_t.obj.x86Releasesqlmkmastrdatabasesmkmastr.projmodel.mdf for file number 1. OS error: 3(The system
cannot find the path specified.).
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:sql12_main_t.obj.x86Releasesqlmkmastrdatabasesmkmastr.projmodellog.ldf'.
Diagnose and correct the operating system
error, and retry the operation.
FCB::Open failed: Could not open file E:sql12_main_t.obj.x86Releasesqlmkmastrdatabasesmkmastr.projMSDBData.mdf for file number 1. OS error: 3(The
system cannot find the path specified.).
FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:sql12_main_t.obj.x86Releasesqlmkmastrdatabasesmkmastr.projMSDBLog.ldf'.
Diagnose and correct the operating system error,
and retry the operation.
See Also/sources
- Blog article «MSSQL Server not starting after fresh installation».
- Remove From My Forums
-
Question
-
Version: SQL Server 2008 SP1 Enterprise Edition
Instance: Stand Alone
Problem: SQL Server is not starting
Major Incident: System Shutdown suddenly in afternoon
SQL Server is not starting since then
Error Message in Event Viewer:
Description: «initerrlog: Could not open error log file ». Operating system error = 3(The system cannot find the path specified.).»
EventID: 17058
Latest ErrorLog: (To help Debug)
2009-08-31 15:10:01.24 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
2009-08-31 15:10:03.54 spid17s Service Broker manager has shut down.
2009-08-31 15:10:03.54 spid17s Error: 17054, Severity: 16, State: 1.
2009-08-31 15:10:03.54 spid17s The current event was not reported to the Windows Events log. Operating system error = (null). You may need to clear the Windows Events log if it is full.
2009-08-31 15:10:03.63 spid7s SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.
Steps Tried:
1. Made SQL Server Manual Start and then restart the Server (No luck)
2. Try to run SQL Server under Minimal Configuration using -c -f (No Luck)
3. Try to run SQL Server under single user -m (No luck)
-
Edited by
Tuesday, September 1, 2009 1:13 AM
appropiate title
-
Edited by
Answers
-
About the error message is pretty straight forward. Does the SQL Server service account have permissions on that folder?
Also the error log:
SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.’I believe it may be due to any scheduled tasks or jobs are set to startup of SQLagent, check.
Satya SKJ, SQL Server MVP, http://www.sqlserver-qa.net
-
Marked as answer by
Nimit Parikh
Wednesday, September 2, 2009 12:13 AM
-
Marked as answer by
-
Check that the errorlog path exists and the service account has permission to it. This is part of the start-up parameters for the SQL Server service and can be found the following way:
start->programs->microsoft sql server 2008->configuration tools->sql server configuration manager
- click on sql server services on the left
- right-click on the sql server instance that’s having trouble starting up.
- select properties
- click on the advanced tab check that the startup parameters has a valid path for the -e switch. eg: -eC:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG
I usually copy the whole line into notepad to edit it, as its difficult to read.
-
Marked as answer by
Nimit Parikh
Wednesday, September 2, 2009 12:13 AM
Top 3 reasons the SQL server services won’t start.
Reason # 1:Service account password changed but not updated on the server where SQL Server instance is installed.
This is one of the most common cause where service account password has been changed by domain admin or SQL Admin but this information is not updated in SQL Server Services.
Hereis the error which we would see if we try to start using Services.
—————————Services—————————Windows could not start the SQL Server (MSSQLSERVER) service on Local Computer.Error 1069: The service did not start due to a logon failure.—————————
System Event logs should show below
Log Name: System
Source: Service Control Manager
Date: <Date Time>
Event ID: 7000
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: MyServer
Description:The SQL Server (MSSQLSERVER) service failed to start due to the following error:
The service did not start due to a logon failure.
Solution: We need to update the password in services.The right way to do itisto use SQL Server Configuration Manager and type in new password(under Log On tab).
Reason # 2:Startup parameters have incorrect file path locations.
This is another common cause of SQL Server Service startup failure.Let’s assume that master database is located on a drive and files of the database (master.mdf and/or mastlog.ldf) are not available. Since master database is a system database, SQL Service would fail to start. If we try to start SQL via services, we will get below error.
—————————
Services
—————————
Windows could not start the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 17113.
—————————If we attempt the same via configuration manager, we get standard error which doesn’t explain much.
—————————
SQL Server Configuration Manager
—————————
The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.
—————————
Whenever we get such errors, we should start looking at SQL Server ERRORLOGs are defined under start-up parametersor application event log.We can look at SQL Server Configuration Manager and look for Startup parameter having name -e as shown below (for SQL 2014):
We can open ERRORLOG using notepad or any other text editor. Here is the snippet which shows the problem.
<Date Time> Server Error: 17113, Severity: 16, State: 1.
<Date Time> Server Error 2(The system cannot find the file specified.)occurred while opening file ‘C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAmaster.mdf’ to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
As highlighted above, we can see that SQL is not able to find the file master.mdf.
Below is another example of error for model database.
<Date Time>spid9s Error: 17207, Severity: 16, State: 1.<Date Time>spid9s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.)occurred while creating or opening file ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmodellog.ldf’. Diagnose and correct the operating system error, and retry the operation.<Date Time>spid9s File activation failure. The physical file name «C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmodellog.ldf» may be incorrect.<Date Time>spid9s Error: 945, Severity: 14, State: 2.<Date Time>spid9s Database ‘model’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details.<Date Time>spid9s Could not create temp db. You may not have enough disk space available. Free additional disk space by deleting other files on the temp db drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the temp db files could not be initialized.
Solution:First we need to find out the correct location of the files. Once files are identified, either put them into the location where SQL Server wants or ALTER the database to point to correct location.If there is an issue with model or temp db database, then we need to start SQL Server using trace flag3608.
Reason #3:System database files not available -accidental deletion or corruption due to disk failures.
If files are missing or corrupted for system databases (master and/or model) SQL Server service would not start. ERROR LOG (mentioned earlier)would contain the exact database name and file name which has the problem.Here are few snippets of error pointing to corruption of system databases.
<Date Time>spid5s Starting up database ‘master’.
<Date Time> spid5sError: 9003, Severity: 20, State: 1.
<Date Time> spid5sThe log scan number (216:72:1) passed to log scan in database ‘master’ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
<Date Time>spid5s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
Depending on which database file is corrupted, we need to take appropriate action. If master database files are corrupted (error above)then we need to rebuild the master database and restore it from the backup. If issue exists with other system databases, then SQL can be started via trace flag and they can be restored.
Did this answer your question?
Thanks for the feedback
There was a problem submitting your feedback. Please try again later.
December 11, 2018
MSSQL
ERROR MESSAGE:
Error 3(The system cannot find the path specified.) occurred while opening file ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf’ to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.
EXPLANATION:
When you want to start SQL Server, you may receive such an error in Event Viewer.
The reason I encounter this error is that the account used for the SQL Server service did not have sufficient permissions for the path specified in the error.
The SQL Server service account did not have access to the DATA folder in the example.
Note: You may need to check the access rights of all folders related to Instance.
SOLUTION:
This problem will be resolved when you grant the necessary access rights to the SQL Server service account.
For some reasons I moved this folder : (Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQL
) to another drive, then returned it to the same location, but sql server has stopped working, showing this error when trying to start it again:
Windows could not start the SQL Server (MSSQLSERVER) on Local
Computer. For more information, review the System Event Log. If this
is a non-Microsoft service, contact the service vendor, and refer to
service-specific error code 3417.
I tried many solutions discussed in different forums, but none of them work for me.
The folder is not compressed or encrypted.
My sql server version is 2012:
Microsoft SQL Server Management Studio 11.0.3128.0
Microsoft Analysis Services Client Tools 11.0.3128.0
Microsoft Data Access Components (MDAC) 6.1.7601.17514
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.0.8112.16421
Microsoft .NET Framework 4.0.30319.18047
Operating System 6.1.7601
Thanks for your kind help in advance.
Aruna
11.9k3 gold badges28 silver badges42 bronze badges
asked Jun 24, 2013 at 10:15
4
Very simple to solve this problem.
Just open RUN window(Window+R) and type services.msc
:
Services.msc window
Find the SQL within name column and right click on that.
right click on SQL as in screenshot
You will get properties option, click on properties.
One new window will be open and there you have to click on Log On tab. And select the Local System Account. then apply and okay.
Select Local system Account
After that come again in services.msc
window. and right click on sql and click start.
And finally, SQL Server services started successfully. enjoy and keep learning.
answered Dec 10, 2017 at 17:44
Nandan SonyNandan Sony
7995 silver badges4 bronze badges
4
Run this command after opening cmd as administrator
net start mssqlserver /T902
This command is called trace flag 902. It is used to bypass script upgrade mode. Every time when you try to start your sql service it also looks for script upgrades. and when the script upgrade fail your service unable to start. So, Whenever we have such upgrade script failure issue and SQL is not getting started, we need to use trace flag 902 to start SQL.
I hope this will help you..
answered Jan 10, 2020 at 6:52
Abdulhakim ZeinuAbdulhakim Zeinu
3,0931 gold badge30 silver badges36 bronze badges
0
Check if you did compress the driver or folder in where you put the .mdf file.
If so, plesae goto the driver or folder, change the compress option by
Properties -> Advanced and unticked the “Compress contents to save disk space” checkbox.
After above things, you should be able to start the service again.
Ajay2707
5,6566 gold badges40 silver badges58 bronze badges
answered Sep 25, 2013 at 3:12
ginogino
1791 silver badge3 bronze badges
2
In my particular case, I fixed this error by looking in the Event Viewer to get a clue as to the source of the issue:
I then followed the steps outlined at Rebuilding Master Database in SQL Server.
Note: Take some good backups first. After erasing the master database, you will have to attach to all of your existing databases again by browsing to the
.mdf files
.
In my particular case, the command to rebuild the master database was:
C:Program FilesMicrosoft SQL Server110Setup BootstrapSQLServer2012>setup /ACTION=rebuilddatabase /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=mike /sapwd=[insert password]
Note that this will reset SQL server to its defaults, so you will have to hope that you can restore the master database from E:backupmaster.bak
. I couldn’t find this file, so attached the existing databases (by browsing to the existing .mdf
files), and everything was back to normal.
After fixing everything, I created a maintenance plan to back up everything, including the master database, on a weekly basis.
In my particular case, this whole issue was caused by a Seagate hard drive getting bad sectors a couple of months after its 2-year warranty period expired. Most of the Seagate drives I have ever owned have ended up expiring either before or shortly after warranty — so I’m avoiding Seagate like the plague now!!
answered Sep 20, 2014 at 21:32
ContangoContango
75.7k57 gold badges258 silver badges300 bronze badges
This usually occurs when the master.mdf or the mastlog.ldf gets corrupt . In order to solve the issue goto the following path C:Program FilesMicrosoft SQL ServerMSSQL10.SQLEXPRESSMSSQL
, there you will find a folder ” Template Data ” , copy the master.mdf and mastlog.ldf and replace it in C:Program FilesMicrosoft SQL ServerMSSQL10.SQLEXPRESSMSSQLData folder
. Thats it . Now start the MS SQL service and you are done
answered Jul 21, 2014 at 9:36
sohaibysohaiby
1,1683 gold badges24 silver badges39 bronze badges
3
I have had the same error recently. I have checked the folder Log of my Server instance.
x:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLLog
and I have found this errors in logs
Starting up database 'master'.
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file
x:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAmaster.mdf for file number 1. OS error: 5(Access is denied.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "E:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAmaster.mdf". Operating system error 5: "5(Access is denied.)".
Error: 17204, Severity: 16, State: 1. FCB::Open failed: Could not open file E:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAmastlog.ldf for file number 2. OS error: 5(Access is denied.).
Error: 5120, Severity: 16, State: 101. Unable to open the physical file "E:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAmastlog.ldf". Operating system error 5: "5(Access is denied.)".
SQL Server shutdown has been initiated
So for me it was an easy fix. I just added proper access rights to this files to the sql server service account.
I hope it will help
answered Mar 4, 2017 at 15:52
In My case I had to Upgrade the SQL Server since evaluation licence had been expired.
answered Apr 18, 2018 at 11:25
MAFAIZMAFAIZ
6816 silver badges13 bronze badges
What is System Event Log saying?
Have you tried to repair:
Sql Server Installation Center -> Maintenance -> Repair
Contango
75.7k57 gold badges258 silver badges300 bronze badges
answered Jun 24, 2013 at 10:57
1
I was getting this error today. And above answers didn’t help me. I was getting this error when I try to start the SQL Server(SQLEXPRESS) service in Services(services.msc).
When I checked the error log at the location C:Program FilesMicrosoft SQL ServerMSSQL13.SQLEXPRESSMSSQLLog, there was an entry related TCP/IP port.
2018-06-19 20:41:52.20 spid12s TDSSNIClient initialization failed
with error 0x271d, status code 0xa. Reason: Unable to initialize the
TCP/IP listener. An attempt was made to access a socket in a way
forbidden by its access permissions.
Recently I was running a MSSQLEXPRESS image in my docker container, which was using the same TCP/IP port, that caused this issue.
So, what I did is, I just reset my TCP/IP by doing the below command.
netsh int ip reset resetlog.txt
Once the resetting is done, I had to restart the machine and when I try to start the SQLEXPRESS service again, it started successfully. Hope it helps.
answered Jun 19, 2018 at 15:55
Sibeesh VenuSibeesh Venu
17.7k12 gold badges98 silver badges138 bronze badges
Go to the services and check if the SQL Server (MSSQLSERVER) service is running stop it and start your SQL Server (SQLEXPRESS) service it should work.
see the image:
answered Jul 20, 2020 at 13:46
MJ XMJ X
8,39612 gold badges70 silver badges99 bronze badges
0
Make sure both drive have the same partition — ( like FAT or NTFS, preferably NTFS ) also make sure he NETWORK SERVICE account, has the access.
answered Jun 24, 2013 at 10:47
Rajeev BeraRajeev Bera
2,0211 gold badge15 silver badges30 bronze badges
Database rebuild fixed it for me as well. Also had to restore the old database from backup as it got corrupted during power outage…
The copy master.mdf procedure did not work for me.
answered Mar 13, 2015 at 5:48
i had the same problem before
the error code 3417 : the SQL SERVER cannot start the master database,
without master db SQL SERVER can’t start
MSSQLSERVER_3417
The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database. Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable
MSDN Master DB
so you need to reconfigure all settings after restoring master db
solutions
- replace master mdf and ldf files with the same files from another instance of sql Server as long as its the same version reference
- Rebuild System Databases refrence
- reinstall sql server
answered Oct 5, 2015 at 17:53
YehiaYehia
3862 silver badges9 bronze badges
I copied a master.mdf und mastlog.ldf from another Computer (luckily, we have a lot of Clients with the same configuration, otherwise template data would be perhaps necessary). I backed up the damaged master.mdf and mastlog.mdf. After that I replaced the bad ones with the ones from another Computer. And it worked. I needed to start the MSSQLSERVER Service of course. But, after that I had Problem that the user was already existing but orphaned (error code 15023), I executed the query
USE Database_name
EXEC sp_change_users_login ‘Auto_Fix’, ‘username’
after that, everything was working smoothly. Hope this helps you and many thanks for this thread, saved me
answered Oct 12, 2017 at 17:07
Just rename the current ErrorLog to any other name like Errorlog _Old and change any old Log file to Error log file
try to start the SQL server services.. That’s it. it will work..
Sql server error log file got corrupted. that is why it gives the problem even when you have all permissions.. when you delete it. new file will be generated.
answered Oct 30, 2017 at 6:32
Re-entering credentials will help to start the services:
- Start > Services
- Right click on SQL Sever > Properties
- Log On
- Re-enter credentials and apply
- Start the services now
answered Jun 18, 2020 at 11:48
The reason behind getting this Error Code : 3417 may be as follows:
- One cause may be due to the Network account for the Data folder in
Program files. - The other reason may be because of some Windows
settings changed somehow.
Example: If for some reasons you have moved this folder (Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQL) to another location then returned it to the same location. So, though it was returned to the same location the server may stop working and show error code 3417 when trying to start it again.
How To Fix SQL Error 3417
- Go to «C:Program Files Microsoft SQLServerMSSQL.1MSSqLData»
- Security/Permission settings
- Network Service Account
- Add a Network Service account
- Then again check all
As stated here, you can try this third party tool as well.
answered Oct 30, 2020 at 9:53
Vignesh Kumar AVignesh Kumar A
27.7k13 gold badges62 silver badges114 bronze badges
A few times now, I’ve experienced various instances MS SQL Server to fail with this error after a Windows update. Most of the quick fixes above did not work for me.
What did:
- copy all production databases over to some safe place
- remove everything SQL Server via add/remove programs
- get the latest SQL Server Express or Dev & install it
- move the databases over to their home and reattach them.
For me, this was the fastest solution. Trying to find the cause of the error and fixing it is an afternoon of googling misty error messages. But YMMV.
answered Jan 29, 2021 at 19:32
RolfBlyRolfBly
3,5335 gold badges32 silver badges46 bronze badges
In my case problem was about evaluation period expiration. I also went through this thread of StackOverFlow.You can investigate your problem by going to Event Viewer --> Windows Log (Application)
and then search for your error id. For Step by Step guide to view error id in event viewer visit this…
answered Jan 17, 2022 at 14:44
IshtiaqIshtiaq
2381 gold badge2 silver badges9 bronze badges