-> SQL server failed to start with below error,

_{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.
OK}

Event viewer

_{Log Name: Application
Source: MSSQLSERVER
Date: 3/10/2021 4:05:40 PM
Event ID: 17204
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: JBSAG3.JBS.COM
Description:
FCB::Open failed: Could not open file for file number 2. OS error: 3(The system cannot find the path specified.).
Event Xml:
17204 2 2 0x80000000000000 41786 Application JBSAG3.JBS.COM FCB::Open failed 2 3(The system cannot find the path specified.) 3443000010000000070000004A0042005300410047003300000000000000}

_{Log Name: System
Source: Service Control Manager
Date: 3/10/2021 4:05:40 PM
Event ID: 7024
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: JBSAG3.JBS.COM
Description:
The SQL Server (MSSQLSERVER) service terminated with the following service-specific error:
WARNING: You have until SQL Server (MSSQLSERVER) to logoff. If you have not logged off at this time, your session will be disconnected, and any open files or devices you have open may lose data.
Event Xml:
7024 0 2 0 0 0x8080000000000000 23971 System JBSAG3.JBS.COM SQL Server (MSSQLSERVER) %%3417 4D005300530051004C005300450052005600450052000000}

SQL Server Error Log

_{2021-03-10 16:05:40.48 Server The service account is ‘JBS\jvivek2k1’. This is an informational message; no user action is required.
2021-03-10 16:05:40.48 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\ERRORLOG
-s C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2021-03-10 16:05:40.49 Server Command Line Startup Parameters:
-s “MSSQLSERVER”
2021-03-10 16:05:40.49 Server SQL Server detected 1 sockets with 2 cores per socket and 2 logical processors per socket, 2 total logical processors; using 2 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2021-03-10 16:05:40.68 Server clflushopt is selected for pmem flush operation.
2021-03-10 16:05:40.68 spid9s Starting up database ‘master’.
2021-03-10 16:05:40.70 spid9s Error: 17204, Severity: 16, State: 1.
2021-03-10 16:05:40.70 spid9s FCB::Open failed: Could not open file for file number 2. OS error: 3(The system cannot find the path specified.).
2021-03-10 16:05:40.70 spid9s Error: 5120, Severity: 16, State: 101.
2021-03-10 16:05:40.70 spid9s Unable to open the physical file “”. Operating system error 3: “3(The system cannot find the path specified.)”.}

-> Above message points that file number 2 cannot be opened. File number 2 is mastlog.ldf.

-> Master log file location is C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf. I checked if the file is present in that location and if it is accessible. File mastlog.df is present and has proper permission.

-> I looked at the startup parameters closely and this is what I see,

2021-03-10 16:05:40.48 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\ERRORLOG
-s C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

-> -s seems like not a correct switch. It should be -l.

-> I changed the startup parameter for mastlog.ldf from “-s C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf” to “-l C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf”.

-> Once after the change SQL Server came online. Below is the startup parameter from SQL Server error log after SQL server came online.

2021-03-10 16:16:12.15 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2021-03-10 16:16:12.15 Server Command Line Startup Parameters:
-s “MSSQLSERVER”

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

-> SQL Server configuration manager was missing on one of the Database Server,

-> I searched for “SQLServerManager*” on my database server and found below,

-> I can see “SQLServerManager15.msc” in location C:\Windows\System32.

Option 1

Double click on SQLServerManager15.msc. SQL server configuration manager will be opened and can be used.

Option 2

-> Right click some where in desktop. Click New -> “Shortcut”,

-> Double click on the shortcut “SQLServerManager15.msc” created. You will be able to use SQLS erver configuration manager.

Option 3

-> Click Start -> Run -> mmc.exe.

-> Click File -> “Add/Remove Snap-in…”

-> Select “SQL Server Configuration Manager” and click Add,

-> Click “OK”. We will be able to use SQL Server configuration manager,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

-> I had to start SQL server in single user mode. I opened services.msc and then right clicked SQL Services and noted down the “Service name”,

-> I then started SQL Services in single user mode using below command from command prompt,

 net start MSSQL$IN2019 /m

-> It started fine, I tried connecting to the SQL server using SQLCMD and got below error,

^{Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user ”. Reason: Server is in single user mode. Only one administrator can connect at this time..}

-> I tried connecting to SQL Server using SQL Server management Studio and got below same error,

^{TITLE: Connect to Server
Cannot connect to JBResearch\IN2019.}

^{ADDITIONAL INFORMATION:
Login failed for user ”. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)}

^{For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18461&LinkId=20476
BUTTONS:
OK}

-> I stopped SQL services and also stopped all other SQL related services,

-> I then used below query to start the SQL server in single user mode that allows only SQLCMD to make connection,

Net Start MSSQL$IN2019 /m”SQLCMD”

-> It worked fine this time.

-> If you want to use SQL Server Management studio instead of SQLCMD, then you can use below command,

net start MSSQL$IN2019 -m"Microsoft SQL Server Management Studio - Query"

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.