One day, we suddenly discovered that the SQL Server was not running and was in a stopped state. In a rush, we quickly enabled the SQL Server and Agent services to get them running again.
Afterward, we began investigating the reason behind this incident. We checked the SQL error log as the first step but couldn’t find any relevant information. The only logs we found in the error log are as follows:
Last log before service terminated –
2023-04-22 06:00:09.45 Logon Login succeeded for user ‘XXX\xxxxxx’. Connection made using Windows authentication. [CLIENT: ]
2023-04-22 06:00:09.46 Logon Login succeeded for user ‘XXX\yyyyyy’. Connection made using Windows authentication. [CLIENT: ]
2023-04-22 06:00:09.53 Logon Login succeeded for user ‘zzzzzzz’. Connection made using SQL Server authentication. [CLIENT: 11.222.3.44]
log after we started the service manually –
2023-04-22 10:14:07.03 Server Microsoft SQL Server 2016 (SP2-CU17-GDR) (KB5014351) – 13.0.5893.48 (X64) May 24 2022 02:58:22 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)
Typically, when the SQL Server shuts down, we can determine the initiating service responsible for the shutdown, such as ‘Windows Server Failover Cluster’ or ‘Service Control Manager.’ However, in this case, the absence of such an indication strongly suggests that the shutdown was abrupt and not properly initiated.
On checking the System Event logs, we can see the same.
The SQL Server (MSSQLSERVER) service terminated unexpectedly. It has done this 1 time(s). Event ID: 7034
Then we reached out to Microsoft for support. They recommended running a tool called PSSDIAG and sharing the collected data with them for analysis. However, even after reviewing the data, they were unable to reach any definitive conclusions regarding the problem’s cause. As a last resort, they suggested running Procdump specifically on the server for the sqlservr.exe process.
We followed their advice and set up Procdump accordingly. Surprisingly, after configuring Procdump, the issue did not reoccur.
Now, It is uncertain when this mystery will be resolved.
You may check our next blog on: Use ProcDump to capture sql server unexpected crash dump