Connection lost to Instance - services will not start

Question

Server2012R2
SQL 2012

Application NovaStor CMON 19.3

This was working and setup/configured correctly.

Now, the SQL instance CMON at boot will not start in services and SQL service configuration.

I manually start the CMON instance services but still do not connect. I get 1225 error

What do I look at to get the Instance connected again?

Answer 1

Hi @John Lenz ,

>One other question, where are the model db files located?

For the default SQL Server 2012 installation, you can find the database files under the folder C:\Program Files\Microsoft SQL Server\MSSQL11.CMON\MSSQL\DATA.

Based on the displayed error log, the reason that affects the failure of the SQL Server instance startup is that an error occurred when starting the Model database.

Find the model database files(model.mdf and modellog.ldf) of current damaged SQL Server 2012 instance from the above path, cut them to another folder(desktop).

Then find and copy these two files(model.mdf and modellog.ldf) from another healthy instance of the same version of SQL Server 2012, and copy them to the above path of the currently damaged SQL Server 2012 instance. Then try to restart the sql server service.

Answer 2

Make sure the SQL Server instance is starting. If it isn't then look in the Event Log to see what errors SQL is showing. Also note that if SQL was shut down unexpectedly then it might be in the middle of restoring databases and therefore won't respond to connection requests.

Also note that if SQL is running on the same machine as SSMS then use .\CMON instead. If you aren't running on the SQL server directly then run SSMS from it to verify it is not a network related error.

Answer 3

OK< I did have a catastrophic failure (power supply failed) with SQL open. Is their anythoing I can do to repair?

Answer 4

Hi @John Lenz ,

You mean that the Startup mode of your SQL Server instance has been set to "Automatic", after the computer restarted due to a power failure, the SQL Server service cannot be started automatically, but you can manually start the SQL Server services(Make sure the SQL Server service is running.).

Please check the SQL Server errorlog to find some information. For sql server 2012 instance, the default folder is C:\Program Files\Microsoft SQL Server\MSSQL11.SWORD\MSSQL\Log\ ERRORLOG. Also check the Windows event logs.

What is the startup account of the SQL Server service? Try to reconfigure the startup account in the SQL Server configuration manager.

Answer 5

I checked another physical machine that has SQL server running on it (working) in both services and SQL server configuration. In both cases, the startup type was "Automatic"

I looked into the error logs (thanks) and found below (if you want I can upload the entire log) as errors but what do I now do?

1. SQL Server is now ready for client connections. This is an informational message; no user action is required.
   2021-01-05 14:42:37.37 Server SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.

2021-01-05 14:42:37.51 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/Long2012.longsoho.local:CMON ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

2021-01-05 14:42:37.51 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/Long2012.longsoho.local:63391 ] for the SQL Server service. Windows return code: 0x200b, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

2021-01-05 14:42:38.80 spid6s Error: 9004, Severity: 23, State: 6.

Next

2021-01-05 14:42:38.80 spid6s An error occurred while processing the log for database 'model'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

2021-01-05 14:42:38.81 spid6s SQL Server shutdown has been initiated

2021-01-05 14:42:38.81 spid6s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

2021-01-05 14:42:39.82 spid6s Error: 25725, Severity: 16, State: 1.

2021-01-05 14:42:39.82 spid6s An error occurred while trying to flush all running Extended Event sessions. Some events may be lost.