SQL Server services are not starting or not coming online

 SQL Server services are not coming online and throwing below error in the eventvwr. So we need to bring it online with single-user mode.

Error:

=> SQL services were not coming up as it was failing with the below errors for all the System databases:

FCB::Open failed: Could not open file d:\dbs\sh\sprel\0822_164025\cmd\33\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
2020-12-22 19:37:05.47 spid15s     Starting up database 'mssqlsystemresource'.

2020-12-22 19:37:05.47 spid7s      Error: 5120, Severity: 16, State: 101.

2020-12-22 19:37:05.47 spid7s      Unable to open the physical file "d:\dbs\sh\sprel\0822_164025\cmd\33\obj\x64retail\sql\mkmastr\databases\mkmastr.proj\MSDBData.mdf". Operating system error 3: "3(The system cannot find the path specified.)".

2020-12-22 19:37:05.47 spid7s      Error: 17207, Severity: 16, State: 1.

 MSSQLSERVER_17204 - SQL Server | Microsoft Docs

 

=>We modified the physical location of the files to point to the right one :

Move System Databases - SQL Server | Microsoft Docs


1)  Start service in single mode:

Because we didn't have information in master database, we were not able to login using   any login. We used the below command that could help us to use sqlcmd utility

 

net start MSSQLSERVER /T3608 /f /m"SQLCMD"

 

2)Use the below command to modify files location  to point to the right one:

 

ALTER DATABASE database_name MODIFY FILE( NAME = logical_name , FILENAME =  'new_path\os_file_name' )  

 

3)Exit the sqlcmd utility 

 

4))Stop the instance of SQL Server. 

     NET STOP MSSQLSERVER.


5)
Restart the instance of SQL Server.






Scenario2: There is an another situation I came across in the environment. One of my colleagues accidentally deleted NT Authority\System account from the logins. After that suddenly services have been stopped are we are unable bring it online and we found the below solutions

1) Login into command prompt as an administrator and run the below command.

net start $instancename -m "Microsoft sql server management studio - query"

2) Open SSMS as run administrator and connect to the sql server instance. At this it will allow you because you are the only user and SQL Server service is in single user mode.

3) Once you connect you can create the same login from with query as password is not needed.

Comments

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server